|
Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index
Oracle DBA FAQ - Managing Oracle Database Tables
By: FYIcenter.com
Part:
1
2
3
4
5
(Continued from previous part...)
How To Rename a Column in an Existing Table?
Let's say you have an existing with an existing column, but you don't like
the name of that column, can you rename that column name? The answer is yes.
You can use the ALTER TABLE ... RENAME COLUMN statement to do this.
See the following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT first_name, last_name FROM emp_dept_90;
FIRST_NAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
SQL> ALTER TABLE emp_dept_90 RENAME COLUMN first_name
2 TO fname;
Table altered.
SQL> SELECT fname, last_name FROM emp_dept_90;
FNAME LAST_NAME
-------------------- -------------------------
Steven King
Neena Kochhar
Lex De Haan
As you can see the column "first_name" is nicely changed to "fname".
How To Delete a Column in an Existing Table?
If you have an existing column in a table and you need that column
any more, you can delete it with ALTER TABLE ... DROP COLUMN statement.
Here is an example SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT last_name FROM emp_dept_90;
LAST_NAME
-------------------------
King
Kochhar
De Haan
SQL> ALTER TABLE emp_dept_90 DROP COLUMN last_name;
Table altered.
SQL> SELECT last_name FROM emp_dept_90;
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier
As you can see the column "last_name" is gone.
How To View All Columns in an Existing Table?
If you have an existing table and want to know how many columns are in the table
and how they are defined, you can use the system view USER_TAB_COLUMNS
as shown in the following tutorial exercise:
SQL> COL data_type FORMAT A12;
SQL> SELECT column_name, data_type, data_length
FROM user_tab_columns WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------------------ ------------ -----------
EMPLOYEE_ID NUMBER 22
FIRST_NAME VARCHAR2 20
LAST_NAME VARCHAR2 25
EMAIL VARCHAR2 25
PHONE_NUMBER VARCHAR2 20
HIRE_DATE DATE 7
JOB_ID VARCHAR2 10
SALARY NUMBER 22
COMMISSION_PCT NUMBER 22
MANAGER_ID NUMBER 22
DEPARTMENT_ID NUMBER 22
How To Recover a Dropped Table?
If you accidentally dropped a table, can you recover it back?
The answer is yes, if you have the recycle bin feature turned on.
You can use the FLASHBACK TABLE ... TO BEFORE DROP statement
to recover a dropped table from the recycle bin as shown in the
following SQL script:
SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.
SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3
SQL> DROP TABLE emp_dept_90;
Table dropped.
SQL> FLASHBACK TABLE emp_dept_90 TO BEFORE DROP
2 RENAME TO emp_dept_bck;
Flashback complete.
SQL> SELECT COUNT(*) FROM emp_dept_bck;
COUNT(*)
----------
3
The FLASHBASK statement in this script recovered the dropped table
"emp_dept_90" to new name "emp_dept_bck". All the data rows are recovered nicely.
What Is a Recycle Bin?
Recycle bin is a logical storage to hold the tables that have been dropped
from the database, in case it was dropped in error. Tables in recycle bin
can be recovered back into database by the Flashback Drop action. Oracle database
recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parameter file.
(Continued on next part...)
Part:
1
2
3
4
5
|