[20130524]11G的Deferred segment creation的问题.txt

11G的Deferred segment creation是一个非常好的特性,有这个特性也产生一些问题.

自己以前写的链接:
http://space.itpub.net/267265/viewspace-716102
http://space.itpub.net/267265/viewspace-695835
http://space.itpub.net/267265/viewspace-713311
http://space.itpub.net/267265/viewspace-714646
http://space.itpub.net/267265/viewspace-745133


如果要使用exp/imp导入会存在一些问题,空表无法导出.虽然现在很少人在使用exp/imp命令.

实际上只要给这些对象分配空间就可以了.


11.2.0.2提供的DBMS_SPACE_ADMIN包含两个过程MATERIALIZE_DEFERRED_SEGMENTS,DROP_EMPTY_SEGMENTS.通过
MATERIALIZE_DEFERRED_SEGMENTS可以给这些没有分配段的对象分配段,DROP_EMPTY_SEGMENTS可以给已经没有记
录的段回收空间.自己做一个简单测试.

1.测试环境:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t(id number ,image clob);
create unique index i_t_id on t(id);

select segment_name, segment_type, bytes, extents from dba_segments
where wner=user and segment_name in ('T','I_T_ID') or
segment_name IN (SELECT segment_name FROM dba_lobs WHERE wner=user and table_name = 'T')
ORDER BY 1,2;

no rows selected
--可以发现没有段分配.

2.执行dbms_space_admin.materialize_deferred_segments看看.

--以sys用户执行.

SQL> exec dbms_space_admin.materialize_deferred_segments(schema_name => 'SCOTT',table_name  => 'T');

PL/SQL procedure successfully completed.

select segment_name, segment_type, bytes, extents from dba_segments 

where wner=user and segment_name in ('T','I_T_ID') or 

segment_name IN (SELECT segment_name FROM dba_lobs WHERE wner=user and table_name = 'T')

ORDER BY 1,2;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS

------------------------------ ------------------ ---------- ----------

I_T_ID                         INDEX                   65536          1

SYS_LOB0000275118C00002$$      LOBSEGMENT              65536          1

T                              TABLE                   65536          1

--可以发现现在分配了空间.

2.执行dbms_space_admin.DROP_EMPTY_SEGMENTS.看看.

--先插入一条.

SQL> insert into t values (1,'x');

1 row created.

SQL> commit ;

Commit complete.

--以sys用户执行.

SQL> exec dbms_space_admin.DROP_EMPTY_SEGMENTS(schema_name => 'SCOTT',table_name  => 'T');

PL/SQL procedure successfully completed.

select segment_name, segment_type, bytes, extents from dba_segments 

where wner=user and segment_name in ('T','I_T_ID') or 

segment_name IN (SELECT segment_name FROM dba_lobs WHERE wner=user and table_name = 'T')

ORDER BY 1,2;

SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS

------------------------------ ------------------ ---------- ----------

I_T_ID                         INDEX                   65536          1

SYS_LOB0000275118C00002$$      LOBSEGMENT              65536          1

T                              TABLE                   65536          1

--因为有数据存在,自然无法回收.

SQL> delete from t;

1 row deleted.

SQL> commit ;

Commit complete.

--以sys用户执行.

SQL> exec dbms_space_admin.DROP_EMPTY_SEGMENTS(schema_name => 'SCOTT',table_name  => 'T');

PL/SQL procedure successfully completed.

select segment_name, segment_type, bytes, extents from dba_segments 

where wner=user and segment_name in ('T','I_T_ID') or 

segment_name IN (SELECT segment_name FROM dba_lobs WHERE wner=user and table_name = 'T')

ORDER BY 1,2;

no rows selected

--这样一些没有记录的段占用的空间也可以回收回来.