One test on 8174, non-archive mode:
Code:
SQL> Select * from v $ version; banner ---------------------------------------- ------------------------ Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production PL / SQL Release 8.1.7.4.0 - Production Core 8.1.7.2 .1 Production TNS for 32-bit Windows: Version 8.1.7.4.0 - Production NLSRTL VERSION 3.4.1.0.0 - Production SQL> Archive LOG LIST Database Log Mode Non-Archive Mode Auto Archive Enable Archive End D: Databaseoraclera817RDBMS The earliest summary information Log Sequence 1488 Current Log Sequence 1491 SQL> Select * from redo_size; value ---------- 91848 SQL> CREATE TABLE TEST NOLGGING AS SELECT * from ALL_OBJECTS; Table has been created. SQL> Select * from redo_size; value ---------- 147148 SQL> DROP TABLE TEST; Table has been discarded. SQL> Select * from redo_size; value ---------- 177584 SQL> CREATE TABLE TEST AS SELECT * from ALL_OBJECTS; Table has been created. SQL> Select * from redo_size; value ---------- 232892 SQL> SELECT (232892 - 177584) REDO, (147148-91848) REDO_NOLOG FROM DUAL; redo redo_nolog ---------------- ---------- 55308 55300 '
In archiving mode:
Code:
SQL> Shutdown Immediate; Database has been turned off. The database has been uninstalled. Oracle routines have been closed. SQL> Startup Mount Oracle routines have been started. Total System Global Area 65648668 Bytes Fixed Size 75804 Bytes Variable Size 44523520 Bytes Database Buffers 20971520 Bytes Redo Buffers 77824 BYtes Database Loading. SQL> ALTABASE ArchiveLog 2 / Database has changed. SQL> ALTER DATABASE OPEN; database has changed. SQL> Drop Table Test; Table has been discarded. SQL> Select * from redo_size; value ---------- 30520 SQL> CREATE TABLE TEST AS SELECT * from ALL_OBJECTS; Table has been created. SQL> Select * from redo_size; value ---------- 2953668 SQL> DROP TABLE TEST; Table has been discarded. SQL> Select * from redo_size; value --------------------------------------------------------------------------------------------------------------------------- SQL> Select * from redo_size; value ---------- 3125328 SQL> SELECT (2953668-30520) Redo, (3125328-3070020) Redo_nolog from dual; redo redo_nolog ---------- ---------- 2923148 55308 'In the archive mode, close one step test, compare CTAS and CTAS No data Insert Append Redo Size:
Code:
SQL> Drop Table Test; Table has been discarded. SQL> Select * from redo_size; value ---------- 3155764 SQL> CREATE TABLE TEST AS SELECT * from ALL_OBJECTS WHERE 1 = 0; the table has been created. SQL> INSERT / * APPEND * / INTO TEST SELECT * ALL_OBJECTS; 25474 rows have been created. SQL> Commit; submit completion. SQL> Select * from redo_size; value ---------- 6079860 SQL> SELECT (6079860-3155764) Logging from Dual; (6079860-3155764) -------------- --- 2924096 SQL> DROP TABLE TEST; Table has been discarded. SQL> Select * from redo_size; value ---------- 6110356 SQL> CREATE TABLE TEST NOLOGGING AS SELECT * ALL_OBJECTS WHERE 1 = 0; the table has been created. SQL> INSERT / * APPEND * / INTO TEST SELECT * ALL_OBJECTS; 25474 rows have been created. SQL> Commit; submit completion. SQL> Select * from redo_size; value ---------- 6167588 SQL> Select (6079860-3155764) Logging, (6167588-6110356) NOLOGGING from Dual; Logging NOLOGGING -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Redo size generated by the system only in ArchiveLog CTAS, only more Redo Size compared CTAS and CTAS INSERT APPEND, actually (ArchiveLogging Redo = CTAS (no data) NOLOGING INSERT Append redo size; ctas redo = CTAS (No Data) INSERT APPEND REDO SIZE
