根据块号查看块内数据和修改时间的例子(普通表和压缩表)

联系:QQ(5163721)

标题:根据块号查看块内数据和修改时间的例子(普通表和压缩表)

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

今天微信群里有个朋友问起一个问题,顺手做了个测试。

问题是这样的:
1,怎么根据file# block#来判断这个block中有多少数据?
2,启用了高级压缩后,如何查看?
3,怎么判断这些数据插入的时间?
4,不适用dump block的形式,可以怎么观察?

最后一个问题不用测试了,不用dump的话,可以使用类似bbed等一堆工具,还可以使用event 10046跟踪来观察。

本次始终压缩相关的脚本可以参考blog:
Exadata上的HCC测试(EHCC)——1
Exadata上的HCC测试(EHCC)—2—:DBMS_COMPRESSION.GET_COMPRESSION_RATIO
Exadata上的HCC测试(EHCC)—3—分区表的压缩

关于HCC压缩的块结构参见:
Exadata上HCC表的数据块结构—1-非压缩数据块结构
Exadata上HCC表的数据块结构—2-BASIC Compress和OLTP Compress
Exadata上HCC表的数据块结构—3-HCC块(compress for query low)

前三个问题,一次测试如下:

因为朋友问到ASM的情况,这个需求实际上跟是否使用ASM无关,是ORACLE DB的原理,测试环境如下:

[oracle@lunar ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 5 08:05:48 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select name from v$datafile where rownum=1;

NAME
--------------------------------------------------------------------------------
+DATA/lunars/system01.dbf

SQL> !date
Tue Aug  5 08:08:56 CST 2014  ----我这里VM的时间有问题,还停留在2014年的盛夏的某个清晨 *^_^*

SQL> 
SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

随便找一个block,或者指定一个block都可以:

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,
  2  dbms_rowid.rowid_block_number(rowid) blk#
  3  from LUNAR
  4  where rownum=1;

     FILE#       BLK#
---------- ----------
         1      50345

SQL> 

查看这个block中有多少条数据:

SQL> select count(*) from LUNAR 
  2  where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345;

  COUNT(*)
----------
        88

SQL> 

这里看到,目前这个block中存储了88行记录。

查看这88行数据是什么时间插入的,以及他们的ROWID:

SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn from LUNAR
  2  where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345;

ROWID              ROW_SCN
------------------ ----------------------------------------------------------------------
AAAE1IAABAAAMSpAAA 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAB 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAC 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAD 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAE 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAF 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAG 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAH 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAI 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAJ 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAK 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAL 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAM 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAN 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAO 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAP 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAQ 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAR 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAS 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAT 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAU 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAV 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAW 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAX 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAY 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAZ 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAa 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAb 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAc 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAd 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAe 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAf 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAg 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAh 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAi 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAj 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAk 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAl 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAm 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAn 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAo 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAp 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAq 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAr 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAs 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAt 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAu 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAv 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAw 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAx 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAy 2014-08-05 07:15:54
AAAE1IAABAAAMSpAAz 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA0 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA1 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA2 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA3 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA4 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA5 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA6 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA7 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA8 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA9 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA+ 2014-08-05 07:15:54
AAAE1IAABAAAMSpAA/ 2014-08-05 07:15:54
AAAE1IAABAAAMSpABA 2014-08-05 07:15:54
AAAE1IAABAAAMSpABB 2014-08-05 07:15:54
AAAE1IAABAAAMSpABC 2014-08-05 07:15:54
AAAE1IAABAAAMSpABD 2014-08-05 07:15:54
AAAE1IAABAAAMSpABE 2014-08-05 07:15:54
AAAE1IAABAAAMSpABF 2014-08-05 07:15:54
AAAE1IAABAAAMSpABG 2014-08-05 07:15:54
AAAE1IAABAAAMSpABH 2014-08-05 07:15:54
AAAE1IAABAAAMSpABI 2014-08-05 07:15:54
AAAE1IAABAAAMSpABJ 2014-08-05 07:15:54
AAAE1IAABAAAMSpABK 2014-08-05 07:15:54
AAAE1IAABAAAMSpABL 2014-08-05 07:15:54
AAAE1IAABAAAMSpABM 2014-08-05 07:15:54
AAAE1IAABAAAMSpABN 2014-08-05 07:15:54
AAAE1IAABAAAMSpABO 2014-08-05 07:15:54
AAAE1IAABAAAMSpABP 2014-08-05 07:15:54
AAAE1IAABAAAMSpABQ 2014-08-05 07:15:54
AAAE1IAABAAAMSpABR 2014-08-05 07:15:54
AAAE1IAABAAAMSpABS 2014-08-05 07:15:54
AAAE1IAABAAAMSpABT 2014-08-05 07:15:54
AAAE1IAABAAAMSpABU 2014-08-05 07:15:54
AAAE1IAABAAAMSpABV 2014-08-05 07:15:54
AAAE1IAABAAAMSpABW 2014-08-05 07:15:54
AAAE1IAABAAAMSpABX 2014-08-05 07:15:54

88 rows selected.

SQL> 

这里看到有88行,跟前面的结果是一致的。

看看这个表中有多少个block,以及他们的块号:

SQL> select EXTENT_ID,file_id,block_id 
  2  from DBA_EXTENTS where segment_name='LUNAR' AND OWNER='SYS';

 EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          1      50344
         1          1      50352
         2          1      50360
         3          1      50368
         4          1      50376
         5          1      50384
         6          1      50392
         7          1      50400
         8          1      50408
         9          1      50416
        10          1      50424
        11          1      44160
        12          1      44168
        13          1      44176
        14          1      44184
        15          1      44192
        16          1      44288

17 rows selected.

SQL> 

因为要测试压缩,那么先看看现在该表的压缩状态,这里我的LUNAR表是没有压缩的:

SQL> @comp

COMPRESSION_TYPE                                     NUM_ROWS
-------------------------------------------------- ----------
No Compression                                          17901

SQL> 

这里的查询方法,参考:

现在启用压缩(HCC只能在exadata上,否则会报错):

SQL> alter table lunar move compress for oltp;

Table altered.

SQL> 

再次查询,可以看到,已经是高级压缩了:

SQL> @comp

COMPRESSION_TYPE                                     NUM_ROWS
-------------------------------------------------- ----------
Advanced compression level                              17901

SQL> 

这里可以看到,rowid已经改变了,这是因为块的存储格式已经变化了:

SQL> select count(*) from LUNAR 
  2  where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=50345;

  COUNT(*)
----------
         0
SQL> 

看下现在这个LUNAR表使用了哪些block:

SQL> select EXTENT_ID,file_id,block_id 
  2  from DBA_EXTENTS where segment_name='LUNAR' AND OWNER='SYS';

 EXTENT_ID    FILE_ID   BLOCK_ID
---------- ---------- ----------
         0          1      44200
         1          1      44208
         2          1      44216
         3          1      44224
         4          1      44232
         5          1      44240
         6          1      44248
         7          1      44256
         8          1      44264
         9          1      44272
        10          1      44280
        11          1      45768

12 rows selected.

SQL> 

这里看到比刚才已经减少了17-12=5个block。

SEGMENT_NAME          EXTENTS ALLOCATEDBLOCKS USEDBLOCKS AVGROWSPERUSEDBLOCK
------------------ ---------- --------------- ---------- -------------------
LUNAR                      12              96         90               198.9

SQL> 

查询每个block中的记录数:

SQL> SELECT
  2     DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file#,
  3     DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) block#,
  4     COUNT(*) rowcount
  5  FROM LUNAR
  6  GROUP BY
  7     DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
  8     DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  9  order by   1,2,3;

     FILE#     BLOCK#   ROWCOUNT
