今天微信群里有个朋友问起一个问题，顺手做了个测试。
问题是这样的：
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>
查看行数据是什么时间插入的,这个表述不准确，实际上只要数据位同一个数据块内，查出来的时间都是一样的，也就是只有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