ctas为何报错不能扩展temp segment

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/ctas-make-temp-segment/

在一个表空间上建表的时候，报错了：

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，而不是报错：

ORA-01653: unable to extend table TEST.T2 by 128 in tablespace TBS_TEST

老白说，这是因为CTAS的时候，创建的表的BLOCK首先被标志为TEMP,等表全部创建完了再改为PERM，这样当CTAS出现问题的时候，不需要回退，只需要以后回收临时段就可以了。

为了验证，做了个10046的trace，发现确实是这样：

……
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，且有显示这个类型的名称，就从这个表入手：

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：

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：

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我们注意到：

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#。

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的类型：

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#编号和类型对应如下：

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

· 【文章发布信息】发表于： 2009-01-25 @ 00:59:53 · ||分类： Study note

留条评论