[20180412]logminer使用問題(10g).txt

[20180412]logminer使用問題(10g).txt

--//今天使用logminer下遇到一個問題,做一個記錄:
1.環境:

SYSTEM@xxx> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

--//以system使用者登入執行:
BEGIN
DBMS_LOGMNR.START_LOGMNR
(
STARTTIME   => '2018/04/12 08:00:00'
,ENDTIME     => '2018/04/12 09:00:00'
,OPTIONS     =>   DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ DBMS_LOGMNR.CONTINUOUS_MINE
+ DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
END;
/

Error at line 1
ORA-06550: line 6, column 19:
PLS-00201: identifier 'DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

--//奇怪,我以前從來沒有遇到這個問題.我換成sys使用者執行正常,為什麼system使用者報錯呢?我在toad下做了跟蹤,發現寫成如下OK.

BEGIN
SYS.DBMS_LOGMNR.START_LOGMNR
(
STARTTIME   => '2018/04/12 08:00:00'
,ENDTIME     => '2018/04/12 09:00:00'
,OPTIONS     =>   SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
+ SYS.DBMS_LOGMNR.CONTINUOUS_MINE
+ SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY
);
END;
/

--//為什麼11g不需要前面的sys呢?
--//如果檢查dba_objects就能發現問題:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> select * from dba_objects where object_name='DBMS_LOGMNR';
OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS    DBMS_LOGMNR                          12291                PACKAGE BODY        2013-08-24 11:41:40 2013-08-24 11:41:40 2013-08-24:11:41:40 VALID   N N N          2
SYS    DBMS_LOGMNR                           8504                PACKAGE             2013-08-24 11:39:33 2013-08-24 11:39:33 2013-08-24:11:39:33 VALID   N N N          1
PUBLIC DBMS_LOGMNR                           8505                SYNONYM             2013-08-24 11:39:33 2013-08-24 11:39:33 2013-08-24:11:39:33 VALID   N N N          1

--//而10g下:

SYSTEM@xxx> select * from dba_objects where object_name='DBMS_LOGMNR';

OWNER  OBJECT_NAME          SUBOBJECT_  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - -
SYS    DBMS_LOGMNR                           5575                PACKAGE             2006-05-15 10:35:43 2012-01-17 07:41:46 2006-05-15:10:35:43 VALID   N N N
SYS    DBMS_LOGMNR                           5576                PACKAGE BODY        2006-05-15 10:35:43 2006-05-16 10:53:59 2006-05-16:10:53:59 VALID   N N N

--//在10g下oracle沒有定義同義詞,導致system使用者無法直接訪問.