There are
various way of tracking DDL,DML activities on a table. Below are some SQL
command and package to get the same info.
1.
First of all we will check
the existing table and its last DDL.
SELECT OBJECT_NAME Table_Name,
TIMESTAMP LAST_DDL
FROM all_objects
WHERE object_name='EMP';
TABLE_NAME
LAST_DDL
------------------------------ -------------------
EMP 2015-11-05:08:14:17
Above example shows when the last date of DDL. Let’s see is if we alter
anything or if we do any DDL then how it reacts:
ALTER TABLE scott.emp MODIFY job
VARCHAR2(20);
Table SCOTT.EMP altered.
Now table is just altered so if we run the previous query to check the
DDL activity then it will be like below:
SELECT OBJECT_NAME Table_Name,
TIMESTAMP LAST_DDL
FROM all_objects
WHERE object_name='EMP';
TABLE_NAME LAST_DDL
------------------------------ -------------------
EMP
2015-11-05:08:38:34
So you can find the timestamp difference when it has changed.
2. Finding out last DML activities on a table.
Oracle internally to track how many inserts, updates and deletes have
been done to a table using the table ALL_TAB_MODIFICATIONS. Also it holds table
partition or table subpartition since the segment last had stats gathered on it
with dbms_stats. It also records if the segment has been truncated since the
last stats gather.
SELECT TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS, UPDATES,
DELETES,TRUNCATED,
TIMESTAMP
FROM ALL_TAB_MODIFICATIONS;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS
UPDATES DELETES TRUNCATED
TIMESTAMP
---------- -------------- ------------------ ------ ------- -------- ---------- ----------
ACTIVE_SESSION ACTIVE_52_564 NULL 852 0 0 NO
05-NOV-2015 08:36:21
DB_CACHE_ADV DB_CAC_52_540 NULL 483 0 0
NO 03-NOV-2015 11:37:38
The information on inserts/updates and
deletes is gathered for pretty much all DML against tables (see an up-coming
post for an example of this not being true).
Direct load SQL*Loader and other
direct-io activity can skip being recorded but insert-append, using the /*+
append */ hint is recorded correctly.
The information is only flushed down when
stats are gathered against the segment OR you manually flush the information
down to the database.
Flushing the latest information is
achieved with DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. It is relatively quick
to run, normally taking less than a few seconds.
When statistics are gathered on a
segment, any corresponding rows in DBA_TAB_MODIFOCATIONS is deleted, not
updated to zeros, and is recreated only when relevent inserts,updates, deletes
or truncates occur on the segment.
desc ALL_TAB_MODIFICATIONS;
Name
Null? Type
-----------------------------------------------------
-------- ------------
TABLE_OWNER
VARCHAR2(30)
TABLE_NAME
VARCHAR2(30)
PARTITION_NAME
VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS
NUMBER
UPDATES
NUMBER
DELETES
NUMBER
TIMESTAMP
DATE
TRUNCATED
VARCHAR2(3)
DROP_SEGMENTS
NUMBER
Let’s see how it works and how to flush
Create table EMP_DTLS as select * from
employee;
SELECT
OBJECT_NAME Table_Name,
TIMESTAMP LAST_DDL
FROM
all_objects
WHERE
object_name='EMP_DTLS';
TABLE_NAME LAST_DDL
------------------------------
-------------------
EMP_DTLS 2015-11-05:10:03:52
insert
into EMP_DTLS select * from scott.emp;
18 rows inserted.
SELECT
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TRUNCATED,
TIMESTAMP
FROM
ALL_TAB_MODIFICATIONS
WHERE
TABLE_NAME='EMP_DTLS';
no rows selected.
EXEC
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
Now run the below query
SELECT
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TRUNCATED,
TIMESTAMP
FROM
ALL_TAB_MODIFICATIONS
WHERE
TABLE_NAME='EMP_DTLS';
TABLE_NAME PARTITION_NAME
SUBPARTITION_NAME INSERTS UPDATES
DELETES TRUNCATE TIMESTAMP
------------------ -------------------------
------------------------------ ----------- ----------- ------------ ------------
------------
EMP_DTLS NULL
NULL 18 0
0 NO
05-NOV-2015 11:31:02
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO this
procedure flushes in-memory monitoring information for all tables in the
dictionary. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS
and *_IND_STATISTICS views are updated immediately, without waiting for the
Oracle database to flush them periodically. This procedure is useful when you
need up-to-date information in those views. Because the GATHER_*_STATS procedures
internally flush monitoring information, it is not necessary to run this
procedure before gathering the statistics.
We also can find the last DML activity
using below SQL also however it will not give us the no of records inserted or
deleted or updated but it will give you the last DML date and time.Also for
ora_scn and scn_to_timestamp will not be required to run the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
procedure to run.
SELECT
MAX(ora_rowscn) SCN_NUM, scn_to_timestamp(MAX(ora_rowscn)) TIMESTAMP FROM
EMP_DTLS ;
SCN_NUM TIMESTAMP
-------------- ----------------------------
3250924 05-NOV-15
11.29.25.000000000
ORA_ROWSCN returns the conservative upper bound system change
number (SCN) of the most recent change to the row. This pseudocolumn is useful
for determining approximately when a row was last updated. It is not absolutely
precise, because Oracle tracks SCNs by transaction committed for the block in
which the row resides.
SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a
system change number (SCN), and returns the approximate timestamp associated
with that SCN.
No comments:
Post a Comment