Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
Sql代码
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
------ -------------- -------------
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
--------------------------------------------------------------------------------
SCOTT
SQL> select OWNER from user_constraints where table_name = 'DEPT2';
OWNER
--------------------------------------------------------------------------------
由上面的使用 CREATE TABLE 表名 AS SELECT 语句可以看出：
1：只会复制表数据和表结构，不会有任何约束。
2：当where条件不成立时，只复制表结构，没有任务数据。
摘自 Ear's blog