SQL> SQL> /* DOC>From Metalink Note:50592.1 DOC> DOC>Create Table As Select (CTAS) DOC>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DOC>TARGET TABLESPACE: DOC>A CTAS statements generally includes a target TABLESPACE in the command, DOC>otherwise the users 'DEFAULT' tablespace is used. DOC> DOC>LOGGING: DOC>Undo: CTAS writes undo for dictionary operations only DOC>Redo: Writing of redo depends on: DOC> CTAS has an 'UNRECOVERABLE' - No redo DOC> Target tablespace is 'NOLOGGING' (Oracle8) - No redo DOC> Dictionary changes are always written to redo. DOC> DOC>IO: DOC>Read: Taken from the SELECT row source so depends on the select statement. DOC>Write: Table blocks are written direct to disk. DOC> DOC>RECOVERY: DOC>If created NOLOGGING or UNRECOVERABLE then a backup should be taken DOC>after the CTAS and any associated index builds have been performed. DOC>Recovery from a backup taken prior to the CTAS will soft corrupt the table DOC>blocks such that they report ORA-1578 when accessed. DOC> DOC>If the CTAS is logged then standard recovery applies. The table will DOC>either exist or not after recovery depending on the point in time recovered to. DOC> DOC>*/ SQL> SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98404665 250703 10:32:40 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> drop table t1_; Table dropped. SQL> -- DDL is logged SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98404665 250703 10:32:40 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> SQL> exec dbms_lock.sleep(5); PL/SQL procedure successfully completed. SQL> -- A logging CTAS SQL> create table t1_ as select * from test1; Table created. SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98404665 250703 10:32:40 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> SQL> exec dbms_lock.sleep(5); PL/SQL procedure successfully completed. SQL> drop table t1_; Table dropped. SQL> -- Nologging CTAS SQL> create table t1_ nologging as select * from test1; Table created. SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98405140 250703 10:34:51 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> select logging from user_tables where table_name='T1_'; LOG --- NO SQL> SQL> exec dbms_lock.sleep(5); PL/SQL procedure successfully completed. SQL> drop table t1_; Table dropped. SQL> -- This is not a valid syntax. SQL> -- You get no error from ORACLE, but the operation is logged and the table SQL> -- is created in logging mode. SQL> create table t1_ as select * from test1 nologging; Table created. SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98405140 250703 10:34:51 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> select logging from user_tables where table_name='T1_'; LOG --- YES SQL> SQL> exec dbms_lock.sleep(5); PL/SQL procedure successfully completed. SQL> alter table t1_ nologging; Table altered. SQL> -- Direct insert is nologging when the underlying table is. SQL> insert /*+ append */ into t1_ select * from test1; 23 rows created. SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98405285 250703 10:35:06 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> -- Rollback will not change the this record in the dictionary view. SQL> rollback; Rollback complete. SQL> select FILE#,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME,LAST_CHANGE#,LAST_TIME from v$datafile; FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 1 84973461 060603 11:09:30 2 0 3 0 4 0 5 92906425 070703 11:00:55 6 0 7 0 8 0 9 98405285 250703 10:35:06 10 0 11 0 FILE# UNRECOVERABLE_CHANGE# UNRECOVERABLE_T LAST_CHANGE# LAST_TIME ---------- --------------------- --------------- ------------ --------------- 12 0 12 rows selected. SQL> SQL> drop table t1_; Table dropped. SQL> SQL> spool off