查看表数据的DML时间

最新推荐文章于 2022-08-18 17:28:20 发布

cnktbkanb141909486

最新推荐文章于 2022-08-18 17:28:20 发布

阅读量303

点赞数

文章标签：数据库

今早来上班看到 “@夜无伤” 大神在群里发的消息，说是可以查看表数据的最后DML时间

隐约记得我之前看过相关的文章，查了下笔记，还是被我给找到了，今天重新拿出来实验一下

SQL> select * from v$version where rownum=1;
BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

其实就是通过伪列ora_rowscn来查看的，很简单，实验内容如下：
SQL> show user
USER is "GOOLEN"
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> set time on

10:16:43 SQL> drop table goolen purge;
drop table goolen purge
*
ERROR at line 1:
ORA-00942: table or view does not exist

10:17:14 SQL> create table goolen (id number);

Table created.

10:17:44 SQL> insert into goolen values(1);

1 row created.

10:17:59 SQL> insert into goolen values(2);

1 row created.

10:18:00 SQL> insert into goolen values(3);

1 row created.

10:18:02 SQL> commit;

Commit complete.

++++先来查一下默认情况下，数据行DML的时间点：
10:18:05 SQL> select scn_to_timestamp(ora_rowscn),id from goolen;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ID
--------------------------------------------------------------------------- ----------
07-MAY-14 10.18.01.000000000 AM 1
07-MAY-14 10.18.01.000000000 AM 2
07-MAY-14 10.18.01.000000000 AM 3

10:19:27 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:18:01 1
2014-05-07 10:18:01 2
2014-05-07 10:18:01 3

+++新插入一条数据，再查看时间，发现表中所有数据的DML时间点都变了
（其实是该BLOCK里的所有rows的时间点变了，不是表中所有的rows，因为这里所有的row都在一个block里，下面会接着验证block的情况）
10:20:09 SQL> insert into goolen values(4);

1 row created.

10:20:24 SQL> commit;

Commit complete.

10:20:26 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:20:25 1
2014-05-07 10:20:25 2
2014-05-07 10:20:25 3
2014-05-07 10:20:25 4

+++新建一个表，验证DML改变的是block里的行，还是表中所有的行
10:36:20 SQL> drop table goolen purge;

Table dropped.

10:36:35 SQL> create table goolen (id number,name varchar2(20));

Table created.

10:36:45 SQL> alter table goolen minimize records_per_block;
alter table goolen minimize records_per_block
*
ERROR at line 1:
ORA-28603: statement not permitted on empty tables

10:36:49 SQL> insert into goolen values(1,'GOOLEN');

1 row created.

10:37:15 SQL> commit;

Commit complete.

10:37:18 SQL> alter table goolen minimize records_per_block;

Table altered.

10:37:27 SQL> insert into goolen values(2,'SCOTT');

1 row created.

10:37:36 SQL> insert into goolen values(3,'TIGER');

1 row created.

10:37:43 SQL> insert into goolen values(4,'KING');

1 row created.

10:37:50 SQL> commit;

Commit complete.

+++查看表数据，这四条数据，分别存放到block 16931 和block 16935里面
10:37:53 SQL> select
10:37:59 2 dbms_rowid.rowid_relative_fno(rowid) fno,
10:37:59 3 dbms_rowid.rowid_block_number(rowid) blockid,
10:37:59 4 id
10:37:59 5 from goolen;

FNO BLOCKID ID
---------- ---------- ----------
6 16931 3
6 16931 4
6 16935 1
6 16935 2

+++查看DML时间，现在所有的rows，时间点相同
10:37:59 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:37:50 3
2014-05-07 10:37:50 4
2014-05-07 10:37:50 1
2014-05-07 10:37:50 2

10:38:08 SQL> select * from goolen;

ID NAME
---------- ------------------------------------------------------------
3 TIGER
4 KING
1 GOOLEN
2 SCOTT

10:38:53 SQL> update goolen set name='GAO' where id = 3;

1 row updated.

10:39:31 SQL> commit;

Commit complete.

