[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用户无法直接访问.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2152854/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2152854/