Резвимся на UNDO

SQL> sho parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 86700
undo_tablespace string UNDOTBS1

SQL> select sum(bytes)/1024/1024 from dba_undo_extents;

SUM(BYTES)/1024/1024
--------------------
4199.5

SQL> select substr(file_name, instr(file_name, '/', -1)+1, 20) "Filename",
2 file_id, tablespace_name, bytes, autoextensible
3 from dba_data_files;

Filename FILE_ID TABLESPACE_NAME BYTES AUT
-------------------- ---------- ------------------------------ ---------- ---
system02.dbf 2 SYSTEM 2904555520 YES
system01.dbf 1 SYSTEM 5442109440 YES
undotbs04.dbf 26 UNDOTBS1 2.4572E+10 YES
undotbs03.dbf 5 UNDOTBS1 524288000 NO
undotbs02.dbf 4 UNDOTBS1 2147483648 YES
undotbs01.dbf 3 UNDOTBS1 419430400 NO
sysaux01.dbf 6 SYSAUX 3040870400 YES
...

SQL> select substr(file_name, instr(file_name, '/', -1)+1, 20) "Filename",
2 file_id,tablespace_name, bytes/1024/1024 , autoextensible
3 from dba_data_files
4 where file_id in (3,4,5,26);

Filename FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUT
-------------------- ---------- ------------------------------ --------------- ---
undotbs01.dbf 3 UNDOTBS1 400 NO
undotbs02.dbf 4 UNDOTBS1 2048 YES
undotbs03.dbf 5 UNDOTBS1 500 NO
undotbs04.dbf 26 UNDOTBS1 23434 YES

SQL> select status, count(1), sum(bytes)/1024/1024
2 from dba_undo_extents
3 where FILE_ID=26
4 group by status;

STATUS COUNT(1) SUM(BYTES)/1024/1024
--------- ---------- --------------------
UNEXPIRED 273 1637.375
EXPIRED 35 52.1875

понаблюдаем....