Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as scott SQL code SQL> select * from dept; deptno dname loc ------ ------------ ------------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston SQL> create table dept2 as select * from dept; Table created www.2cto.com SQL> select * from dept; deptno dname loc ----------------------------- ---- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston SQL> select * from dept2; deptno dname loc ------ ------------ ------------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston SQL> create table dept3 as select * from dept where 1 = 2; table created www.2cto.com SQL> select * from dept3; DEPTNO DNAME LOC ------------------------- -------- SQL> select OWNER from user_constraints where table_name = 'dept'; OWNER then scott SQL> select OWNER from user_constraints where table_name = 'dept2 '; OWNER -------------------------------------------------------------------------------- the preceding create table name as select statement shows that 1: only copies TABLE data and TABLE structure without any constraints. 2: When the where condition is invalid, only the table structure is copied and no task data is available.
From the Ear's blog