DBMS_LOGMNR something useful. Who generates redo?

На наших системах некоторые данные подлежат периодической перегенерации и располагаются в partitioned таблицах на nologging разделах. Иногда разработчики промахиваются и создают таблицу в разделе с опцией NOLOGGING. Это приводит к генерации большого количества архивных redo логов. Определить кто производит столь непосильную работу поможет следующий скрипт:

##########################
#
# DBMS_LOGMNR
#

STORE SET /tmp/_sqlplus-env.sql
SET AUTOP OFF
set long 200000
set lines 180
set trimspool on
set heading off
set feedback off
set pages 0
set verify off
set echo off
set timing offn
SET LINESIZE 5000
SET TERMOUT OFF
SET TRIMS ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
WHENEVER OSERROR EXIT FAILURE;
set serveroutput on;
spool /tmp/_tmp2run.sql


select case rownum
when 1 then
'EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '|| '''' || name || '''' ||', OPTIONS => sys.DBMS_LOGMNR.NEW);'||chr(10)
else
'EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '|| '''' || name || '''' ||', OPTIONS => sys.DBMS_LOGMNR.ADDFILE);'||chr(10)
end
from ( select name from V$ARCHIVED_LOG where DEST_ID=1 and name is not null order by FIRST_TIME desc) a where rownum < 21; spool off host grep '^EXECUTE' /tmp/_tmp2run.sql > /tmp/_sql2run.sql
@/tmp/_sqlplus-env.sql
@/tmp/_sql2run.sql
host rm /tmp/_sqlplus-env.sql
host rm /tmp/_tmp2run.sql
host rm /tmp/_sql2run.sql
SET LINESIZE 300
EXECUTE sys.DBMS_LOGMNR.START_LOGMNR(OPTIONS => sys.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY);

SELECT count(*), OPERATION, TABLE_NAME, SEG_TYPE_NAME, TABLE_SPACE FROM V$LOGMNR_CONTENTS group by OPERATION, TABLE_NAME, SEG_TYPE_NAME, TABLE_SPACE order by 1;
-- or
col SESSION_INFO for a40
SELECT count(*), OPERATION, TABLE_NAME, TABLE_SPACE, SEG_TYPE_NAME, USERNAME, SESSION_INFO FROM V$LOGMNR_CONTENTS group by OPERATION, TABLE_NAME, SEG_TYPE_NAME, TABLE_SPACE, username, SESSION_INFO order by 1;
-- or
col SESSION_INFO for a100
select SESSION_INFO, min( TIMESTAMP ), max( TIMESTAMP ) from V$LOGMNR_CONTENTS group by SESSION_INFO;
-- or
col filename for a50
select FILENAME, LOW_TIME, HIGH_TIME from V$LOGMNR_LOGS;
-- or
col SQL_REDO for a100
col SESSION_INFO for a60
define _TABLE_NAME='NMS_ATTRIBUTE'
define _OPERATION='INSERT'
define _TABLESPACE='KAZNA_PSS_NOLOG'
SELECT USERNAME, SESSION_INFO, SQL_REDO FROM V$LOGMNR_CONTENTS where TABLE_NAME='&_TABLE_NAME' and OPERATION = '&_OPERATION' and TABLE_SPACE = '&_TABLESPACE';
EXECUTE sys.DBMS_LOGMNR.END_LOGMNR;
#
# DBMS_LOGMNR
#
###########################
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
если YES or IMPLICIT то всё хорошо, можно получить инфу по chained rows и cluster tables. Но если нет, то вывода будет вполне достаточно что-бы понять что происходит, кто генерит redo.
если мало то ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement.

reedo генерится если на таблице есть:

1. trigger
2. constraint
3. option logging set to true
4. if data storage object located in tablespace with logging option

redo не генерится если:

1. SQL*Loader direct load operations
2. Database nologging & Tablespace nologging & Object nologging
3. - direct-load INSERT : insert /*+ APPEND */
4. - direct-load INSERT : insert in without hint in parallel mode because direct path insert is the default
5. CREATE TABLE ... AS SELECT
6. CREATE INDEX
7. ALTER TABLE ... MOVE PARTITION
8. ALTER TABLE ... SPLIT PARTITION
9. ALTER INDEX ... SPLIT PARTITION
10. ALTER INDEX ... REBUILD
11. ALTER INDEX ... REBUILD PARTITION
12. INSERT /*+APPEND*/ INTO AS SELECT ...
13. INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line