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 an Existing Table?

If you don't like the name of an existing table, you change it by using the CREATE TABLE ... RENAME TO statement. Here is a sample script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> ALTER TABLE emp_dept_10 RENAME TO emp_dept_dba;
Table altered.

SQL> SELECT first_name, last_name, salary FROM emp_dept_dba;
FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Jennifer             Whalen                          4400

How To Drop an Existing Table?

If you want to delete an existing table and its data rows, you can use the DROP TABLE statement as shown in this script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
  2  AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> DROP TABLE emp_dept_10;
Table dropped.

Be careful, when you use the DROP TABLE statement. All data rows are gone too.

How To Add a New Column to an Existing Table?

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script:

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_110 
  2  AS SELECT * FROM employees WHERE department_id=110;
Table created.

SQL> ALTER TABLE emp_dept_110 ADD (vacation NUMBER);
Table altered.

SQL> SELECT first_name, last_name, vacation
  2  FROM emp_dept_110;
FIRST_NAME           LAST_NAME                   VACATION
-------------------- ------------------------- ----------
Shelley              Higgins
William              Gietz

This SQL script added a new column called "vacation" to the "emp_dept_110" table. NULL values were added to this column on all existing data rows.

How To Add a New Column to an Existing Table with a Default Value?

If you want to add a new column to an existing table, and insert a default value in this column on all existing data rows, you can use the ALTER TABLE ... ADD statement with the DEFAULT clause. Here is an example script:

SQL> CREATE TABLE emp_dept_90 
  2  AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> ALTER TABLE emp_dept_90
  2  ADD (vacation NUMBER DEFAULT 10);
Table altered.

SQL> SELECT first_name, last_name, vacation
  2  FROM emp_dept_90;
FIRST_NAME           LAST_NAME                   VACATION
-------------------- ------------------------- ----------
Steven               King                              10
Neena                Kochhar                           10
Lex                  De Haan                           10

As you can see, the "DEFAULT 10" clause did inserted 10 to all existing data rows.

(Continued on next part...)

Part: 1 2 3 4 5

More...