---------- ---------- ----------
         1      44201        252
         1      44202        207
         1      44203        215
         1      44204        184
         1      44205        196
         1      44206        218
         1      44207        201
         1      44208        236
         1      44209        221
         1      44210        221
         1      44211        242
         1      44212        215
         1      44213        225
         1      44214        218
         1      44215        246
         1      44216        257
         1      44217        257
         1      44218        260
         1      44219        238
         1      44220        311
         1      44221        266
         1      44222        213
         1      44223        213
         1      44224        218
         1      44225        230
         1      44226        189
         1      44227        189
         1      44228        187
         1      44229        174
         1      44230        201
         1      44231        214
         1      44232        212
         1      44233        193
         1      44234        234
         1      44235        193
         1      44236        202
         1      44237        234
         1      44238        242
         1      44239        202
         1      44240        202
         1      44241        207
         1      44242        220
         1      44243        223
         1      44244        213
         1      44245        200
         1      44246        178
         1      44247        200
         1      44248        194
         1      44249        194
         1      44250        203
         1      44251        227
         1      44252        212
         1      44253        199
         1      44254        220
         1      44255        184
         1      44256        175
         1      44257        162
         1      44258        199
         1      44259        178
         1      44260        188
         1      44261        144
         1      44262        179
         1      44263        171
         1      44264        198
         1      44265        175
         1      44266        175
         1      44267        174
         1      44268        165
         1      44269        170
         1      44270        161
         1      44271        185
         1      44272        165
         1      44273        161
         1      44274        161
         1      44275        161
         1      44276        169
         1      44277        165
         1      44278        165
         1      44279        183
         1      44280        178
         1      44281        175
         1      44282        161
         1      44283        175
         1      44284        177
         1      44285        163
         1      44286        170
         1      44287        187
         1      45768        167
         1      45769        156
         1      45770        161

90 rows selected.

查看某个块中数据的修改时间:

SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn from LUNAR
  2  where dbms_rowid.rowid_relative_fno(rowid)=1 and dbms_rowid.rowid_block_number(rowid)=44277;

