ORACLE STREAM ERROR

Subject:	Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786
	Doc ID:	Note:265201.1	Type:	TROUBLESHOOTING
	Last Revision Date:	08-FEB-2008	Status:	PUBLISHED

SET SERVEROUTPUT ON;

DECLARE
lcr SYS.AnyData;
BEGIN
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(3, '3.39.138135');
print_lcr(lcr);
END;
/

CREATE OR REPLACE PROCEDURE print_any (DATA IN ANYDATA)
IS
tn VARCHAR2 (61);
str VARCHAR2 (4000);
CHR VARCHAR2 (1000);
num NUMBER;
dat DATE;
rw RAW (4000);
res NUMBER;
BEGIN
IF DATA IS NULL THEN
DBMS_OUTPUT.put_line ('NULL value');
RETURN;
END IF;

tn := DATA.gettypename ();

IF tn = 'SYS.VARCHAR2' THEN
res := DATA.getvarchar2 (str);
DBMS_OUTPUT.put_line (SUBSTR (str, 0, 253));
ELSIF tn = 'SYS.CHAR' THEN
res := DATA.getchar (CHR);
DBMS_OUTPUT.put_line (SUBSTR (CHR, 0, 253));
ELSIF tn = 'SYS.VARCHAR' THEN
res := DATA.getvarchar (CHR);
DBMS_OUTPUT.put_line (CHR);
ELSIF tn = 'SYS.NUMBER' THEN
res := DATA.getnumber (num);
DBMS_OUTPUT.put_line (num);
ELSIF tn = 'SYS.DATE' THEN
res := DATA.getdate (dat);
DBMS_OUTPUT.put_line (dat);
ELSIF tn = 'SYS.RAW' THEN
res := data.GETRAW(rw);
DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
DBMS_OUTPUT.put_line ('BLOB Value');
ELSIF tn = 'SYS.BLOB' THEN
DBMS_OUTPUT.put_line ('BLOB Found');
ELSE
DBMS_OUTPUT.put_line ('typename is ' || tn);
END IF;
END print_any;

CREATE OR REPLACE PROCEDURE print_lcr (lcr IN ANYDATA)
IS
typenm VARCHAR2 (61);
ddllcr SYS.lcr$_ddl_record;
proclcr SYS.lcr$_procedure_record;
rowlcr SYS.lcr$_row_record;
res NUMBER;
newlist SYS.lcr$_row_list;
oldlist SYS.lcr$_row_list;
ddl_text CLOB;
ext_attr ANYDATA;
BEGIN
typenm := lcr.gettypename ();
DBMS_OUTPUT.put_line ('type name: ' || typenm);
IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
res := lcr.getobject (ddllcr);
DBMS_OUTPUT.put_line ( 'source database: ' || ddllcr.get_source_database_name);
DBMS_OUTPUT.put_line ('owner: ' || ddllcr.get_object_owner);
DBMS_OUTPUT.put_line ('object: ' || ddllcr.get_object_name);
DBMS_OUTPUT.put_line ('is tag null: ' || ddllcr.is_null_tag);
DBMS_LOB.createtemporary (ddl_text, TRUE);
ddllcr.get_ddl_text (ddl_text);
DBMS_OUTPUT.put_line ('ddl: ' || ddl_text);
-- Print extra attributes in DDL LCR
ext_attr := ddllcr.get_extra_attribute ('serial#');
IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := ddllcr.get_extra_attribute ('session#');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := ddllcr.get_extra_attribute ('thread#');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := ddllcr.get_extra_attribute ('tx_name');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ( 'transaction name: ' || ext_attr.accessvarchar2 ());
END IF;

ext_attr := ddllcr.get_extra_attribute ('username');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());
END IF;

DBMS_LOB.freetemporary (ddl_text);
ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
res := lcr.getobject (rowlcr);
DBMS_OUTPUT.put_line ( 'source database: ' || rowlcr.get_source_database_name);
DBMS_OUTPUT.put_line ('owner: ' || rowlcr.get_object_owner);
DBMS_OUTPUT.put_line ('object: ' || rowlcr.get_object_name);
DBMS_OUTPUT.put_line ('is tag null: ' || rowlcr.is_null_tag);
DBMS_OUTPUT.put_line ('command_type: ' || rowlcr.get_command_type);
oldlist := rowlcr.get_values ('old');

FOR i IN 1 .. oldlist.COUNT LOOP
IF oldlist (i) IS NOT NULL THEN
DBMS_OUTPUT.put_line ('old(' || i || '): ' || oldlist (i).column_name);
print_any (oldlist (i).DATA);
END IF;
END LOOP;

