Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to find the last DML operation time of the location table in ORACLE, the article is very detailed, has a certain reference value, interested friends must read it!

1: use ORA_ROWSCN pseudo-columns to get the last DML time of the table

The ORA_ROWSCN pseudo-column was introduced by Oracle 10g, and you can query the SCN that records the last change in the table. Then the SCN can be converted into a timestamp through the SCN_TO_TIMESTAMP function, thus finding the corresponding time of the SCN in the last DML operation. However, by default, the ORA_ROWSCN of each row record is based on Block, unless row-level tracing is turned on when the table is built.

SELECT MAX (ORA_ROWSCN), SCN_TO_TIMESTAMP (MAX (ORA_ROWSCN)) FROM xxx.xxx

As shown below, we can create a table TEST and then check the operation time of the DML at the end of the TEST table. As follows:

SQL > CREATE TABLE TEST.TEST (ID NUMBER); Table created. SQL > COL OWNER FOR A12 WHERE OWNER='TEST' > COL TABLE_NAME FOR A32 > COL MONITORING FOR A32 > SELECT OWNER, TABLE_NAME, MONITORING 2 FROM DBA_TABLES 3 WHERE OWNER='TEST' 4 AND TABLE_NAME='TEST' OWNER TABLE_NAME MONITORING- TEST TEST YESSQL > INSERT INTO TEST.TEST VALUES (1); 1 row created.SQL > COMMIT;Commit complete.SQL > SELECT sysdate FROM DUAL SYSDATE-2018-11-19 14:34:12SQL > SELECT MAX (ORA_ROWSCN), SCN_TO_TIMESTAMP (MAX (ORA_ROWSCN)) FROM TEST.TEST MAX (ORA_ROWSCN) SCN_TO_TIMESTAMP (MAX (ORA_ROWSCN))-52782810 19-NOV-18 02.34.03.000000000 PMSQL >

Using ORA_ROWSCN pseudo-columns to get the latest DML time of the table also has some shortcomings and defects, as shown below:

1: when you use SCN_TO_TIMESTAMP (MAX (ORA_ROWSCN)) to get the last DML operation of the table, you may encounter an ORA-08181 error.

$oerr ora 8181

08181, 00000, "specified number is not a valid system change number"

/ / * Cause: supplied scn was beyond the bounds of a valid scn.

/ / * Action: use a valid scn.

This conversion of SCN and timestamps depends on the data records within the database, which come from the SMON_SCN_TIME base table. Specifically, the SMON_SCN_TIME base table is used to record the mapping between SCN (system change number) and specific timestamps (timestamp) in past time periods, because it is a sampling record. So SMON_SCN_TIME can roughly (inaccurately) locate the time information of a SCN. The actual SMON_SCN_TIME is a cluster table. Moreover, starting from 10g, SMON will also regularly clean up the records in SMON_SCN_TIME, so it cannot be converted for older SCN. It also occurs that some tables in the database will encounter an ORA-08181 error when using the SCN_TO_TIMESTAMP function. As shown below, we will encounter the error of ORA-08181 when we convert a SCN that is 1 smaller than MIN (SCN) in the base table SMON_SCN_TIME.

According to official documents, the SMON process collects and inserts into the SMON_SCN_ time table every 5 minutes, while deleting some historical data (more than 5 days ago).

This is expected behavior as the SCN must be no older than 5 days as part of the current flashback database

Features.

Currently, the flashback query feature keeps track of times up to a

Maximum of 5 days. This period reflects server uptime, not wall-clock

Time. You must record the SCN yourself at the time of interest, such as

Before doing a DELETE.

2: using ORA_ROWSCN pseudo-columns to get the DML operation time of a row in the table may not be accurate, but of course it is accurate to get the last DML time of the table.

By default, the ORA_ROWSCN of each row is based on block, which is not accurate for the last DML time of a row, unless row-level tracking is enabled (create table) when the table is created. Rowdependencies), which is the SCN at the row-level record level. Each block is the SCN that records the most recent transaction of the block in the header, so by default, you only need to get this value directly from the header of the block without any other overhead. But this is obviously imprecise, there are many rows in a block, and each transaction cannot affect all rows in the whole block, so this is a very imprecise estimate, and the ORA_ROWSCN of all records in the same block will be the same. As shown in the following experiment, of course, it is accurate to get the last DML time of the table. So if the ORA_ROWSCN of each line requires precision, row-level tracing must be turned on.

