This is one in the list of my Example Series in this blog. Check this blog for more possible examples.
If your database is in Archivelog Mode and you have already enabled Supplemental Logging before the rows were create(This is done using ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;), You can use LogMiner to find the changes in the database for any user. My test example here finds changes made by TEST user from today. You can change the username to any user and add more columns from V$LOGMNR_CONTENTS view.
SQL> connect /as sysdba
Connected.
SQL>
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME > TRUNC(SYSDATE);
NAME
---------------------------------------------------------------------------------------
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_99_3LMO6WJF_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC
C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC
10 rows selected.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_101_3LMOFN8Q_.ARC',OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_100_3LMOF7HQ_.ARC');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_102_3LMQ0ZGL_.ARC');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_103_3LMQ1756_.ARC');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\FLASH_RECOVERY_AREA\TESTDB\ARCHIVELOG\2007_11_01\O1_MF_1_104_3LMQ1D0D_.ARC');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> set lines 200
SQL> set pages 9999
SQL> col USERNAME format a10
SQL> col XID format a10
SQL> col SQL_REDO format a40
SQL> col SQL_UNDO format a40
SQL>
SQL> SELECT username,session#,
to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp,
(XIDUSN '.' XIDSLT '.' XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS WHERE username = 'TEST'
2
SQL>
--- Your results Display here
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
SQL>
Wednesday, 14 May 2008
Friday, 2 May 2008
Row-level Dependency Tracking ROWDEPENDENCIES Example
This is one in the list of my Example Series in this blog. Check this blog for more possible examples
Oracle 10g provided one of the best features I like using. This is the pseudo column ORA_ROWSCN. Using scn_to_timestamp with ORA_ROWSCN, you can find the timestamp your block or row was changed. But this does not come automatically without the usual changes and space (about 6 bytes per row).
By default, Oracle 10g tables are created with the clause NOROWDEPENDENCIES. This means that Block-level Dependency Tracking is used. You can create your table with ROWDEPENDENCIES thereby enabling Row-level Dependency Tracking.
SQL> CREATE TABLE TESTTAB
(COL1 NUMBER(5),
COL2 VARCHAR2(30),
COL3 DATE)
ROWDEPENDENCIES;
2 3 4 5
Table created.
SQL>
I will demonstrate with EMP table which has NOROWDEPENDENCIES
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM SMITH 7369 2000
02-MAY-08 11.49.00.000000000 AM ALLEN 7499 1600
02-MAY-08 11.49.00.000000000 AM WARD 7521 1250
02-MAY-08 11.49.00.000000000 AM JONES 7566 2975
02-MAY-08 11.49.00.000000000 AM MARTIN 7654 1250
02-MAY-08 11.49.00.000000000 AM BLAKE 7698 2850
02-MAY-08 11.49.00.000000000 AM CLARK 7782 2450
02-MAY-08 11.49.00.000000000 AM SCOTT 7788 3000
02-MAY-08 11.49.00.000000000 AM KING 7839 5000
02-MAY-08 11.49.00.000000000 AM TURNER 7844 1500
02-MAY-08 11.49.00.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM JAMES 7900 950
02-MAY-08 11.49.00.000000000 AM FORD 7902 3000
02-MAY-08 11.49.00.000000000 AM MILLER 7934 1300
14 rows selected.
SQL> update emp set sal=3000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
As you can see from the result below, there was NO row level tracking
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM SMITH 7369 3000
02-MAY-08 11.54.10.000000000 AM ALLEN 7499 1600
02-MAY-08 11.54.10.000000000 AM WARD 7521 1250
02-MAY-08 11.54.10.000000000 AM JONES 7566 2975
02-MAY-08 11.54.10.000000000 AM MARTIN 7654 1250
02-MAY-08 11.54.10.000000000 AM BLAKE 7698 2850
02-MAY-08 11.54.10.000000000 AM CLARK 7782 2450
02-MAY-08 11.54.10.000000000 AM SCOTT 7788 3000
02-MAY-08 11.54.10.000000000 AM KING 7839 5000
02-MAY-08 11.54.10.000000000 AM TURNER 7844 1500
02-MAY-08 11.54.10.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM JAMES 7900 950
02-MAY-08 11.54.10.000000000 AM FORD 7902 3000
02-MAY-08 11.54.10.000000000 AM MILLER 7934 1300
14 rows selected.
I will now demonstrate with EMPDPD table which has ROWDEPENDENCIES
SQL> create table empdpd ROWDEPENDENCIES as select * from emp;
Table created.
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM SMITH 7369 3000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300
14 rows selected.
SQL> update empdpd set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
As you can see from the result below, there was Row level tracking
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.56.20.000000000 AM SMITH 7369 4000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300
14 rows selected.
SQL>select table_name,dependencies from user_tables;
TABLE_NAME DEPENDEN
------------------------------ --------
EMP DISABLED
DEPT DISABLED
BONUS DISABLED
SALGRADE DISABLED
DUMMY DISABLED
EMPDPD ENABLED
TESTTAB ENABLED
EXTERNAL_TEST1 DISABLED
TEST1 DISABLED
TEST2 DISABLED
EXTERNAL_TEST DISABLED
11 rows selected.
SQL>
Note: using ROWDEPENDENCIES will normally add about 6 bytes to each row. You need to read moer from Oracle Documentation for limitations/restrictions
Oracle 10g provided one of the best features I like using. This is the pseudo column ORA_ROWSCN. Using scn_to_timestamp with ORA_ROWSCN, you can find the timestamp your block or row was changed. But this does not come automatically without the usual changes and space (about 6 bytes per row).
By default, Oracle 10g tables are created with the clause NOROWDEPENDENCIES. This means that Block-level Dependency Tracking is used. You can create your table with ROWDEPENDENCIES thereby enabling Row-level Dependency Tracking.
SQL> CREATE TABLE TESTTAB
(COL1 NUMBER(5),
COL2 VARCHAR2(30),
COL3 DATE)
ROWDEPENDENCIES;
2 3 4 5
Table created.
SQL>
I will demonstrate with EMP table which has NOROWDEPENDENCIES
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM SMITH 7369 2000
02-MAY-08 11.49.00.000000000 AM ALLEN 7499 1600
02-MAY-08 11.49.00.000000000 AM WARD 7521 1250
02-MAY-08 11.49.00.000000000 AM JONES 7566 2975
02-MAY-08 11.49.00.000000000 AM MARTIN 7654 1250
02-MAY-08 11.49.00.000000000 AM BLAKE 7698 2850
02-MAY-08 11.49.00.000000000 AM CLARK 7782 2450
02-MAY-08 11.49.00.000000000 AM SCOTT 7788 3000
02-MAY-08 11.49.00.000000000 AM KING 7839 5000
02-MAY-08 11.49.00.000000000 AM TURNER 7844 1500
02-MAY-08 11.49.00.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM JAMES 7900 950
02-MAY-08 11.49.00.000000000 AM FORD 7902 3000
02-MAY-08 11.49.00.000000000 AM MILLER 7934 1300
14 rows selected.
SQL> update emp set sal=3000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
As you can see from the result below, there was NO row level tracking
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM SMITH 7369 3000
02-MAY-08 11.54.10.000000000 AM ALLEN 7499 1600
02-MAY-08 11.54.10.000000000 AM WARD 7521 1250
02-MAY-08 11.54.10.000000000 AM JONES 7566 2975
02-MAY-08 11.54.10.000000000 AM MARTIN 7654 1250
02-MAY-08 11.54.10.000000000 AM BLAKE 7698 2850
02-MAY-08 11.54.10.000000000 AM CLARK 7782 2450
02-MAY-08 11.54.10.000000000 AM SCOTT 7788 3000
02-MAY-08 11.54.10.000000000 AM KING 7839 5000
02-MAY-08 11.54.10.000000000 AM TURNER 7844 1500
02-MAY-08 11.54.10.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM JAMES 7900 950
02-MAY-08 11.54.10.000000000 AM FORD 7902 3000
02-MAY-08 11.54.10.000000000 AM MILLER 7934 1300
14 rows selected.
I will now demonstrate with EMPDPD table which has ROWDEPENDENCIES
SQL> create table empdpd ROWDEPENDENCIES as select * from emp;
Table created.
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM SMITH 7369 3000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300
14 rows selected.
SQL> update empdpd set sal=4000 where empno=7369;
1 row updated.
SQL> commit;
Commit complete.
As you can see from the result below, there was Row level tracking
SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.56.20.000000000 AM SMITH 7369 4000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100
SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300
14 rows selected.
SQL>select table_name,dependencies from user_tables;
TABLE_NAME DEPENDEN
------------------------------ --------
EMP DISABLED
DEPT DISABLED
BONUS DISABLED
SALGRADE DISABLED
DUMMY DISABLED
EMPDPD ENABLED
TESTTAB ENABLED
EXTERNAL_TEST1 DISABLED
TEST1 DISABLED
TEST2 DISABLED
EXTERNAL_TEST DISABLED
11 rows selected.
SQL>
Note: using ROWDEPENDENCIES will normally add about 6 bytes to each row. You need to read moer from Oracle Documentation for limitations/restrictions
Subscribe to:
Posts (Atom)