How to change default tablespace for LogMiner tables in Oracle Streams

1) Create a new tablespace for Log miner

CREATE TABLESPACE LOGMNR_TBS DATAFILE ‘/oradata01/STREAMDB/logmnr_tbs01.dbf' size 100m;

2) Move the objects from SYSAUX (default tablespace) to new tablespace.

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/

3) If there are active log miner session, then we'll get following error

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNR_TBS');
END;
ORA-01356: active logminer sessions found
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 233
ORA-06512: at line 2

This error suggests that, there are active logminer sessions in the database.

There are two types of log mining sessions

1) Persistent (CAPTURE process)
2) Non-persistent

We need to stop the LogMiner sessions to change the tablespace.

– Stop Non-persistent

BEGIN
DBMS_LOGMNR.END_LOGMNR();
END;
/
PL/SQL procedure successfully completed.

NOTE: If no logminer sessions are active, we'll get ORA-01307: no LogMiner session is currently active

– Stop Persistent

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE'); END;
/
PL/SQL procedure successfully completed.

4) Then change the LogMiner tablespace

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/
PL/SQL procedure successfully completed.

5) Confirm the objects are moved to new tablespace

SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'LOGMNR_TBS';


6) Start capture process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE');
END;
/
PL/SQL procedure successfully completed.