nologging产生的条件

直接开始测试
SQL> truncate table test1;

Table truncated

SQL> insert into test1 select * from test nologging;

2 rows inserted

SQL> commit;

Commit complete

SQL> alter system switch logfile;

System altered

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/app/oracle/arch/arc1971729533226.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed

SQL> select sql_redo from v$logmnr_contents where table_name='TEST1';

SQL_REDO
-----------------------------
truncate table test1

;

insert into "SYS"."TEST1"("ID") values ('1');
insert into "SYS"."TEST1"("ID") values ('2');

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed

SQL> insert /*+ append */ into test1 select * from test nologging;

2 rows inserted

SQL> commit;

Commit complete

SQL> alter system switch logfile;

System altered

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oracle/app/oracle/arch/arc1981729533226.arc',options=>dbms_logmnr.new);

PL/SQL procedure successfully completed

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed

SQL> select sql_redo from v$logmnr_contents where table_name='TEST1';

SQL_REDO
--------------------------------
Unsupported

SQL>
经测试，update table,insert into values用nologging之后，用logmin都能在归档日志中抓到sql
但是insert into xxx select方式只有在insert /*+ append */ into xxx select方式下用nologging才会生效，即产生少量redo
并抓不到sql语句，以上测试是在归档模式下进行的，在非归档模式下也是同样的结果