在一个表空间上建表的时候，报错了：
|
1 2 3 4 5 6 7 |
SQL> create tablespace tbs_test datafile '/oracle/tbs_test.dbf' size 2m; Tablespace created. SQL> create table test.t2 tablespace tbs_test as select * from dba_objects; create table test.t2 tablespace tbs_test as select * from dba_objects * ERROR at line 1: ORA-01652: unable to extend temp segment by 128 in tablespace TBS_TEST |
在这边比较奇怪，为什么是不能扩展temp segment，而不是报错：
|
1 |
ORA-01653: unable to extend table TEST.T2 by 128 in tablespace TBS_TEST |
老白说，这是因为CTAS的时候，创建的表的BLOCK首先被标志为TEMP,等表全部创建完了再改为PERM，这样当CTAS出现问题的时候，不需要回退，只需要以后回收临时段就可以了。
为了验证，做了个10046的trace，发现确实是这样：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
…… insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,scanhint, hwmincr, spare1) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,0,:16,DECODE(:17,0,NULL,:17)) END OF STMT PARSE #6:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1717905537842 BINDS #6: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0 bfp=800003fa00072090 bln=22 avl=03 flg=05 value=597 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0 bfp=800003fa00072060 bln=24 avl=02 flg=05 value=9 bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0 bfp=800003fa00072030 bln=24 avl=02 flg=05 value=3 …… update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2 and block#=:3 END OF STMT PARSE #6:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1717905551761 BINDS #6: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0 bfp=800003fa00071f40 bln=24 avl=02 flg=05 value=3 （此处update不同的blocks和extents） …… update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2 and block#=:3 END OF STMT PARSE #5:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1717905672848 BINDS #5: bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=4000000000000001 size=24 offset=0 bfp=800003fa00070e88 bln=24 avl=02 flg=05 value=5 |
在这边，我们看到seg$的type#一开始是3，到最后update成5。
那么，type#=3是否就是temp segment，type#=5是否就是permanent呢？进一步查找，考虑到dba_segments中有segment_type，且有显示这个类型的名称，就从这个表入手：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
SQL> select TEXT from dba_views where view_name='DBA_SEGMENTS'; TEXT -------------------------------------------------------------------------------- select owner, segment_name, partition_name, segment_type, tablespace_name, header_file, header_block, decode(bitand(segment_flags, 131072), 131072, blocks, (decode(bitand(segment_flags,1),1, dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks), blocks)))*blocksize, decode(bitand(segment_flags, 131072), 131072, blocks, (decode(bitand(segment_flags,1),1, dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, blocks), blocks))), decode(bitand(segment_flags, 131072), 131072, extents, (decode(bitand(segment_flags,1),1, dbms_space_admin.segment_number_extents(tablespace_id, relative_fno, header_block, segment_type_id, buffer_pool_id, segment_flags, segment_objd, extents) , extents))), initial_extent, next_extent, min_extents, max_extents, pct_increase, freelists, freelist_groups, relative_fno, decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL) from sys_dba_segs SQL> |
进一步查看sys_dba_segs：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
SQL> select TEXT from dba_views where view_name=upper('sys_dba_segs'); TEXT -------------------------------------------------------------------------------- select u.name, o.name, o.subname, so.object_type, s.type#, ts.ts#, ts.name, ts.blocksize, f.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)), s.file#, s.cachehint, NVL(s.spare1, 0), o.dataobj# from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s, sys.file$ f where s.file# = so.header_file and s.block# = so.header_block and s.ts# = so.ts_number and s.ts# = ts.ts# and o.obj# = so.object_id and o.owner# = u.user# and s.type# = so.segment_type_id and o.type# = so.object_type_id and s.ts# = f.ts# and s.file# = f.relfile# union all select u.name, un.name, NULL, decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'), s.type#, ts.ts#, ts.name, ts.blocksize, f.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts, s.maxexts, s.extpct, decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)), s.file#, s.cachehint, NVL(s.spare1, 0), un.us# from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f where s.file# = un.file# and s.block# = un.block# and s.ts# = un.ts# and s.ts# = ts.ts# and s.user# = u.user# and s.type# in (1, 10) and un.status$ != 1 and un.ts# = f.ts# and un.file# = f.relfile# union all select u.name, to_char(f.file#) || '.' || to_char(s.block#), NULL, decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY', 4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#, ts.ts#, ts.name, ts.blocksize, f.file#, s.block#, s.blocks * ts.blocksize, s.blocks, s.extents, s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)), s.file#, s.cachehint, NVL(s.spare1, 0), s.hwmincr from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f where s.ts# = ts.ts# and s.user# = u.user# and s.type# not in (1, 5, 6, 8, 10) and s.ts# = f.ts# and s.file# = f.relfile# SQL> |
这边已经基本看出了sys_dba_segs是从基表seg$来的，而且在sys_dba_segs这个表上基本已经有type#和decode之后的type#，即SEGMENT_TYPE_ID和SEGMENT_TYPE：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> select SEGMENT_TYPE ,SEGMENT_TYPE_ID from sys_dba_segs group by SEGMENT_TYPE ,SEGMENT_TYPE_ID order by 2; SEGMENT_TYPE SEGMENT_TYPE_ID ------------------ --------------- ROLLBACK 1 TEMPORARY 3 CACHE 4 CLUSTER 5 NESTED TABLE 5 TABLE 5 TABLE PARTITION 5 TABLE SUBPARTITION 5 INDEX 6 INDEX PARTITION 6 INDEX SUBPARTITION 6 LOBINDEX 6 LOBSEGMENT 8 TYPE2 UNDO 10 14 rows selected. |
我们这边确实看到，ctas一开始时候的type#是3是TEMPORARY的segment，之后type#被update成5，是属于CLUSTER、NESTED TABLE 、TABLE、TABLE PARTITION 、TABLE SUBPARTITION的一种。
其实如果我们希望进一步研究各个type名称和type编号，找出所有的segment_type和segment_type_id我们注意到：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> desc sys_dba_segs Name Null? Type ----------------------------------------------------- -------- ------------------------------------ OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) SEGMENT_TYPE_ID NUMBER TABLESPACE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BLOCKSIZE NUMBER HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL_ID NUMBER SEGMENT_FLAGS NUMBER SEGMENT_OBJD NUMBER |
在这个表的第三和第四个字段，根据这表的dml，我们先发现decode(s.type#, 1, ‘ROLLBACK’, 10, ‘TYPE2 UNDO’),s.type#，和decode(s.type#,2,’DEFERRED ROLLBACK’,3,’TEMPORARY’,4,’CACHE’,9,’SPACE HEADER’,’UNDEFINED’),s.type#。剩下的5，6，7，8的类型要去找SYS_OBJECTS.object_type, seg$.type#。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
SQL> desc SYS_OBJECTS Name Null? Type ----------------------------------------------------- -------- ------------------------------------ OBJECT_TYPE VARCHAR2(18) OBJECT_TYPE_ID NUMBER SEGMENT_TYPE_ID NUMBER OBJECT_ID NUMBER HEADER_FILE NUMBER HEADER_BLOCK NUMBER TS_NUMBER NUMBER SQL> SQL> select text from dba_views where view_name='SYS_OBJECTS'; TEXT -------------------------------------------------------------------------------- select decode(bitand(t.property, 8192), 8192, 'NESTED TABLE', 'TABLE'), 2, 5, t.obj#, t.file#, t.block#, t.ts# from sys.tab$ t where bitand(t.property, 1024) = 0 /* exclude clustered tables */ union all select 'TABLE PARTITION', 19, 5, tp.obj#, tp.file#, tp.block#, tp.ts# from sys.tabpart$ tp union all select 'CLUSTER', 3, 5, c.obj#, c.file#, c.block#, c.ts# from sys.clu$ c union all select decode(i.type#, 8, 'LOBINDEX', 'INDEX'), 1, 6, i.obj#, i.file#, i.block#, i.ts# from sys.ind$ i where i.type# in (1, 2, 3, 4, 6, 7, 8, 9) union all select 'INDEX PARTITION', 20, 6, ip.obj#, ip.file#, ip.block#, ip.ts# from sys.indpart$ ip union all select 'LOBSEGMENT', 21, 8, l.lobj#, l.file#, l.block#, l.ts# from sys.lob$ l where (bitand(l.property, 64) = 0) or (bitand(l.property, 128) = 128) union all select 'TABLE SUBPARTITION', 34, 5, tsp.obj#, tsp.file#, tsp.block#, tsp.ts# from sys.tabsubpart$ tsp union all select 'INDEX SUBPARTITION', 35, 6, isp.obj#, isp.file#, isp.block#, isp.ts# from sys.indsubpart$ isp union all select decode(lf.fragtype$, 'P', 'LOB PARTITION', 'LOB SUBPARTITION'), decode(lf.fragtype$, 'P', 40, 41), 8, lf.fragobj#, lf.file#, lf.block#, lf.ts# from sys.lobfrag$ lf SQL> |
我们根据这个表分别取到各个segment的类型：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 |
SQL> l 1 select 'CLUSTER',s.type# 2 from sys.clu$ c,seg$ s 3 where s.file# = c.file# 4 and s.block# = c.block# 5 and s.ts# = c.ts# 6* group by 'CLUSTER',s.type# SQL> / 'CLUSTE TYPE# ------- ---------- CLUSTER 5 SQL> l 1 select decode(bitand(t.property, 8192), 8192, 'NESTED TABLE', 'TABLE'),type# 2 from sys.tab$ t,seg$ s 3 where bitand(t.property, 1024) = 0 4 and s.file# = t.file# 5 and s.block# = t.block# 6 and s.ts# = t.ts# 7* group by decode(bitand(t.property, 8192), 8192, 'NESTED TABLE', 'TABLE'),type# SQL> / DECODE(BITAN TYPE# ------------ ---------- TABLE 5 NESTED TABLE 5 SQL> SQL> l 1 select 'TABLE PARTITION',type# 2 from sys.tabpart$ tp,seg$ s 3 where s.file# = tp.file# 4 and s.block# = tp.block# 5 and s.ts# = tp.ts# 6* group by 'TABLE PARTITION',type# SQL> / 'TABLEPARTITION TYPE# --------------- ---------- TABLE PARTITION 5 SQL> l 1 select 'TABLE SUBPARTITION', s.type# 2 from sys.tabsubpart$ tsp, seg$ s 3 where s.file# = tsp.file# 4 and s.block# = tsp.block# 5 and s.ts# = tsp.ts# 6* group by 'TABLE SUBPARTITION', s.type# SQL> / 'TABLESUBPARTITION TYPE# ------------------ ---------- TABLE SUBPARTITION 5 SQL> l 1 select decode(i.type#, 8, 'LOBINDEX', 'INDEX'), s.type# 2 from sys.ind$ i, seg$ s 3 where i.type# in (1, 2, 3, 4, 6, 7, 8, 9) 4 and s.file# = i.file# 5 and s.block# = i.block# 6 and s.ts# = i.ts# 7* group by decode(i.type#, 8, 'LOBINDEX', 'INDEX'), s.type# SQL> / DECODE(I TYPE# -------- ---------- INDEX 6 LOBINDEX 6 SQL> SQL> l 1 select 'INDEX PARTITION', s.type# 2 from sys.indpart$ ip, seg$ s 3 where s.file# = ip.file# 4 and s.block# = ip.block# 5 and s.ts# = ip.ts# 6* group by 'INDEX PARTITION', s.type# SQL> / 'INDEXPARTITION TYPE# --------------- ---------- INDEX PARTITION 6 SQL> l 1 select 'INDEX SUBPARTITION', s.type# 2 from sys.indsubpart$ isp, seg$ s 3 where s.file# = isp.file# 4 and s.block# = isp.block# 5 and s.ts# = isp.ts# 6* group by 'INDEX SUBPARTITION', s.type# SQL> / 'INDEXSUBPARTITION TYPE# ------------------ ---------- INDEX SUBPARTITION 6 SQL> SQL> l 1 select 'LOBSEGMENT', s.type# 2 from sys.lob$ l, seg$ s 3 where ((bitand(l.property, 64) = 0) or (bitand(l.property, 128) = 128)) 4 and s.file# = l.file# 5 and s.block# = l.block# 6 and s.ts# = l.ts# 7* group by 'LOBSEGMENT', s.type# SQL> / 'LOBSEGMEN TYPE# ---------- ---------- LOBSEGMENT 8 SQL> SQL> l 1 select decode(lf.fragtype$, 'P', 'LOB PARTITION', 'LOB SUBPARTITION'), 2 s.type# 3 from sys.lobfrag$ lf, seg$ s 4 where s.file# = lf.file# 5 and s.block# = lf.block# 6 and s.ts# = lf.ts# 7 group by decode(lf.fragtype$, 'P', 'LOB PARTITION', 'LOB SUBPARTITION'), 8* s.type# SQL> / no rows selected |
由于我的数据库中不存在的’LOB PARTITION’, ‘LOB SUBPARTITION’，初步判定type#为7的是’LOB PARTITION’, ‘LOB SUBPARTITION’。
综上所述，所有的seg$的type#编号和类型对应如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SEGMENT_TYPE SEGMENT_TYPE_ID ------------------ ---------------- ROLLBACK 1 DEFERRED ROLLBACK 2 TEMPORARY 3 CACHE 4 CLUSTER 5 TABLE 5 NESTED TABLE 5 TABLE PARTITION 5 TABLE SUBPARTITION 5 INDEX 6 LOBINDEX 6 INDEX PARTITION 6 INDEX SUBPARTITION 6 LOB PARTITION 7 LOB SUBPARTITION 7 LOBSEGMENT 8 SPACE HEADER 9 TYPE2 UNDO 10 UNDEFINED |