Create Table as select

 L'istruzione seguente

SQL> create table emp_copy
        as
        select * from employees;

Crea una tabella che contiene solo i dati della tabella originaria ma non copia le contraints ad eccezione della constraint NOT NULL.

SQL > select dBMS_metadata.get_ddl('TABLE','EMPLOYEES','HR') FROM DUAL;

CREATE TABLE "HR"."EMPLOYEES"
( "EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL NABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,
CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
)

SQL> select dBMS_metadata.get_ddl('TABLE','EMP_COPY','HR') FROM DUAL;

  CREATE TABLE "HR"."EMP_COPY" 

   ( "EMPLOYEE_ID" NUMBER(6,0), 

"FIRST_NAME" VARCHAR2(20), 

"LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, 

"EMAIL" VARCHAR2(25) NOT NULL ENABLE, 

"PHONE_NUMBER" VARCHAR2(20), 

"HIRE_DATE" DATE NOT NULL ENABLE, 

"JOB_ID" VARCHAR2(10) NOT NULL ENABLE, 

"SALARY" NUMBER(8,2), 

"COMMISSION_PCT" NUMBER(2,2), 

"MANAGER_ID" NUMBER(6,0), 

"DEPARTMENT_ID" NUMBER(4,0)

   )