Oracle DBA FAQ - Managing Oracle Database Tables

Home >> FAQs/Tutorials >> Oracle DBA FAQ

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

More...