[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
--这样一些没有记录的段占用的空间也可以回收回来.