三、列出一个延迟事务的所有调用

作为一个复制管理员，我们经常需要查看某个延迟事务中到底包含那些调用，而这些调用的参数又是什么。Oracle复制包中没有提供相应的脚本来实现该功能，通常我们的做法只能是借助于Oracle的复制管理器来查看，但是如果延迟事务很多，且没有延迟事务的调用个数也很多的话，Oracle复制管理器非常的慢，而且最重要的是我们根本无法直接操作这些数据。下面这个脚本可以列出延迟队列中的某个事务的所有调用内容，如果再对这个脚本加以改造的话，甚至可以恢复出延迟事务中的Oracle DDL语句。这对于Oracle复制管理员是非常有用的功能。

在对复制环境的管理中，还经常作这样一个工作，如果复制发生错误，将会将错误信息写入错误队列中（deferror视图），系统会显示出在一个延迟事务中错误的调用号，也可以将下面的程序加以改造，让其直接输出某个事务的某个调用。由于在很多情况下，一个事务通常含有很多的调用，将所有的都显示出来没有必要，其实我们更关心其中的某个调用。该存储过程这里就不详述，其实根据下面的这个过程改造是非常容易的。有感兴趣的也可以和我联系。

存储过程 p_list_calls可以列出一个延迟事务中的所有调用的参数类型和值，支持所有的复制类型，包括NCHAR, NVARCHAR和所有的LOB.

运行方法和前面谈到的存储过程一样，首先需要将输出定位到屏幕，

set serveroutput on size 200000

其中参数存储过程的输入参数T为延迟事务的ID号，可以通过视图deferror或者defcall得到，下面是一个典型的调用过程例子：

