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