newlist := rowlcr.get_values ('new', 'n');

FOR i IN 1 .. newlist.COUNT
LOOP
IF newlist (i) IS NOT NULL THEN
DBMS_OUTPUT.put_line ('new(' || i || '): ' || newlist (i).column_name);
print_any (newlist (i).DATA);
END IF;
END LOOP;

-- Print extra attributes in row LCR
ext_attr := rowlcr.get_extra_attribute ('row_id');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('row_id: ' || ext_attr.accessurowid ());
END IF;

ext_attr := rowlcr.get_extra_attribute ('serial#');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := rowlcr.get_extra_attribute ('session#');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := rowlcr.get_extra_attribute ('thread#');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());
END IF;

ext_attr := rowlcr.get_extra_attribute ('tx_name');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ( 'transaction name: ' || ext_attr.accessvarchar2 ());
END IF;

ext_attr := rowlcr.get_extra_attribute ('username');

IF (ext_attr IS NOT NULL) THEN
DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());
END IF;
ELSE
DBMS_OUTPUT.put_line ('Non-LCR Message with type ' || typenm);
END IF;
END print_lcr;

CREATE OR REPLACE PROCEDURE print_errors
IS
CURSOR c IS
SELECT local_transaction_id, source_database, message_number, message_count, error_number, error_message
FROM dba_apply_error
ORDER BY source_database, source_commit_scn;

i NUMBER;
txnid VARCHAR2 (30);
SOURCE VARCHAR2 (128);
msgno NUMBER;
msgcnt NUMBER;
errnum NUMBER := 0;
errno NUMBER;
errmsg VARCHAR2 (255);
lcr ANYDATA;
r NUMBER;
BEGIN
FOR r IN c LOOP
errnum := errnum + 1;
msgcnt := r.message_count;
txnid := r.local_transaction_id;
SOURCE := r.source_database;
msgno := r.message_number;
errno := r.error_number;
errmsg := r.error_message;
DBMS_OUTPUT.put_line('*************************************************');
DBMS_OUTPUT.put_line ('----- ERROR #' || errnum);
DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);
DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);
DBMS_OUTPUT.put_line ('----Error Number: ' || errno);
DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);

FOR i IN 1 .. msgcnt LOOP
DBMS_OUTPUT.put_line ('--message: ' || i);
lcr := DBMS_APPLY_ADM.get_error_message (i, txnid);
print_lcr (lcr);
END LOOP;
END LOOP;
END print_errors;

CREATE OR REPLACE PROCEDURE print_transaction (ltxnid IN VARCHAR2)
IS
i NUMBER;
txnid VARCHAR2 (30);
SOURCE VARCHAR2 (128);
msgno NUMBER;
msgcnt NUMBER;
errno NUMBER;
errmsg VARCHAR2 (128);
lcr ANYDATA;
BEGIN
SELECT local_transaction_id, source_database, message_number, message_count, error_number, error_message
INTO txnid, SOURCE, msgno, msgcnt, errno, errmsg
FROM dba_apply_error
WHERE local_transaction_id = ltxnid;

DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);
DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);
DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);
DBMS_OUTPUT.put_line ('----Error Number: ' || errno);
DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);

FOR i IN 1 .. msgcnt
LOOP
DBMS_OUTPUT.put_line ('--message: ' || i);
lcr := DBMS_APPLY_ADM.get_error_message (i, txnid); -- gets the LCR
print_lcr (lcr);
END LOOP;
END print_transaction;

SQL> SET SERVEROUTPUT ON SIZE 999999
SQL> EXEC print_errors

SET SERVEROUTPUT ON SIZE 999999
EXEC print_transaction('3.39.138135')

BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(LOCAL_TRANSACTION_ID => '3.39.138135',EXECUTE_AS_USER => FALSE);
END;

SET SERVEROUTPUT ON;

DECLARE
lcr SYS.AnyData;
BEGIN
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(1, '1.5.48006');
print_lcr(lcr);
END;
/

https://metalink.oracle.com/metalink/plsql/f?p=130:14:3755128476560223575::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,265201.1,1,1,1,helvetica

Subject:	Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786
	Doc ID:	Note:265201.1	Type:	TROUBLESHOOTING
	Last Revision Date:	08-FEB-2008	Status:	PUBLISHED

http://download.oracle.com/docs/cd/B28359_01/server.111/b28322/troub_rep.htm#sthref703

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242506/，如需轉載，請註明出處，否則將追究法律責任。