SQL > SELECT * FROM TEST.TEST; ID- 1SQL > SELECT ID, SCN_TO_TIMESTAMP (ORA_ROWSCN) FROM TEST.TEST ID SCN_TO_TIMESTAMP (ORA_ROWSCN)-- 1 19-NOV-18 02.34.03.000000000 PMSQL > INSERT INTO TEST.TEST VALUES (2); 1 row created.SQL > COMMIT Commit complete.SQL > INSERT INTO TEST.TEST VALUES (3); 1 row created.SQL > COMMIT;Commit complete.SQL > SELECT ID, SCN_TO_TIMESTAMP (ORA_ROWSCN) FROM TEST.TEST ID SCN_TO_TIMESTAMP (ORA_ROWSCN)-1 19-NOV-18 03.41.01.000000000 PM 2 19-NOV-18 03.41.01.000000000 PM 3 19-NOV-18 03.41.01.000000000 PM

3: if the data of the table is dropped or all DELETE by TRUNCATE, it will also result in the failure to locate the time of the last DML operation. As follows:

2: use DBA_TAB_MODIFICATIONS to find and set the last DML operation time

DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables

This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure.

Use DBA_TAB_MODIFICATIONS to view the operation time of the last DML of the table, as shown in the following test

SQL > CREATE TABLE TEST.TEST (ID NUMBER); Table created.SQL > COL OWNER FOR A12 WHERE OWNER='TEST' > COL TABLE_NAME FOR A32 > COL MONITORING FOR A32 > SELECT OWNER, TABLE_NAME, MONITORING 2 FROM DBA_TABLES 3 WHERE OWNER='TEST' 4 AND TABLE_NAME='TEST' OWNER TABLE_NAME MONITORING- TEST TEST YESSQL > INSERT INTO TEST.TEST VALUES (1); 1 row created.SQL > COMMIT Commit complete.SQL > ALTER SESSION SET NLS_DATE_FORMAT= "YYYY-MM-DD HH24:MI:SS"; Session altered.SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST' AND TABLE_OWNER='TEST';no rows selectedSQL > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;PL/SQL procedure successfully completed.SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST' AND TABLE_OWNER='TEST' INSERTS UPDATES DELETES TRU TIMESTAMP--100 NO 2018-11-20 10:34:24

However, using DBA_TAB_MODIFICATIONS to locate the last DML operation time of the table also has some limitations. As shown below, some limitations affect the accuracy of locating the time of the last DML operation.

1: if the table does not have the MONITORING property set, the DBA_TAB_MODIFICATIONS view will not collect data about the related table. If the MONITORING property is not set before a table, the time of the last DML operation cannot be found. After setting the MONITORING property, the DML operation time after this setting point is collected in the DBA_TAB_MODIFICATIONS view.

2: the view will not have data until the EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO needs to be executed.

3:DML operations are logged to the view without committing or rolling back. This will lead to inaccurate data.

Non-submission:

Rollback situation:

3: after collecting statistics (ANALYZE or dbms_stats packages collect statistics), the relevant table records in the view will be left empty.

SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST' AND TABLE_OWNER='TEST'; INSERTS UPDATES DELETES TRU TIMESTAMP- 604 YES 2018-11-20 13:14:08SQL > exec dbms_stats.gather_table_stats ('TEST','TEST') PL/SQL procedure successfully completed.SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST' AND TABLE_OWNER='TEST';no rows selectedSQL >

Insert information created by 4:CTAS is not logged. As shown in the following test:

SQL > CREATE TABLE TEST.TEST1 2 AS 3 SELECT * FROM TEST.TEST;Table created.SQL > exec dbms_stats.flush_database_monitoring_info;PL/SQL procedure successfully completed.SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST1' AND TABLE_OWNER='TEST';no rows selected

There is a delay of several seconds for 5:DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to collect data, which is only close to the last DML time, not accurate.

SQL > COL OWNER FOR A12 WHERE OWNER='TEST' > COL TABLE_NAME FOR A32 > COL MONITORING FOR A32 > SELECT OWNER, TABLE_NAME, MONITORING 2 FROM DBA_TABLES 3 WHERE OWNER='TEST' 4 AND TABLE_NAME='TEST1' OWNER TABLE_NAME MONITORING--TEST TEST1 YESSQL > SQL > SELECT SYSDATE FROM DUAL SYSDATE-2018-11-20 10:46:39SQL > INSERT INTO TEST.TEST VALUES (10); 1 row created.SQL > SELECT SYSDATE FROM DUAL;SYSDATE-2018-11-20 10:46:57SQL > COMMIT;Commit complete.SQL > SELECT SYSDATE FROM DUAL SYSDATE-2018-11-20 10:47:07SQL > exec dbms_stats.flush_database_monitoring_info;PL/SQL procedure successfully completed.SQL > SELECT INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP 2 FROM DBA_TAB_MODIFICATIONS 3 WHERE TABLE_NAME='TEST' AND TABLE_OWNER='TEST' INSERTS UPDATES DELETES TRU TIMESTAMP--300 NO 2018-11-20 10:47:13

3: trigger captures the last DML operation time

The last time to capture DML operations using triggers is the most accurate, but it is also the most expensive in performance and is not recommended.

These are all the contents of the article "how to find the time of the last DML operation in the location table in ORACLE". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.