logminer.mine_value giving error ORA-01323: Invalid state
Date: Mon, 9 Jun 2008 08:07:30 -0700 (PDT)
Message-ID: <5beedc48-a0cd-4a87-8b85-b797b7ea5e43@34g2000hsf.googlegroups.com>
logminer.mine_value giving error ORA-01323: Invalid state
I am trying to capture changes using logminer
and I am getting ORA-01323: Invalid state.
Basically I am trying to get the actual redo and undo values( not the binary representation of actual values)
Here are the steps which I am doing.
- begin sys.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'E:\APP \ADMINISTRATOR\PRODUCT\11.1.0\DB_2\RDBMS\ARC00027_0656739866.001', OPTIONS => sys.DBMS_LOGMNR.ADDFILE); end;
- BEGIN sys.dbms_Logmnr.Start_Logmnr(Options => sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG + sys.DBMS_LOGMNR.COMMITTED_DATA_ONLY + sys.DBMS_LOGMNR.CONTINUOUS_MINE); END;
- In this step I am atarting my stored procedure called sartcapture( actuall code is atached)
begin
ds.StartCapture(1594395);
end;
In this pocedure I am selecting from the logminer_contents and in trun calls dbms_logminer.mine_value and get the ORA-01323.
After reading the post for ORA-01323 , it says that there is no dictionary associated with logminer session. But I am kind of confuse that I am specifying DICT_FROM_ONLINE_CATALOG in step 2 while starting the logminer session, and why still I am getting this error.
any help would be aprreciated.
CODE FOR the Stored procdure StartCapture
create or replace
PROCEDURE StartCapture(p_Starting_SCN number) is
cursor cur_Browse_Logminer
is
select
seg_owner,
seg_name,
--SQL_COLUMN_NAME,
redo_value,
undo_value,
username,
operation,
scn,
timestamp,
commit_timestamp
from sys.v_$Logmnr_Contents where scn>=p_Starting_SCN ;--and
seg_name='TEST4';
--cursor cur_Browse_Logminer is select sysdate from dual;
Before_Image_test4_rec test4%rowtype;
After_Image_test4_rec test4%rowtype;
v_Column varchar2(1000);
begin
for logmnr_rec in cur_Browse_Logminer --(p_Starting_SCN) loop
--insert into capture_log
values(sysdate,logmnr_rec.commit_timestamp);
--dbms_output.put_line('Entring the loop');
if upper(logmnr_rec.seg_NAME)='TEST4' then --and
logmnr_rec.SQL_COLUMN_NAME='ID' then
- dbms_output.put_line(' Entring the if condition');
- v_Column:=logmnr_rec.SEG_OWNER||'.'||logmnr_rec.SEG_NAME||'.'||
logmnr_rec.SQL_COLUMN_NAME;
if
sys.DBMS_LOGMNR.COLUMN_PRESENT(logmnr_rec.redo_value,'DS.TEST4.ID')=1
then
Before_Image_test4_rec.id:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,'DS.TEST4.ID');
--null;
end if;
--
Before_Image_test4_rec.value:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.undo_value,'DS.TEST4.VALUE');
--
After_Image_test4_rec.id:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,v_Column);
--
After_Image_test4_rec.value:=sys.DBMS_LOGMNR.mine_value(logmnr_rec.redo_value,'DS.TEST4.VALUE');
insert into captured_changes
(
seg_owner,
seg_name,
username,
operation,
Commit_SCN,
Operation_Time,
Commit_Time,
Captured_Time,
Id_Before ,
Name_Before ,
ID_After ,
Name_After
)
values
(
logmnr_rec.seg_owner, logmnr_rec.seg_name, logmnr_rec.username, logmnr_rec.operation, logmnr_rec.scn, logmnr_rec.timestamp, logmnr_rec.commit_timestamp,
sysdate,
logmnr_rec.undo_value, Before_Image_test4_rec.value, logmnr_rec.redo_value,
After_Image_test4_rec.value
);
commit;
end if ;
end loop;
end;
Received on Mon Jun 09 2008 - 10:07:30 CDT