LogMiner的东东

显示全部楼层 · 2008-11-27 14:56:58

今天做了一下LogMiner的程式，书上说缺省是只看到DML，如果要看DDL，则要加上某些参数。
奇怪了，我做出来的只有DDL，想看看update竟然不显示了，，，
SQL> exec dbms_logmnr.add_logfile(LogFileName => 'c:\joan\redo01.log',Options => dbms_logmnr.NEW);

PL/SQL procedure successfully completed

SQL> exec dbms_logmnr.start_logmnr(DictFileName => 'c:\oracle\Ora11g.dict');

PL/SQL procedure successfully completed

SQL> select sql_redo from v$logmnr_contents where sql_redo like '%pfe%';

SQL_REDO
--------------------------------------------------------------------------------

create table pfe as select empno,ename,sal from emp
;

create table pfe as select empno,ename,sal from emp
;

SQL> select sql_redo from v$logmnr_contents where sql_redo like '%PFE%';

SQL_REDO
--------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME"

pfe是我新建的表，我用create table pfe as select empno,ename,sal from emp建立的表，我用Update修改了最后一项的Sal并提交了，可是怎么看不到呢？

千问 · 2008-11-27 14:56:58

没有人做LogMiner么？我以前在10g下是这样的情况，现在在11g了还是这样的结果，
想要的Update看不到啊，

千问 · 2008-11-27 14:56:58

你的日志是最新的么。你分析的是当前日志么。

千问 · 2008-11-27 14:56:58

不会的啊,是不是你的update语句写到其他的日志文件中了?把其他的日志文件都添加进来试试.

千问 · 2008-11-27 14:56:58

不会吧，我确定就是当前联机日志啊。刚才又重复了一下。选Insert一条记录，后Commit，把当前联机文档归档了，用归档文件来分析，还是一无所获啊，
晕了，

千问 · 2008-11-27 14:56:58

你把你所有的日志成员都分析一下。

千问 · 2008-11-27 14:56:58

原帖由 foxmile 于 2008-1-19 15:19 发表
你把你所有的日志成员都分析一下。
还有忘记说了，logminer分析之后，是元sql语句。
不是和你最初的语句一样的。

千问 · 2008-11-27 14:56:58

原帖由 foxmile 于 2008-1-19 15:20 发表
还有忘记说了，logminer分析之后，是元sql语句。
不是和你最初的语句一样的。
不晓得你说的元语句是什么东东，我重建了一个表abcd，新建了字典文件，把三个联机日志全加进去了，还是没有啊，
SQL> column sql_redo format a1000
SQL> select sql_redo from v$logmnr_contents where upper(sql_redo) like '%ABCD%';

SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('69852','69852','81','ABCD','1',NULL,'2',TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1','81',NULL,NULL,NULL);

create table abcd as select empno,ename,sal from emp
;

SQL> select * from abcd;

EMPNO ENAME
SAL
----- ---------- ---------
7369 SMITH 800.00
7499 ALLEN1600.00
7521 WARD 1250.00
7566 JONES2975.00
7654 MARTIN 1250.00
7698 BLAKE2850.00
7782 CLARK2450.00
7788 SCOTT3000.00
7839 KING 5000.00
7844 TURNER 1500.00
7876 ADAMS1100.00
7900 JAMES 950.00
7902 FORD 3000.00
7934 MILLER 1300.00
8000 PEIFEI 5800.00

15 rows selected

事实上可以看到我已在表中Insert了一行并提交过了。。。

千问 · 2008-11-27 14:56:58

insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('69852','69852','81','ABCD','1',NULL,'2',TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1','81',NULL,NULL,NULL);

你说这个语句是我执行的还是Oracle执行的啊？

千问 · 2008-11-27 14:56:58

原帖由 Charele 于 2008-1-19 15:34 发表
insert into "SYS"."OBJ$"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('69852','69852','81','ABCD','1',NULL,'2',TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),TO_DATE('19-1月 -08', 'DD-MON-RR'),'1',NULL,NULL,'0',NULL,'6','1','81',NULL,NULL,NULL);

你说这个语句是我执行的还是Oracle执行的啊？
这个是oracle的。