ORA-01356: active logminer sessions found & ORA-01307: no LogMiner session is currently active

When I tried to switch tablespace to new tablespace which created for log miner I faced some ORA-??? errors.



SQL> create tablespace test_tbs datafile '/home/oracle/oracle/product/10.2.0/oradata/mydb/test_tbs01.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

Now want to move objects from SYSAUX tablecpase (this is default for log miner) to new one.

SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS');

BEGIN DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS'); END;

*

ERROR at line 1:

ORA-01356: active logminer sessions found

ORA-06512: at "SYS.DBMS_LOGMNR_D", line 232

ORA-06512: at line 1

This means there are active logminer sessions in our database. I tried to use dbms_logmnr_end_logmnr procedure, but this time new Ora exception raised.

SQL> EXEC DBMS_LOGMNR.END_LOGMNR();

BEGIN DBMS_LOGMNR.END_LOGMNR(); END;

*

ERROR at line 1:

ORA-01307: no LogMiner session is currently active

ORA-06512: at "SYS.DBMS_LOGMNR", line 76

ORA-06512: at line 1

SQL>

On our database streams configured and works! To stop LogMiner session we have to stop the capture process.


SQL> select capture_name, capture_user from dba_capture;

CAPTURE_NAME                   CAPTURE_USER
------------------------------ ------------------------------
CAPTURE_EMP                    SYS

SQL>



SQL> EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => 'capture_emp');

PL/SQL procedure successfully completed.

Now repeat above action to switch tablespace.


SQL> EXEC DBMS_LOGMNR_D.SET_TABLESPACE('TEST_TBS');

PL/SQL procedure successfully completed.

SQL>

That`s it.