ROWID              ROW_SCN
------------------ ----------------------------------------------------------------------
AAAE1JAABAAAKz1AAA 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAB 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAC 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAD 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAE 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAF 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAG 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAH 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAI 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAJ 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAK 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAL 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAM 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAN 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAO 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAP 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAQ 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAR 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAS 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAT 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAU 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAV 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAW 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAX 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAY 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAZ 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAa 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAb 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAc 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAd 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAe 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAf 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAg 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAh 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAi 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAj 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAk 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAl 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAm 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAn 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAo 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAp 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAq 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAr 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAs 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAt 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAu 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAv 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAw 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAx 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAy 2014-08-05 07:25:58
AAAE1JAABAAAKz1AAz 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA0 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA1 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA2 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA3 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA4 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA5 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA6 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA7 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA8 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA9 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA+ 2014-08-05 07:25:58
AAAE1JAABAAAKz1AA/ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABA 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABB 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABC 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABD 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABE 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABF 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABG 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABH 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABI 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABJ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABK 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABL 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABM 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABN 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABO 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABP 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABQ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABR 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABS 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABT 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABU 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABV 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABW 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABX 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABY 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABZ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABa 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABb 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABc 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABd 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABe 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABf 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABg 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABh 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABi 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABj 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABk 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABl 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABm 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABn 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABo 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABp 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABq 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABr 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABs 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABt 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABu 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABv 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABw 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABx 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABy 2014-08-05 07:25:58
AAAE1JAABAAAKz1ABz 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB0 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB1 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB2 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB3 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB4 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB5 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB6 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB7 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB8 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB9 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB+ 2014-08-05 07:25:58
AAAE1JAABAAAKz1AB/ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACA 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACB 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACC 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACD 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACE 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACF 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACG 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACH 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACI 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACJ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACK 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACL 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACM 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACN 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACO 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACP 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACQ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACR 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACS 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACT 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACU 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACV 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACW 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACX 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACY 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACZ 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACa 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACb 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACc 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACd 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACe 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACf 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACg 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACh 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACi 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACj 2014-08-05 07:25:58
AAAE1JAABAAAKz1ACk 2014-08-05 07:25:58

165 rows selected.

SQL> !date
Tue Aug  5 07:37:48 CST 2014  注意,我这个VM的系统时间是“Tue Aug  5 07:37:48 CST 2014”

SQL> 
此条目发表在 Internal, 内部机制 分类目录,贴了 , 标签。将固定链接加入收藏夹。

根据块号查看块内数据和修改时间的例子(普通表和压缩表)》有 1 条评论

  1. ljty_0203 说:

    查看行数据是什么时间插入的,这个表述不准确,实际上只要数据位同一个数据块内,查出来的时间都是一样的,也就是只有scn号对应一个块中所有数据行
    SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn,i,name from hr.k;

    ROWID ROW_SCN I NAME
    —————— ————————————————————————— ———- —————————————-
    AAAWXmAADAAAACDAAA 15-MAR-17 10.42.34.000000000 PM 20 dddfdfsdfsadf
    AAAWXmAADAAAACDAAB 15-MAR-17 10.42.34.000000000 PM 31 cccddd
    AAAWXmAADAAAACHAAA 15-MAR-17 10.44.01.000000000 PM 33 hhhhh

    SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file#,
    2 dbms_rowid.rowid_block_number(rowid) blk#
    3 from hr.k;

    FILE# BLK#
    ———- ———-
    3 135
    3 131
    SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
    2 dbms_rowid.rowid_block_number(rowid) blk#,i
    3 from hr.k ;

    FILE# BLK# I
    ———- ———- ———-
    3 131 20
    3 131 31
    3 135 33

    SQL> !date
    Wed Mar 15 22:46:27 CST 2017

    SQL> insert into hr.k values (44,’kkkk’);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select rowid,scn_to_timestamp(ora_rowscn) row_scn,i,name from hr.k;

    ROWID ROW_SCN I NAME
    —————— ————————————————————————— ———- —————————————-
    AAAWXmAADAAAACDAAA 15-MAR-17 10.42.34.000000000 PM 20 dddfdfsdfsadf
    AAAWXmAADAAAACDAAB 15-MAR-17 10.42.34.000000000 PM 31 cccddd
    AAAWXmAADAAAACHAAA 15-MAR-17 10.46.37.000000000 PM 33 hhhhh
    AAAWXmAADAAAACHAAB 15-MAR-17 10.46.37.000000000 PM 44 kkkk

    SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
    2 dbms_rowid.rowid_block_number(rowid) blk#,i
    3 from hr.k ;

    FILE# BLK# I
    ———- ———- ———-
    3 131 20
    3 131 31
    3 135 33
    3 135 44

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注