ORA-31693 ORA-00604 and ORA-942 During Data Pump Import
(Doc ID 1943390.1)
Last updated on APRIL 03, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
You are performing a schema data pump import (impdp) and are constantly getting the following errors during the import
ORA-31693: Table data object "<schema>"."<tablename>" failed to load/unload and is being skipped due to error:
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist
ORA-00604: error occurred at recursive SQL level 2
ORA-00942: table or view does not exist
You have enabled tracing for the ORA-942 and from the trace files, you see:
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=4va1rghfz28hj) -----
SELECT COUNT(*) FROM SYS_SQL_FILE_TABLE_0
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=4va1rghfz28hj) -----
SELECT COUNT(*) FROM SYS_SQL_FILE_TABLE_0
and
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=8bg2vzmunu954) -----
CREATE OR REPLACE FORCE VIEW "SYS"."AQ$KUPC$DATAPUMP_QUETAB_1" AS SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(s.subscriber_type,2,'UNDELIVERABLE',
decode(h.dequeue_time, NULL,decode(l.dequeue_time, NULL, decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED',
10, 'BUFFERED_EXPIRED'),(decode(l.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED'))), (decode(h.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED')))
) MSG_STATE, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as date) delay, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, expiration, cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, qt.enq_tid ENQ_TXN_ID, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_DATE(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as date)) DEQ_TIME, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_TIMESTAMP(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as timestamp))
DEQ_TIMESTAMP, decode(h.dequeue_user, NULL, l.dequeue_user, h.dequeue_user)
DEQ_USER_ID, decode(h.transaction_id, NULL, l.transaction_id,
h.transaction_id) DEQ_TXN_ID, h.retry_count retry_count, decode (state, 0, exception_qschema,
1, exception_qschema,
2, exception_qschema,
NULL) EXCEPTION_QUEUE_OWNER, decode (state, 0, exception_queue,
1, exception_queue,
2, exception_queue,
NULL) EXCEPTION_QUEUE, user_data, h.propagated_msgid PROPAGATED_MSGID, sender_name SENDER_NAME, sender_address SENDER_ADDRESS, sender_protocol SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_queue, NULL),
decode (l.transaction_id, NULL, NULL, exception_queue)),
decode (h.transaction_id, NULL, NULL, exception_queue))
ORIGINAL_QUEUE_NAME, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_qschema, NULL),
decode (l.transaction_id, NULL, NULL, exception_qschema)),
decode (h.transaction_id, NULL, NULL, exception_qschema))
ORIGINAL_QUEUE_OWNER, decode(s.subscriber_type,2,
'Messages to be cleaned up later',
decode(h.dequeue_time, NULL,
decode(state, 3,
decode(h.transaction_id, NULL,
decode (expiration , NULL ,
'MAX_RETRY_EXCEEDED',
'TIME_EXPIRATION'),
'INVALID_TRANSACTION', NULL,
'MAX_RETRY_EXCEEDED'), NULL),
decode(h.transaction_id, NULL,
'PROPAGATION_FAILURE', NULL)))
EXPIRATION_REASON, decode(h.subscriber#, 0, decode(h.name, '0', NULL,
h.name),
s.name) CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "KUPC$DATAPUMP_QUETAB_1" qt, "AQ$_KUPC$DATAPUMP_QUETAB_1_H" h LEFT OUTER JOIN "AQ$_KUPC$DATAPUMP_QUETAB_1_L" l ON h.msgid = l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address# = l.address#, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE qt.msgid = h.msgid AND ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id) OR (h.subscriber# = 0 AND h.address# = s.subscriber_id)) AND (qt.state != 7 OR qt.state != 9 ) UNION ALL SELECT q.name QUEUE, b.msgid MSG_ID, b.corrid CORR_ID, b.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '4', 'IN MEMORY',
'5', 'DEFERRED',
'12', 'EXPIRED INMEMORY',
NULL) MSG_STATE, cast (null as DATE) DELAY, cast (null as TIMESTAMP) DELAY_TIMESTAMP, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(b.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, b.enq_uid ENQ_USER_ID, cast (null as VARCHAR2(30)) ENQ_TXN_ID, cast (null as DATE) DEQ_TIME, cast(null as TIMESTAMP) DEQ_TIMESTAMP, cast (null as number) DEQ_USER_ID, cast(null as VARCHAR2(30)) DEQ_TXN_ID, b.retry_count RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num, "SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA, cast(null as RAW(16)) PROPAGATED_MSGID, b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, b.dequeue_msgid ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast(null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 12, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM SYS.qt40238_BUFFER b, all_queues q, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE s.subscriber_id = b.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state, 4) = 4 AND q.qid = b.queue_id UNION ALL SELECT p.q_name QUEUE, p.msgid MSG_ID, p.corrid CORR_ID, p.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '2', 'SPILLED',
'3', 'DEFERRED SPILLED',
'10', 'EXPIRED SPILLED',
NULL) MSG_STATE, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as date) DELAY, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(p.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, p.enq_uid ENQ_USER_ID, p.enq_tid ENQ_TXN_ID, cast(FROM_TZ(p.deq_time, '00:00')
at time zone sessiontimezone as date) DEQ_TIME, cast(FROM_TZ(p.deq_time, '00:00')
at time zone sessiontimezone as timestamp)
DEQ_TIMESTAMP, p.deq_uid DEQ_USER_ID, p.deq_tid DEQ_TXN_ID, p.retry_count RETRY_COUNT, p.exception_qschema EXCEPTION_QUEUE_OWNER, p.exception_queue EXCEPTION_QUEUE, p.user_data USER_DATA, cast (null as RAW(16))PROPAGATED_MSGID,p.sender_name SENDER_NAME, p.sender_address SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 10, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER b, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s, all_queues q WHERE b.subscriber_id = s.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state ,2) = 2 AND p.msgid = b.msgid AND q.qid = b.queue_id and p.q_name = q.name WITH READ ONLY
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=8bg2vzmunu954) -----
CREATE OR REPLACE FORCE VIEW "SYS"."AQ$KUPC$DATAPUMP_QUETAB_1" AS SELECT q_name QUEUE, qt.msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, decode(s.subscriber_type,2,'UNDELIVERABLE',
decode(h.dequeue_time, NULL,decode(l.dequeue_time, NULL, decode(state, 0, 'READY',
1, 'WAIT',
2, 'PROCESSED',
3, 'EXPIRED',
8, 'DEFERRED',
10, 'BUFFERED_EXPIRED'),(decode(l.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED'))), (decode(h.transaction_id, NULL, 'UNDELIVERABLE', 'PROCESSED')))
) MSG_STATE, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as date) delay, cast(FROM_TZ(qt.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, expiration, cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as date) enq_time, cast(FROM_TZ(qt.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
enq_timestamp, enq_uid ENQ_USER_ID, qt.enq_tid ENQ_TXN_ID, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_DATE(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as date)) DEQ_TIME, decode(h.transaction_id, NULL,
decode(l.transaction_id, NULL, TO_TIMESTAMP(NULL), l.dequeue_time),
cast(FROM_TZ(h.dequeue_time, '00:00')
at time zone sessiontimezone as timestamp))
DEQ_TIMESTAMP, decode(h.dequeue_user, NULL, l.dequeue_user, h.dequeue_user)
DEQ_USER_ID, decode(h.transaction_id, NULL, l.transaction_id,
h.transaction_id) DEQ_TXN_ID, h.retry_count retry_count, decode (state, 0, exception_qschema,
1, exception_qschema,
2, exception_qschema,
NULL) EXCEPTION_QUEUE_OWNER, decode (state, 0, exception_queue,
1, exception_queue,
2, exception_queue,
NULL) EXCEPTION_QUEUE, user_data, h.propagated_msgid PROPAGATED_MSGID, sender_name SENDER_NAME, sender_address SENDER_ADDRESS, sender_protocol SENDER_PROTOCOL, dequeue_msgid ORIGINAL_MSGID, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_queue, NULL),
decode (l.transaction_id, NULL, NULL, exception_queue)),
decode (h.transaction_id, NULL, NULL, exception_queue))
ORIGINAL_QUEUE_NAME, decode (h.dequeue_time, NULL, decode (l.dequeue_time, NULL,
decode (state, 3, exception_qschema, NULL),
decode (l.transaction_id, NULL, NULL, exception_qschema)),
decode (h.transaction_id, NULL, NULL, exception_qschema))
ORIGINAL_QUEUE_OWNER, decode(s.subscriber_type,2,
'Messages to be cleaned up later',
decode(h.dequeue_time, NULL,
decode(state, 3,
decode(h.transaction_id, NULL,
decode (expiration , NULL ,
'MAX_RETRY_EXCEEDED',
'TIME_EXPIRATION'),
'INVALID_TRANSACTION', NULL,
'MAX_RETRY_EXCEEDED'), NULL),
decode(h.transaction_id, NULL,
'PROPAGATION_FAILURE', NULL)))
EXPIRATION_REASON, decode(h.subscriber#, 0, decode(h.name, '0', NULL,
h.name),
s.name) CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "KUPC$DATAPUMP_QUETAB_1" qt, "AQ$_KUPC$DATAPUMP_QUETAB_1_H" h LEFT OUTER JOIN "AQ$_KUPC$DATAPUMP_QUETAB_1_L" l ON h.msgid = l.msgid AND h.subscriber# = l.subscriber# AND h.name = l.name AND h.address# = l.address#, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE qt.msgid = h.msgid AND ((h.subscriber# != 0 AND h.subscriber# = s.subscriber_id) OR (h.subscriber# = 0 AND h.address# = s.subscriber_id)) AND (qt.state != 7 OR qt.state != 9 ) UNION ALL SELECT q.name QUEUE, b.msgid MSG_ID, b.corrid CORR_ID, b.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '4', 'IN MEMORY',
'5', 'DEFERRED',
'12', 'EXPIRED INMEMORY',
NULL) MSG_STATE, cast (null as DATE) DELAY, cast (null as TIMESTAMP) DELAY_TIMESTAMP, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(b.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, b.enq_uid ENQ_USER_ID, cast (null as VARCHAR2(30)) ENQ_TXN_ID, cast (null as DATE) DEQ_TIME, cast(null as TIMESTAMP) DEQ_TIMESTAMP, cast (null as number) DEQ_USER_ID, cast(null as VARCHAR2(30)) DEQ_TXN_ID, b.retry_count RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num, "SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA, cast(null as RAW(16)) PROPAGATED_MSGID, b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, b.dequeue_msgid ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast(null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 12, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM SYS.qt40238_BUFFER b, all_queues q, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s WHERE s.subscriber_id = b.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state, 4) = 4 AND q.qid = b.queue_id UNION ALL SELECT p.q_name QUEUE, p.msgid MSG_ID, p.corrid CORR_ID, p.priority MSG_PRIORITY, decode (TO_CHAR(b.state), '2', 'SPILLED',
'3', 'DEFERRED SPILLED',
'10', 'EXPIRED SPILLED',
NULL) MSG_STATE, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as date) DELAY, cast(FROM_TZ(p.delay, '00:00')
at time zone sessiontimezone as timestamp) DELAY_TIMESTAMP, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00')
at time zone sessiontimezone as date) ENQ_TIME, cast(FROM_TZ(p.enq_time, '00:00')
at time zone sessiontimezone as timestamp)
ENQ_TIMESTAMP, p.enq_uid ENQ_USER_ID, p.enq_tid ENQ_TXN_ID, cast(FROM_TZ(p.deq_time, '00:00')
at time zone sessiontimezone as date) DEQ_TIME, cast(FROM_TZ(p.deq_time, '00:00')
at time zone sessiontimezone as timestamp)
DEQ_TIMESTAMP, p.deq_uid DEQ_USER_ID, p.deq_tid DEQ_TXN_ID, p.retry_count RETRY_COUNT, p.exception_qschema EXCEPTION_QUEUE_OWNER, p.exception_queue EXCEPTION_QUEUE, p.user_data USER_DATA, cast (null as RAW(16))PROPAGATED_MSGID,p.sender_name SENDER_NAME, p.sender_address SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) ORIGINAL_MSGID, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_NAME, cast (null as VARCHAR2(30)) ORIGINAL_QUEUE_OWNER, decode(b.state, 10, 'TIME_EXPIRATION',
NULL) EXPIRATION_REASON, s.name CONSUMER_NAME, s.address ADDRESS, s.protocol PROTOCOL FROM "AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER b, "AQ$_KUPC$DATAPUMP_QUETAB_1_S" s, all_queues q WHERE b.subscriber_id = s.subscriber_id AND bitand(s.subscriber_type, 8) != 8 AND bitand(b.state ,2) = 2 AND p.msgid = b.msgid AND q.qid = b.queue_id and p.q_name = q.name WITH READ ONLY
and
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=406stjn3n5qvg) -----
CREATE OR REPLACE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" AS SELECT /*+ NO_MERGE (qo) USE_NL(iot) */ qt.q_name Q_NAME, qt.rowid ROW_ID, qt.msgid MSGID, qt.corrid CORRID, qt.priority PRIORITY, qt.state STATE, cast(FROM_TZ(qt.delay, '00:00') at time zone sessiontimezone as timestamp) DELAY, qt.expiration EXPIRATION, cast(FROM_TZ(qt.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, qt.enq_uid ENQ_UID, qt.enq_tid ENQ_TID, cast(FROM_TZ(qt.deq_time, '00:00') at time zone sessiontimezone as timestamp) DEQ_TIME, qt.deq_uid DEQ_UID, qt.deq_tid DEQ_TID, qt.retry_count RETRY_COUNT, qt.exception_qschema EXCEPTION_QSCHEMA, qt.exception_queue EXCEPTION_QUEUE, qt.cscn CSCN, qt.dscn DSCN, qt.chain_no CHAIN_NO, qt.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(qt.time_manager_info, '00:00') at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, qt.step_no STEP_NO, qt.user_data USER_DATA, qt.sender_name SENDER_NAME, qt.sender_address SENDER_ADDRESS, qt.sender_protocol SENDER_PROTOCOL, qt.dequeue_msgid DEQUEUE_MSGID, 'PERSISTENT' DELIVERY_MODE, 0 SEQUENCE_NUM, 0 MSG_NUM, qo.qid QUEUE_ID, qt.user_prop USER_PROP, iot.subscriber# SUBSCRIBER_ID, iot.name SUBSCRIBER_NAME, iot.queue# QUEUE_EVTID FROM "KUPC$DATAPUMP_QUETAB_1" qt, "AQ$_KUPC$DATAPUMP_QUETAB_1_I" iot, SYS.ALL_INT_DEQUEUE_QUEUES qo WHERE qt.msgid=iot.msgid and qt.q_name = qo.name AND qo.owner = 'SYS' AND iot.msg_enq_time = qt.enq_time AND iot.msg_step_no = qt.step_no AND iot.msg_local_order_no = qt.local_order_no AND iot.msg_chain_no = qt.chain_no UNION ALL SELECT qo.name Q_NAME, cast (null as rowid) ROW_ID, b.msgid MSGID, b.corrid CORRID, b.priority PRIORITY, decode(b.state, 4, 0, 5, 8, 12, 10, 0) STATE, cast (null as TIMESTAMP) DELAY, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, b.enq_uid ENQ_UID, cast(null as varchar2(30)) ENQ_TID, cast(null as timestamp) DEQ_TIME, cast (null as number) DEQ_UID, cast (null as varchar2(30)) DEQ_TID, 0 RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, 0 CSCN, 0 DSCN, 0 CHAIN_NO, 0 LOCAL_ORDER_NO, cast (null as TIMESTAMP) TIME_MANAGER_INFO, 0 STEP_NO, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num, "SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA, b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, cast(null as raw(16)) DEQUEUE_MSGID, 'BUFFERED' DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM, qo.qid QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM SYS.qt40238_BUFFER b, sys.all_int_dequeue_queues qo WHERE bitand(b.state, 4) = 4 AND qo.qid =b.queue_id UNION ALL SELECT p.q_name Q_NAME, cast (null as rowid) ROW_ID, p.msgid MSGID, p.corrid CORRID, p.priority PRIORITY, p.state STATE, cast(FROM_TZ(p.delay, '00:00') at time zone sessiontimezone as timestamp) DELAY, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, p.enq_uid ENQ_UID, p.enq_tid ENQ_TID, cast(FROM_TZ(p.deq_time, '00:00') at time zone sessiontimezone as timestamp) DEQ_TIME, p.deq_uid DEQ_UID, p.deq_tid DEQ_TID, p.retry_count RETRY_COUNT, p.exception_qschema EXCEPTION_QSCHEMA, p.exception_queue EXCEPTION_QUEUE, p.cscn CSCN, p.dscn DSCN, p.chain_no CHAIN_NO, p.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(p.time_manager_info, '00:00') at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, p.step_no STEP_NO, p.user_data USER_DATA, p.sender_name SENDER_NAME, p.sender_address SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) DEQUEUE_MSGID, 'BUFFERED' DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM, qo.qid QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM "AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER b, sys.all_int_dequeue_queues qo WHERE bitand(b.state, 2) = 2 AND p.msgid = b.msgid AND qo.qid = b.queue_id and p.q_name = qo.name WITH READ ONLY
----- Error Stack Dump -----
ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=406stjn3n5qvg) -----
CREATE OR REPLACE VIEW "SYS"."AQ$_KUPC$DATAPUMP_QUETAB_1_F" AS SELECT /*+ NO_MERGE (qo) USE_NL(iot) */ qt.q_name Q_NAME, qt.rowid ROW_ID, qt.msgid MSGID, qt.corrid CORRID, qt.priority PRIORITY, qt.state STATE, cast(FROM_TZ(qt.delay, '00:00') at time zone sessiontimezone as timestamp) DELAY, qt.expiration EXPIRATION, cast(FROM_TZ(qt.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, qt.enq_uid ENQ_UID, qt.enq_tid ENQ_TID, cast(FROM_TZ(qt.deq_time, '00:00') at time zone sessiontimezone as timestamp) DEQ_TIME, qt.deq_uid DEQ_UID, qt.deq_tid DEQ_TID, qt.retry_count RETRY_COUNT, qt.exception_qschema EXCEPTION_QSCHEMA, qt.exception_queue EXCEPTION_QUEUE, qt.cscn CSCN, qt.dscn DSCN, qt.chain_no CHAIN_NO, qt.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(qt.time_manager_info, '00:00') at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, qt.step_no STEP_NO, qt.user_data USER_DATA, qt.sender_name SENDER_NAME, qt.sender_address SENDER_ADDRESS, qt.sender_protocol SENDER_PROTOCOL, qt.dequeue_msgid DEQUEUE_MSGID, 'PERSISTENT' DELIVERY_MODE, 0 SEQUENCE_NUM, 0 MSG_NUM, qo.qid QUEUE_ID, qt.user_prop USER_PROP, iot.subscriber# SUBSCRIBER_ID, iot.name SUBSCRIBER_NAME, iot.queue# QUEUE_EVTID FROM "KUPC$DATAPUMP_QUETAB_1" qt, "AQ$_KUPC$DATAPUMP_QUETAB_1_I" iot, SYS.ALL_INT_DEQUEUE_QUEUES qo WHERE qt.msgid=iot.msgid and qt.q_name = qo.name AND qo.owner = 'SYS' AND iot.msg_enq_time = qt.enq_time AND iot.msg_step_no = qt.step_no AND iot.msg_local_order_no = qt.local_order_no AND iot.msg_chain_no = qt.chain_no UNION ALL SELECT qo.name Q_NAME, cast (null as rowid) ROW_ID, b.msgid MSGID, b.corrid CORRID, b.priority PRIORITY, decode(b.state, 4, 0, 5, 8, 12, 10, 0) STATE, cast (null as TIMESTAMP) DELAY, b.expiration EXPIRATION, cast(FROM_TZ(b.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, b.enq_uid ENQ_UID, cast(null as varchar2(30)) ENQ_TID, cast(null as timestamp) DEQ_TIME, cast (null as number) DEQ_UID, cast (null as varchar2(30)) DEQ_TID, 0 RETRY_COUNT, b.exceptionq_schema EXCEPTION_QUEUE_OWNER, b.exceptionq_name EXCEPTION_QUEUE, 0 CSCN, 0 DSCN, 0 CHAIN_NO, 0 LOCAL_ORDER_NO, cast (null as TIMESTAMP) TIME_MANAGER_INFO, 0 STEP_NO, sys.dbms_aq_bqview.get_adt_payload(b.queue_id, b.msg_num, "SYS"."KUPC$_MESSAGE"(NULL, NULL)) USER_DATA, b.sender_name SENDER_NAME, b.sender_address SENDER_ADDRESS, b.sender_protocol SENDER_PROTOCOL, cast(null as raw(16)) DEQUEUE_MSGID, 'BUFFERED' DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM, qo.qid QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM SYS.qt40238_BUFFER b, sys.all_int_dequeue_queues qo WHERE bitand(b.state, 4) = 4 AND qo.qid =b.queue_id UNION ALL SELECT p.q_name Q_NAME, cast (null as rowid) ROW_ID, p.msgid MSGID, p.corrid CORRID, p.priority PRIORITY, p.state STATE, cast(FROM_TZ(p.delay, '00:00') at time zone sessiontimezone as timestamp) DELAY, p.expiration EXPIRATION, cast(FROM_TZ(p.enq_time, '00:00') at time zone sessiontimezone as timestamp) ENQ_TIME, p.enq_uid ENQ_UID, p.enq_tid ENQ_TID, cast(FROM_TZ(p.deq_time, '00:00') at time zone sessiontimezone as timestamp) DEQ_TIME, p.deq_uid DEQ_UID, p.deq_tid DEQ_TID, p.retry_count RETRY_COUNT, p.exception_qschema EXCEPTION_QSCHEMA, p.exception_queue EXCEPTION_QUEUE, p.cscn CSCN, p.dscn DSCN, p.chain_no CHAIN_NO, p.local_order_no LOCAL_ORDER_NO, cast(FROM_TZ(p.time_manager_info, '00:00') at time zone sessiontimezone as timestamp) TIME_MANAGER_INFO, p.step_no STEP_NO, p.user_data USER_DATA, p.sender_name SENDER_NAME, p.sender_address SENDER_ADDRESS, p.sender_protocol SENDER_PROTOCOL, cast (null as RAW(16)) DEQUEUE_MSGID, 'BUFFERED' DELIVERY_MODE, b.sequence_num SEQUENCE_NUM, b.msg_num MSG_NUM, qo.qid QUEUE_ID, null USER_PROP, b.subscriber_id SUBSCRIBER_ID, cast (null as varchar2(30)) SUBSCRIBER_NAME, 0 QUEUE_EVTID FROM "AQ$_KUPC$DATAPUMP_QUETAB_1_P" p, SYS.qt40238_BUFFER b, sys.all_int_dequeue_queues qo WHERE bitand(b.state, 2) = 2 AND p.msgid = b.msgid AND qo.qid = b.queue_id and p.q_name = qo.name WITH READ ONLY
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |