48 lines
1.2 KiB
Plaintext
48 lines
1.2 KiB
Plaintext
Clean up old Extracts
|
|
---------------------
|
|
https://www.dbasolved.com/2022/04/clean-up-old-extracts/
|
|
|
|
0. Identify captures and log miner sessions
|
|
-------------------------------------------
|
|
set linesize 150
|
|
col capture_name format a20
|
|
select capture_name from dba_capture;
|
|
|
|
|
|
set linesize 130
|
|
col session_name format a20
|
|
col global_db_name format a45
|
|
select SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME from system.LOGMNR_SESSION$;
|
|
|
|
|
|
1. Drop the extracts
|
|
---------------------
|
|
exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('<MY_CAPTURE_01>');
|
|
|
|
2. Drop queue tables from log miner
|
|
-----------------------------------
|
|
|
|
set linesize 250
|
|
col owner format a30
|
|
col name format a30
|
|
col queue_table format a30
|
|
select owner, name, queue_table from dba_queues where owner = 'OGGADMIN';
|
|
|
|
|
|
|
|
# delete in automatic mode
|
|
declare
|
|
v_queue_name varchar2(60);
|
|
begin
|
|
for i in (select queue_table, owner from dba_queues where owner = 'OGGADMIN')
|
|
loop
|
|
v_queue_name := i.owner||'.'||i.queue_table;
|
|
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => v_queue_name, force => TRUE);
|
|
end loop;
|
|
end;
|
|
/
|
|
|
|
# or delete one by one
|
|
exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => '<OWNER>.<TABLE_NAME>', force => TRUE);
|
|
# note that tables with AQ$_ prefix will be autotaic deleted
|