About creating a table as select (CTAS)

Question

When we do:

create table big2 as select * from big1; 

Are indexes and constraints also copied to the new table?

Answer 1

Only the NOT NULL constraints are copied. See the FAQ .

You can do CREATE TABLE big2 (bigid PRIMARY KEY) AS SELECT * FROM big1 tp create a primary key, but yes, for other indexes you will want to copy and run the index creation scripts.

Answer 2

Just for information, there is a simple way to remember indexes to recreate them after deleting the original table:

 SELECT DBMS_METADATA.get_ddl('INDEX', index_name) FROM user_indexes WHERE TABLE_NAME = 'BIG1';