SQL> select * from deftran;
DEFERRED_TRAN_ID DELIVERY_ORDER D START_TIME
------------------------------ -------------- - ----------
7.0.3741 65040962 R 25-7月 -01
8.41.3747 65040963 R 25-7月 -01
6.18.3739 65040974 R 25-7月 -01
8.39.3746 65040843 R 25-7月 -01
SQL> set serveroutput on size 1000000
SQL> execute p_list_calls(''''7.0.3741'''');
调用顺序: 0
操作： DB_ZGXT.PA_REP_JB.P_REP_DJ_NSRXX_U
参数个数: 12
参数 数据类型 值
-------------------- -------------- ----------------------
01 N_NSRNM VARCHAR2 034530001
02 N_PZWH VARCHAR2 (NULL)
03 N_TBRQ DATE (NULL)
04 N_BGRQ DATE 2000-12-28 00:00:00
05 N_JBR VARCHAR2 (NULL)
06 N_FZR VARCHAR2 (NULL)
07 N_SWJGYJ VARCHAR2 (NULL)
08 N_BZ VARCHAR2 (NULL)
09 N_RYDM VARCHAR2 030811
10 N_BGLRRQ DATE 2000-12-28 14:57:01
11 N_ZHWZBM VARCHAR2 13302030000270999999
12 N_KZBZ CHAR 1
PL/SQL 过程已成功完成。

附：存储过程代码。下载该脚本

CREATE OR REPLACE PROCEDURE P_LIST_CALLS (T IN VARCHAR2) IS
ARGNO NUMBER;
ARGTYP NUMBER;
ARGFORM NUMBER;
CALLNO NUMBER;
TRANID VARCHAR2(30);
TYPDSC CHAR(15);
ROWID_VAL ROWID;
CHAR_VAL VARCHAR2(255);
NCHAR_VAL NVARCHAR2(255);
DATE_VAL DATE;
NUMBER_VAL NUMBER;
VARCHAR2_VAL VARCHAR2(2000);
NVARCHAR2_VAL NVARCHAR2(2000);
RAW_VAL RAW(255);
ARG_NAME VARCHAR2(20);
ARG_NAME_C CHAR(20);
TABLE_NAME VARCHAR2(100);
COL_NAME VARCHAR2(100);
PK_CHAR CHAR(1);

-- 延迟队列光标
CURSOR C_DEFCALL (T VARCHAR2) IS
SELECT CALLNO, DEFERRED_TRAN_ID, SCHEMANAME, PACKAGENAME, PROCNAME,ARGCOUNT
FROM DEFCALL
WHERE DEFERRED_TRAN_ID = T;

-- 获得参数名称
CURSOR C_ARG_NAME (P_SCHEMA VARCHAR2, P_PROCNAME VARCHAR2,
P_PKGNAME VARCHAR2, P_CALL_COUNT VARCHAR2) IS
SELECT ARGUMENT_NAME
FROM ALL_ARGUMENTS
WHERE OWNER = P_SCHEMA
AND PACKAGE_NAME = P_PKGNAME
AND OBJECT_NAME = P_PROCNAME
AND (OVERLOAD = (SELECT OVRLD.OVERLOAD FROM
(SELECT OVERLOAD, OBJECT_NAME, PACKAGE_NAME, MAX(POSITION) POS
FROM ALL_ARGUMENTS
WHERE OBJECT_NAME = P_PROCNAME
AND PACKAGE_NAME = P_PKGNAME
GROUP BY OVERLOAD, OBJECT_NAME, PACKAGE_NAME
) OVRLD
WHERE P_CALL_COUNT = OVRLD.POS
AND OBJECT_NAME = P_PROCNAME
AND PACKAGE_NAME = P_PKGNAME
)
OR OVERLOAD IS NULL
)
ORDER BY POSITION;
-- 该光标用来获得某个列是否为该表的主键
CURSOR PK_CURSOR (SCHEMA VARCHAR2, T_NAME VARCHAR2, COL_NAME VARCHAR2) IS
SELECT DECODE (COUNT(*),1,''''*'''','''' '''')
FROM DBA_CONSTRAINTS T1, DBA_CONS_COLUMNS T2
WHERE T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
AND T1.OWNER = T2.OWNER
AND T1.OWNER = SCHEMA
AND T1.CONSTRAINT_TYPE = ''''P''''
AND T1.TABLE_NAME = T_NAME
AND T2.COLUMN_NAME LIKE COL_NAME;

BEGIN

FOR C1REC IN C_DEFCALL (T) LOOP
DBMS_OUTPUT.PUT_LINE(''''调用顺序: '''' ||C1REC.CALLNO);
DBMS_OUTPUT.PUT_LINE(''''操作： ''''||C1REC.SCHEMANAME||''''.''''||C1REC.PACKAGENAME||''''.''''||C1REC.PROCNAME);
DBMS_OUTPUT.PUT_LINE(''''参数个数: ''''||C1REC.ARGCOUNT);
DBMS_OUTPUT.PUT_LINE('''' 参数 '''' || '''' 数据类型 '''' || ''''值'''');
DBMS_OUTPUT.PUT_LINE('''' ---------------- '''' || ''''---------------- '''' ||''''----------------------'''');
ARGNO := 1;
CALLNO := C1REC.CALLNO;
TRANID := C1REC.DEFERRED_TRAN_ID;
OPEN C_ARG_NAME (C1REC.SCHEMANAME, C1REC.PROCNAME, C1REC.PACKAGENAME,C1REC.ARGCOUNT);
WHILE TRUE LOOP
IF (ARGNO > C1REC.ARGCOUNT) THEN
CLOSE C_ARG_NAME;
EXIT;
END IF;
ARGTYP := DBMS_DEFER_QUERY.GET_ARG_TYPE(CALLNO, ARGNO, TRANID);
ARGFORM := DBMS_DEFER_QUERY.GET_ARG_FORM(CALLNO, ARGNO, TRANID);
FETCH C_ARG_NAME INTO ARG_NAME;
ARG_NAME_C := ARG_NAME;
TABLE_NAME := SUBSTR(C1REC.PACKAGENAME, 1, INSTR(C1REC.PACKAGENAME, ''''$'''') - 1);
COL_NAME := SUBSTR(ARG_NAME, 1, LENGTH(ARG_NAME) - 5) || ''''%'''';
OPEN PK_CURSOR (C1REC.SCHEMANAME, TABLE_NAME, COL_NAME);
FETCH PK_CURSOR INTO PK_CHAR;
CLOSE PK_CURSOR;
IF (ARGTYP = 1 AND ARGFORM = 1) THEN
TYPDSC := ''''VARCHAR2'''';
VARCHAR2_VAL := DBMS_DEFER_QUERY.GET_VARCHAR2_ARG(CALLNO, ARGNO,TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(VARCHAR2_VAL,''''(NULL)''''));
ELSIF ARGTYP = 1 AND ARGFORM = 2 THEN
TYPDSC := ''''NVARCHAR2'''';
NVARCHAR2_VAL := DBMS_DEFER_QUERY.GET_NVARCHAR2_ARG(CALLNO, ARGNO,TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(TRANSLATE(NVARCHAR2_VAL USING CHAR_CS),''''(NULL)''''));
ELSIF ARGTYP = 2 THEN
TYPDSC := ''''NUMBER'''';
NUMBER_VAL := DBMS_DEFER_QUERY.GET_NUMBER_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(TO_CHAR(NUMBER_VAL),''''(NULL)''''));
ELSIF ARGTYP = 11 THEN
TYPDSC := ''''ROWID'''';
ROWID_VAL := DBMS_DEFER_QUERY.GET_ROWID_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(ROWID_VAL,''''(NULL)''''));
ELSIF ARGTYP = 12 THEN
TYPDSC := ''''DATE'''';
DATE_VAL := DBMS_DEFER_QUERY.GET_DATE_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(TO_CHAR(DATE_VAL,''''YYYY-MM-DD HH24:MI:SS''''),''''(NULL)''''));
ELSIF ARGTYP = 23 THEN
TYPDSC := ''''RAW'''';
RAW_VAL := DBMS_DEFER_QUERY.GET_RAW_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(RAW_VAL,''''(NULL)''''));
ELSIF ARGTYP = 96 AND ARGFORM = 1 THEN
TYPDSC := ''''CHAR'''';
CHAR_VAL := DBMS_DEFER_QUERY.GET_CHAR_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(CHAR_VAL,''''(NULL)'''')||''''|'''');
ELSIF ARGTYP = 96 AND ARGFORM = 2 THEN
TYPDSC := ''''NCHAR'''';
NCHAR_VAL := DBMS_DEFER_QUERY.GET_NCHAR_ARG(CALLNO, ARGNO, TRANID);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(TRANSLATE(NCHAR_VAL USING CHAR_CS),''''(NULL)'''')||''''|'''');
ELSIF ARGTYP = 113 THEN
TYPDSC := ''''BLOB'''';
VARCHAR2_VAL := DBMS_LOB.SUBSTR(DBMS_DEFER_QUERY.GET_BLOB_ARG(CALLNO,ARGNO, TRANID));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(VARCHAR2_VAL,''''(NULL)''''));
ELSIF ARGTYP = 112 AND ARGFORM = 1 THEN
TYPDSC := ''''CLOB'''';
VARCHAR2_VAL := DBMS_LOB.SUBSTR(DBMS_DEFER_QUERY.GET_CLOB_ARG(CALLNO,ARGNO, TRANID));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(VARCHAR2_VAL,''''(NULL)''''));
ELSIF ARGTYP = 112 AND ARGFORM = 2 THEN
TYPDSC := ''''NCLOB'''';
NVARCHAR2_VAL := DBMS_LOB.SUBSTR(DBMS_DEFER_QUERY.GET_NCLOB_ARG(CALLNO, ARGNO, TRANID));
DBMS_OUTPUT.PUT_LINE(TO_CHAR(ARGNO,''''09'''')||PK_CHAR||ARG_NAME_C||TYPDSC||'''' ''''||NVL(TRANSLATE(NVARCHAR2_VAL USING CHAR_CS),''''(NULL)''''));
END IF;
ARGNO := ARGNO + 1;
END LOOP;
END LOOP;
END;
/

上一页  [1] [2]