Rename/Copy a Table

A table in oracle can be renamed using the following syntax:

alter table table_name rename to new_table_name;

Ex: SQL> alter table dept rename to hist_dept;

Renaming a table doesn't update the oracle objects such as HTML DB, PL/SQL, Views some of which might be invalid based on their dependancy on the renamed object.

A copy of the original table can be created using the following syntax:

create table new_table_name as select * from table_name;

Ex: SQL> create hist_dept as select * from dept;

we need not necessarily need to include all the columns from the original table we can create a copy based on our choice of columns that we want to be included in the new table. The new table can be created either from one single table or multiple tables. The advantage of using CREATE TABLE....AS SELECT..(CTAS) it will create the new table with datatypes similar to old table(s).

A copy of the original table created including only the columns of our choice from a single table:

create table new_table_name as select column1, column2 from table_name;

Ex: SQL> create hist_dept as select dept_id, dept_name from dept;


A copy of the original table created including only the columns of our choice from a two tables:

create table new_table_name as select t1.column1, t2.column1, t2.column2 from table_name1 t1, table_name2 t2 where t1.column1 = t2.column1;

Ex: SQL> create hist_dept as select d.dept_id, e.emp_id, e.emp_anem from dept d, employee e where
                 d.dept_id = e.dept_id;