为了对于数据库中的内容进行履历跟踪,使用了Logminer.但是,
由于客户可能对Oracle不了解,所以,需要做成可以知道INSERT,UPDATE和DELETE
的具体信息.
问题:
1.对于INSERT,可以使用DBMS_LOGMNR.MINE_VALUE函数取得各项目的值,取得不到的就是
NULL,但是对于UPDATE,只能取到UNDO_SQL或REDO_SQL中出现的字段,对于没出现的自段
无法知道.如何解决?
比如:
表A有A1,A2两个字段,UPDATE语句如下:
UPDATE A SET A1 = "1";
这种情况下,应该是取不到A2的值的.2.如果上面没有办法实现的话,可否取得UPDATE和DELETE的行数?
由于客户可能对Oracle不了解,所以,需要做成可以知道INSERT,UPDATE和DELETE
的具体信息.
问题:
1.对于INSERT,可以使用DBMS_LOGMNR.MINE_VALUE函数取得各项目的值,取得不到的就是
NULL,但是对于UPDATE,只能取到UNDO_SQL或REDO_SQL中出现的字段,对于没出现的自段
无法知道.如何解决?
比如:
表A有A1,A2两个字段,UPDATE语句如下:
UPDATE A SET A1 = "1";
这种情况下,应该是取不到A2的值的.2.如果上面没有办法实现的话,可否取得UPDATE和DELETE的行数?
但是对这个表,如果有不止一次的更新,那么还是无法取得当时的A2,只能取得最新的值.
同样,update和delete行数也有同样的问题.
“最新”,呵呵,似乎要你手工去清除分析结果,加新的日志,分析之,来得到了
另外,还有一个问题,当对数据库或者表使用了SUPPLEMENTAL之后,据说可以得到具体的信息.
但是使用了SUPPLEMENTAL之后,如何取得具体的信息,查了半天文档也没有找到,如何处理?
Supplemental Logging -------------------------------------------------
Oracle9i has the ability to log columns in the redo which are not actually changed as part of the DML statements. This is useful for maintaining copies of tables on other databases. Prior to 9i, LogMiner only returned the columns which were changed and identified the row with a WHERE clause with a ROWID. But, ROWIDs are not portable to other databases so it was not possible to extract SQL using LogMiner which could be used on other databases. The ROWID is still included in V$LOGMNR_CONTENTS.sql_redo, but can be removed if necessary.
There are two types of supplemental logging: database and table. ***
Database supplemental logging Database supplemental logging allows you to specify logging of primary keys, unique indexes or both. With this enabled, whenever a DML is performed, the columns involved in the primary key or unique index are always logged even if they were not involved in the DML. To turn on database-wide supplemental logging for both primary keys and unique indexes, execute the following:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; This only takes effect for statements which have not yet been parsed. It also invalidates all DML cursors in the cursor cache and therefore has an effect on performance until the cache is repopulated. 1. The EMP table has a primary key defined on the EMPID column. --> If supplemental logging is turned on for primary key columns, then any update to EMP logs the EMPID column. SQL> select * from emp; EMPID SAL ---------- ---------- 10 100000 SQL> update emp set sal=150000; 1 row updated. --> Without supplemental logging, V$LOGMNR_CONTENTS.sql_redo contains: update "SCOTT"."EMP" set "SAL" = '150000' where "SAL" = '100000' and ROWID ='AAABOaAABAAANZ/AAA'; But, with the log group test_always defined above, V$LOGMNR_CONTENTS.sql_redo contains: update "SCOTT"."EMP" set "SAL" = '150000' where "EMPID" = '10' and "SAL" ='100000' and ROWID = 'AAABOaAABAAANZ/AAA'; 2. To turn off the supplemental logging, execute the following: SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
*** Table-level supplemental logging
Table-level supplemental logging allows users to define log groups and specify which columns are always logged in the redo stream. It is done on a table-by-table basis. The ALWAYS keyword is used to indicate that if a row is updated, all columns in the group are logged. If ALWAYS is not used, the columns in the log group are logged when at least one of the columns in the group is updated.