[20161226]关于sys.seg$.txt
1.环境:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> select * from dba_objects where owner='SYS' and object_name='SEG$';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS SEG$ 14 8 TABLE 2013-08-24 11:37:35 2013-08-24 11:59:25 2013-08-24:11:37:35 VALID N N N 1
--小于OBJECT_ID<59的对象,有bootstrap$引导建立.
SYS@book> select * from bootstrap$ where obj#=14;
LINE# OBJ# SQL_TEXT
---------- ---------- ------------------------------------------------------------
14 14 CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NO
T NULL,"TYPE#" NUMBER NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS
" NUMBER NOT NULL,"EXTENTS" NUMBER NOT NULL,"INIEXTS" NUMBER
NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NUL
L,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#"
NUMBER NOT NULL,"LISTS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGE
S" NUMBER NOT NULL,"CACHEHINT" NUMBER NOT NULL,"SCANHINT" NU
MBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPA
RE2" NUMBER) STORAGE ( OBJNO 14 TABNO 2) CLUSTER C_FILE#_BL
OCK#(TS#,FILE#,BLOCK#)
2.查看:
SYS@book> select * from sys.seg$ where file#=4;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
----- ------ ----- --- ------ ------- ------- ------- ---------- ------- ------ ----- ----- ------ ------------ --------- -------- ------- ------- ----------
4 130 5 4 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 87106 4325633
4 138 6 4 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 87107 4325633
4 146 5 4 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 87108 4325633
4 154 6 4 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 87109 4325633
4 162 5 4 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 87111 4325633
4 170 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88489 4325633
4 178 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88490 4325633
4 186 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88491 4325633
4 194 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88492 4325633
4 202 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88493 4194561
4 210 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88494 4325633
4 218 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88495 4325633
4 226 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88496 4325633
4 234 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88497 4325633
4 242 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88498 4194561
4 250 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88488 4194561
4 258 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88499 4325633
4 266 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88500 4325633
4 274 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88501 4325633
4 282 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88502 4325633
4 290 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88503 4325633
4 298 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88504 4325633
4 306 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88505 4325633
4 314 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88506 4325633
4 322 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88507 4325633
4 330 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88508 4325633
4 338 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88509 4325633
4 346 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88510 4325633
4 354 8 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88511 4325633
4 362 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88512 4325633
4 370 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88513 4325633
4 378 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88514 4325633
4 386 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88515 4325633
4 450 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88539 4325633
4 458 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88540 4325633
4 466 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88541 4325633
4 474 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88542 4325633
4 482 5 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88538 4325633
4 490 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88543 4325633
4 498 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88544 4325633
4 506 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88545 4325633
4 514 6 4 8 1 8 1 2147483645 128 0 85 0 0 2147483645 0 0 88546 4325633
42 rows selected.
SYS@book> @ &r/which_obj 4 130
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT DEPT TABLE USERS 0 4 128 65536 8 4
SYS@book> @ &r/which_obj 4 138
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- -------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT PK_DEPT INDEX USERS 0 4 136 65536 8 4
--可以看出type#=5对应是表,type#6=对应索引.
--如果仔细检查可以里面记录的是段的块头.实际上如果全表扫描开始扫的就是段头.段头坏了,全表扫描可能失败.
SYS@book> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='SCOTT' and header_file=4;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------ ------------ --------------- ----------- ------------
DEPT TABLE USERS 4 130
EMP TABLE USERS 4 146
SALGRADE TABLE USERS 4 162
PK_DEPT INDEX USERS 4 138
PK_EMP INDEX USERS 4 154
SYS@book> @ &r/which_obj 4 178
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
OE SYS_LOB0000088489C00 LOBSEGMENT USERS 0 4 176 65536 8 4
004$$
--type#=8对应LOB类型.
SYS@book> @ &r/which_obj 4 186
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------ --------------- ---------- ---------- ---------- ---------- ---------- ------------
OE SYS_IL0000088489C000 LOBINDEX USERS 0 4 184 65536 8 4
04$$
--lob类型的索引也是type#=6.看看当前的数据库存在几种类型:
SYS@book> select distinct type# from sys.seg$ --where file#=4;
TYPE#
-----
1
6
5
8
10
--还有2种类型1,10,看看到底是什么段.
-- type#=1
SYS@book> select * from sys.seg$ where type#=1;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
1 128 1 0 8 1 14 1 32765 7 0 0 0 0 2147483645 0 0 0 4194307
SYS@book> @ &r/which_obj 1 128
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS SYSTEM ROLLBACK SYSTEM 0 1 128 65536 8 1
--type#=1 是系统回滚段.
SYS@book> select * from sys.seg$ where type#=10;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
3 144 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 2 4194307
3 160 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 3 4194307
3 176 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 4 4194307
3 192 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 5 4194307
3 208 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 6 4194307
3 224 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 7 4194307
3 240 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 8 4194307
3 256 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 9 4194307
3 272 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 10 4194307
3 128 10 2 8 1 16 2 32765 8 0 0 0 0 0 0 0 1 4194307
10 rows selected.
SYS@book> @ &r/which_obj 3 128
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SYS _SYSSMU1_3724004606$ TYPE2 UNDO UNDOTBS1 0 3 128 65536 8 3
--type#=10 , 是用户回滚段(普通回滚段)
3.测试mssm的表空间看看:
CREATE TABLESPACE tea DATAFILE
'/mnt/ramdisk/book/tea01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT manual
FLASHBACK ON;
SCOTT@book> create table deptx tablespace tea as select * from dept;
Table created.
SCOTT@book> create index pk_deptx on deptx(deptno) tablespace tea;
Index created.
SCOTT@book> select * from sys.seg$ where file#=6;
FILE# BLOCK# TYPE# TS# BLOCKS EXTENTS INIEXTS MINEXTS MAXEXTS EXTSIZE EXTPCT USER# LISTS GROUPS BITMAPRANGES CACHEHINT SCANHINT HWMINCR SPARE1 SPARE2
----- ------ ----- --- ------ ------- ------- ------- ---------- ------- ------ ----- ----- ------ ------------ ---------- ---------- ---------- ---------- ----------
6 128 5 7 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 88921 4325377
6 136 6 7 8 1 8 1 2147483645 128 0 83 0 0 2147483645 0 0 88922 4325377
SCOTT@book> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='SCOTT' and header_file=6;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
-------------------- ------------------ ------------------------------ ----------- ------------
DEPTX TABLE TEA 6 128
PK_DEPTX INDEX TEA 6 136
--依旧记录的是文件头.类型与前面一样.