+++更新block 16931里 id = 3的数据后，查看时间，发现改变的只是block 16931里的rows，block 16935里的rows没有变
说明默认情况下，DML操作会改变该block里面所有rows的ora_rowscn值，而不会影响到其他block的ora_rowscn值
10:39:34 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:39:32 3
2014-05-07 10:39:32 4
2014-05-07 10:37:50 1
2014-05-07 10:37:50 2

10:39:36 SQL> delete goolen where id = 2;

1 row deleted.

10:39:53 SQL> commit;

Commit complete.

+++验证delete 操作，结果一样
10:39:55 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:39:32 3
2014-05-07 10:39:32 4
2014-05-07 10:39:53 1

+++默认情况下，对表数据进行DML操作，会改变涉及到的block里面的所有rows的ora_rowscn值
我们可以在建表的时候，加上rowdependencies参数，这样DML操作只会影响到改行的ora_rowscn值

10:40:52 SQL> drop table goolen purge;

Table dropped.

10:41:00 SQL> create table goolen(id number,name varchar2(20)) rowdependencies;

Table created.

10:43:26 SQL> insert into goolen values(1,'GOOLEN');
insert into goolen values(2,'SCOTT');
insert into goolen values(3,'TIGER');

1 row created.

10:43:31 SQL>
1 row created.

10:43:31 SQL>
1 row created.

10:43:31 SQL> insert into goolen values(4,'KING');

1 row created.

10:43:32 SQL> commit;

Commit complete.

10:43:33 SQL> select
10:43:33 2 dbms_rowid.rowid_relative_fno(rowid) fno,
10:43:33 3 dbms_rowid.rowid_block_number(rowid) blockid,
10:43:33 4 id
10:43:33 5 from goolen;

FNO BLOCKID ID
---------- ---------- ----------
6 16935 1
6 16935 2
6 16935 5
6 16935 4
6 16935 3

10:43:35 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:43:30 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 3
2014-05-07 10:43:30 4

10:43:44 SQL> select * from goolen;

ID NAME
---------- ------------------------------------------------------------
1 GOOLEN
2 SCOTT
3 TIGER
4 KING

10:44:01 SQL> update goolen set name='GAO' where id = 1;

1 row updated.

10:44:38 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;
select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen
*
ERROR at line 1:
ORA-01405: fetched column value is NULL

10:44:40 SQL> commit;

Commit complete.

+++查看update，发现改变的只是id = 1的值，其他行没变，还发现，update后没有commit，貌似不能查看ora_rowscn的值，至于为什么，后续在去研究一下
10:45:12 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:09 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 3
2014-05-07 10:43:30 4

10:45:13 SQL> update goolen set name='GOOLEN' where id = 1;

1 row updated.

10:45:27 SQL> alter system flush buffer_cache;

System altered.

10:45:36 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;
select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen
*
ERROR at line 1:
ORA-01405: fetched column value is NULL

10:45:38 SQL> commit;

Commit complete.

10:45:42 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 3
2014-05-07 10:43:30 4

+++验证delelte操作
10:45:44 SQL> delete goolen where id = 3;

1 row deleted.

10:46:09 SQL> commit;

Commit complete.

10:46:11 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 4

+++验证insert操作
10:46:14 SQL> insert into goolen values(3,'TIGER');

1 row created.

10:46:24 SQL> commit;

Commit complete.

10:46:26 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 4
2014-05-07 10:46:24 3

+++dlelte后不提交，之后rollback
10:46:32 SQL> delete goolen where id = 3;

1 row deleted.

10:46:43 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 4

10:46:44 SQL> roll
Rollback complete.
10:47:01 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:43:30 4
2014-05-07 10:46:24 3

+++insert后不提交，也查不了ora_rowscn值
10:47:02 SQL> insert into goolen values(5,'XIAOMING');

1 row created.

10:47:40 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;
ERROR:
ORA-01405: fetched column value is NULL

no rows selected

10:47:42 SQL> commit;

Commit complete.

10:47:47 SQL> select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss'),id from goolen;

TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DDHH24:MI:S ID
--------------------------------------------------------- ----------
2014-05-07 10:45:36 1
2014-05-07 10:43:30 2
2014-05-07 10:47:45 5
2014-05-07 10:43:30 4
2014-05-07 10:46:24 3

至于表的DDL时间，可以通过dba_objects视图的LAST_DDL_TIME来查看

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1155945/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1155945/