Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to locate the latest DML time of the data table by Oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how Oracle locates the most recent DML time of the data table.

Because there are too many tables in the database for a long time, many people have manipulated them before and after, and they don't know which tables are useful or useless, so I want to determine which data tables have not been operated recently by judging the last DML time of the data table.

SQL > create table An as select id,name from t_employee

Table created

SQL > select tb.table_name,tb.monitoring from user_tables tb where table_name='A'

TABLE_NAME MONITORING

A YES

As you can see, the datasheet defaults to enabling the monitoring function.

Next, locate the last dml time of Table A through ORA_ROWSCN.

SQL > select max (ora_rowscn), scn_to_timestamp (max (ora_rowscn)) from mvs.A

MAX (ORA_ROWSCN) SCN_TO_TIMESTAMP (MAX (ORA_ROWSC)

-

155220760 29-November-11 11.25.50.000000000 a.m.

Some people say that as long as the table is in monitoring state, you can see it from the view user_tab_modifications, but in fact nothing is found.

SQL > select * from user_tab_modifications where table_name='A'

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED DROP_SEGMENTS

--

Delete the data in An and there is only one item left.

SQL > select max (ora_rowscn), scn_to_timestamp (max (ora_rowscn)) from mvs.A

MAX (ORA_ROWSCN) SCN_TO_TIMESTAMP (MAX (ORA_ROWSC)

-

155223006 29-November-11 11.46.33.000000000 a.m.

And then insert another record.

SQL > insert into a (id,name) values

1 row inserted

SQL > commit

Commit complete

View the record and the corresponding pseudo column ORA_ ROWSCN value.

SQL > select id,name,ora_rowscn from an order by id

ID NAME ORA_ROWSCN

1 test 155223032

1108 s11 155223006

SQL >

The last DML time of the row of data can be obtained through the pseudo column ORA_ROWSCN above and the function SCN_TO_TIMESTAMP (ORA_ROWSCN).

SQL > insert into a (id,name) values (2m)

1 row inserted

SQL > insert into a (id,name) values

1 row inserted

SQL > commit

Commit complete

SQL > select id,name,ora_rowscn from an order by id

ID NAME ORA_ROWSCN

1 test 155226434

2 test 155226434

3 test 155226434

1108 s11 155223006

SQL > insert into a (id,name) values

1 row inserted

SQL > commit

Commit complete

SQL > select id,name,ora_rowscn from an order by id

ID NAME ORA_ROWSCN

1 test 155226448

2 test 155226448

3 test 155226448

4 test 155226448

1108 s11 155223006

SQL > insert into a (id,name) values

1 row inserted

SQL > commit

Commit complete

SQL > select id,name,ora_rowscn from an order by id

ID NAME ORA_ROWSCN

1 test 155226463

2 test 155226463

3 test 155226463

4 test 155226463

5 test2 155226463

1108 s11 155223006

6 rows selected

SQL >

At this point, I believe you have a deeper understanding of "Oracle how to locate the recent DML time of the data table". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.