本帖最后由 heizistudio 于 2013-6-9 16:36 编辑
先看表空间和数据文件分布
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ---------------
/opt/oracle/oradata/duxiu/sysaux01.dbf 3 SYSAUX
/opt/oracle/oradata/duxiu/undotbs01.dbf 2 UNDOTBS1
/opt/oracle/oradata/duxiu/system02.dbf 1 SYSTEM
/opt/oracle/oradata/duxiu/users01.dbf 4 USERS
/opt/oracle/oradata/duxiu/test01.dbf 5 TEST01
/opt/oracle/oradata/duxiu/dul.dbf 6 DUL
/opt/oracle/oradata/duxiu/lmt.dbf 7 LMT
/opt/oracle/oradata/duxiu/lmt02 8 LMT
8 rows selected.
SQL>
SQL> select TS#,NAME from v$tablespace;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
9 TEST01
16 DUL
11 TEMP2
17 LMT
9 rows selected.
LMT表空间包含
/opt/oracle/oradata/duxiu/lmt.dbf 7 LMT
/opt/oracle/oradata/duxiu/lmt02 8 LMT
两个数据文件
建立一个表,存在LMT表空间SQL> create table hello tablespace lmt as select * from test;
查看表extents分布,发现7 和8号文件都有
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME='HELLO';
OWNER SEGMENT_ TABLES EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------- -------- ------ ---------- ---------- ---------- ----------
SYS HELLO LMT 0 7 273 8
SYS HELLO LMT 1 7 281 8
SYS HELLO LMT 2 7 289 8
SYS HELLO LMT 3 7 297 8
SYS HELLO LMT 4 7 305 8
SYS HELLO LMT 5 7 313 8
SYS HELLO LMT 6 7 321 8
SYS HELLO LMT 7 7 329 8
SYS HELLO LMT 8 7 337 8
SYS HELLO LMT 9 7 345 8
SYS HELLO LMT 10 7 353 8
SYS HELLO LMT 11 7 361 8
SYS HELLO LMT 12 7 369 8
SYS HELLO LMT 13 7 377 8
SYS HELLO LMT 14 7 385 8
SYS HELLO LMT 15 8 9 8
SYS HELLO LMT 16 7 393 128
SYS HELLO LMT 17 8 137 128
SYS HELLO LMT 18 7 521 128
SYS HELLO LMT 19 8 265 128SYS HELLO LMT 20 7 649 128
SYS HELLO LMT 21 8 393 128
SYS HELLO LMT 22 7 777 128
SYS HELLO LMT 23 8 521 128
SYS HELLO LMT 24 7 905 128
SYS HELLO LMT 25 8 649 128SYS HELLO LMT 26 7 1033 128
SYS HELLO LMT 27 8 777 128
28 rows selected.
发现该表的extents分布在该表空间的两个数据文件上
BBED> dump /v count 100 offset 0 block 395;
File: /opt/oracle/oradata/duxiu/lmt02 (0)
Block: 395 Offsets: 0 to 99 Dba:0x00000000
-------------------------------------------------------
06a20000 8b010002 7eff1001 00000106 l ........~.......
a2450000 01000000 ae100100 5bff1001 l .E..........[...
00000000 02003200 89010002 01000a00 l ......2.........
78220000 e1088000 b80b0c00 06220000 l x"..........."..
7eff1001 00000000 00000000 00000000 l ~...............
00000000 00000000 00000000 00000000 l ................
00000000 l ....
<16 bytes per line>
块头0x60是数据块
各文件数据分布
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,count(*) num from hello group by dbms_rowid.ROWID_RELATIVE_FNO(rowid);
FILE# NUM
---------- ----------
8 351888
7 448112
SQL>
但是问题出现了
SQL> select OBJECT_NAME, OBJECT_ID from dba_objects where OBJECT_NAME=upper('hello');
OBJECT_NAME OBJECT_ID
-------------------- ----------
HELLO 69806
在seg$基表里却查不到
SQL> select HWMINCR,FILE#,BLOCK#,BLOCKS from seg$ where HWMINCR=69806;
HWMINCR FILE# BLOCK# BLOCKS
---------- ---------- ---------- ----------
69806 7 275 1664
没有FILE#为8 的信息,为什么,求解释
是SEG$存放的不全??