Alter Table - Rename Table Add Column Modify Column Drop Column

With the ALTER TABLE statement you can rename table, rename table column, add columns to the column, modify existing column, drop column from a table or move the table. With an example the all scenarios are demonstrated.

I work with following table,

SQL> create table test (a number);
Table created.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

A)Rename a Table
----------------------
The syntax is,
ALTER TABLE [table name] RENAME TO [new table name];
To rename test to test2 use,
SQL> ALTER TABLE TEST RENAME TO TEST2;
Table altered.

We can also use only RENAME like,
SQL> RENAME TEST2 TO TEST;
Table renamed.

B)Rename Table Column
------------------------
Oracle Database lets you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column.

If you want to rename the column A of Test table to first_col then use,

SQL> ALTER TABLE TEST RENAME COLUMN A to FIRST_COL;
Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER


C)Add Table Column
-----------------------------
To add a column to an existing table, use the ALTER TABLE...ADD statement. If you want to add second_col to table Test then use,
SQL> ALTER TABLE TEST ADD SECOND_COL NUMBER;
Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NUMBER

D)Modify Existing Table Column
--------------------------------------
To modify a column use the ALTER TABLE...MODIFY statement. You can modify column datatype, default value, column constraint, and column encryption.

If I want to change data type to varchhar2 and column contrainst of column SECOND_COL to NOT NULL of table test then use,
SQL> ALTER TABLE TEST MODIFY SECOND_COL VARCHAR2(10) NOT NULL;
Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NOT NULL VARCHAR2(10)

Remember You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length.

E)Drop Table Columns
----------------------------
To drop a column from a table use ALTER TABLE...DROP COLUMN statement.

To drop the first_col of test table use,
SQL> ALTER TABLE TEST DROP COLUMN FIRST_COL;
Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)

Remember you cannot drop all columns from a table, nor can you drop columns from a table owned by SYS.

F)Marking Columns Unused
--------------------------------
If you see that the table is very large and dropping a table will take much more time then you can mark the column unused instead of dropping it using the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns.

However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

1)Add new column.
SQL> ALTER TABLE TEST ADD FIRST_COL NUMBER;
Table altered.

2)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
FIRST_COL NUMBER

3)Mark the first_col unused.
SQL> ALTER TABLE test SET UNUSED (first_col);
Table altered.

4)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)

To remove unused columns use ALTER TABLE...DROP UNUSED COLUMNS statement. If I want to drop unused column then first see in which table there remain unused columns. To see it issue,

SQL> SELECT * FROM USER_UNUSED_COL_TABS;
TABLE_NAME COUNT
------------------------------ ----------
TEST 1

Now drop it by,
SQL> ALTER TABLE TEST DROP UNUSED COLUMNS;
Table altered.

Now check for unused columns by,
SQL> SELECT * FROM USER_UNUSED_COL_TABS;
no rows selected

It is very good to remember that the clause 'COLUMN' is used only for dropping a column and for rename a column. For modify a column or for adding a column 'COLUMN' clause is not present.

Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html

http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html