Co generuje takové množství archívů? 
SQL> SELECT s.sid, s.serial#, s.username, s.program,
 i.block_changes
 FROM v$session s, v$sess_io i
 WHERE s.sid = i.sid
 ORDER BY 5 desc, 1, 2, 3, 4; 2 3 4 5
SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM BLOCK_CHANGES
------------------------------------------------ -------------
 466 47375 SYS
oracle@ORACLE (J004) 9376646
301 1
oracle@ORACLE (SMON) 8842765
128 21097 APP
appserverl-x86@G4 (TNS V1-V3) 2732673
 SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM BLOCK_CHANGES
------------------------------------------------ -------------
 341 1
oracle@ORACLE (MMON) 82347
221 3117 SYSMAN
OMS 30190
201 1
oracle@ORACLE (DBW0) 24574
UNDO generation
SQL> SELECT s.sid, s.serial#, s.username, s.program,
 t.used_ublk, t.used_urec
 FROM v$session s, v$transaction t
 WHERE s.taddr = t.addr
 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4; 2 3 4 5
SID SERIAL# USERNAME
---------- ---------- ------------------------------
PROGRAM USED_UBLK USED_UREC
------------------------------------------------ ---------- ----------
 466 47375 SYS
oracle@ORACLE (J004) 41713 4693630
SQL> select s.username, s.osuser, s.status,s.sql_id, sr.* from
 2 (select sid, round(value/1024/1024) as "RedoSize(MB)"
 3 from v$statname sn, v$sesstat ss
 4 where sn.name = 'redo size'
 5 and ss.statistic# = sn.statistic#
 6 order by value desc) sr,
 v$session s
 7 8 where sr.sid = s.sid
 9 and rownum <= 10;
USERNAME OSUSER STATUS
------------------------------ ------------------------------ --------
SQL_ID SID RedoSize(MB)
------------- ---------- ------------
SYS oracle ACTIVE
a0qsdyuf31jfa 466 34468
oracle ACTIVE
 301 1214
oracle ACTIVE
 201 234
USERNAME OSUSER STATUS
------------------------------ ------------------------------ --------
SQL_ID SID RedoSize(MB)
------------- ---------- ------------
 oracle ACTIVE
 241 227
oracle ACTIVE
 222 225
APP root INACTIVE
 128 215
Konkrétní dotaz
SQL> SELECT sql_text FROM v$sql WHERE sql_id = 'a0qsdyuf31jfa';
SQL_TEXT
--------------------------------------------------------------------------------
 create table "APP".DBMS_TABCOMP_TEMP_UNCMP tablespace APP as select /*+ full
("APP".DOCUMENTDATA)*/ * from "APP".DOCUMENTDATA sample block( 71)
create table "APP".DBMS_TABCOMP_TEMP_UNCMP tablespace APP as select /*+ full
("APP".DOCUMENTDATA)*/ * from "APP".DOCUMENTDATA sample block( 71)
MOS:
Master Note: Troubleshooting Redo Logs and Archiving [Article ID 1507157.1]
SQL: How to Find Sessions Generating Lots of Redo or Archive logs [Article ID 167492.1]
Troubleshooting High Redo Generation Issues [Article ID 782935.1]


Komentáře