How to change default tablespace for LogMiner tables in Oracle Streams

— Create a new tablespace

CREATE TABLESPACE LOGMNR_TBS DATAFILE ‘+CDATA2’ SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED
/


–Then move the objects from SYSAUX (default tablespace) to new tablespace.

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNR_TBS’);
END;
/

— If there are active log miner session, then we’ll get following error stack

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 cannot stop persistent sessions with DBMS_LOGMNR. This package controls only non-persistent sessions.

To stop the persistent LogMiner session we must stop the capture process.

So we need to use

— 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=>’TARGETDB_CAPTURE’); END;
/
PL/SQL procedure successfully completed.

— Then change the LogMiner tablespace

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

— Confirm the objects are moved to new tablespace

SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = ‘LOGMNR_TBS’
/


— Start capture process

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

Advertisements
This entry was posted in Oracle LogMiner and tagged . Bookmark the permalink.

One Response to How to change default tablespace for LogMiner tables in Oracle Streams

  1. Pingback: ORA-00600: internal error code, arguments: [krvuffl_5] | Oracle-Hands-On

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s