ORA-02289: sequence does not exist

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:



SQL> exec DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (SCHEMA_NAME => 'DPPDEC',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_DEC_PROC',
QUEUE_NAME => 'APP_DEC_QUEUE',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'PDPP.DUBAIWORLD.AE');
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES (SCHEMA_NAME => 'DPPDEC',STREAMS_TYPE => 'APPLY',STREAMS_NAME => 'APPLY_DEC_PROC',QUEUE_NAME => 'APP_DEC_QUEUE',INCLUDE_DML => TRUE,INCLUDE_DDL => TRUE,SOURCE_DATABASE => 'PDPP.DUBAIWORLD.AE'); END;

*
ERROR at line 1:
ORA-02289: sequence does not exist
ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL_INVOK", line 289
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 439
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 397
ORA-06512: at line 1

Log files:
strmp1_ora_1057.log
PARSE ERROR #64:len=58 dep=2 uid=0 oct=3 lid=0 tim=105769277631 err=2289
SELECT "STRMADMIN"."AQ$_APP_DEC_TAB_N".NEXTVAL FROM DUAL
EXEC #20:c=170000,e=629520,p=0,cr=343,cu=0,mis=1,r=0,dep=1,og=4,tim=105769279603
ERROR #20:err=2289 tim=10830774




Solution:





10046 revealed the sequence it is looking for is AQ$_APP_DEC_TAB_N...

It looks like the previous cleanup didn't go thru properly and that might have left the queue_tables and it's associated object inconsistent...

Manually drop is needed for the queue table since It looks like the previous cleanup didn't go thru properly and that might have left the queue_tables and it's associated object inconsistent...

-Manually drop queue table AQ$_APP_DEC_TAB_N using DBMS_AQADM.drop_queue_table('q_table','force=>true)
-Then try to recreate the apply process

Get Oracle Certifications for all Exams
Free Online Exams.com