清理某个表空间,所有segment全部清理了,查dba_tables视图,该表空间中还有表存在,奇了怪了,google下。
ORACLE
11g
新增了一个参数:deferred_segment_creation,默认是true。具体是如果deferred_segment_creation为true,当新建空表时不会立即分配extent,即不占数据空间,只有insert数据后才分配空间,这样做可以节省少量的空间。
开始测试
1. 先在11g中测试
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> select a.name, a.value, a.DESCRIPTION FROM v$parameter a WHERE a.name like '%deferred_segment_creation%';
NAME VALUE DESCRIPTION
---------------------------------------- --------------- ----------------------------------------------
deferred_segment_creation TRUE defer segment creation to first insert
新建空表,不插入任何行:
SQL> create table t0821 as select * from dba_objects where 1 = 2;
Table created.
--查询,该表没有分配任何extent
SQL> select * from dba_extents a where a.segment_name = 'T0821';
no rows selected
需要分配空间的话, alter TABLE tb_name allocate EXTENT (SIZE size_num);
(2) 修改
deferred_segment_creation 为false
SQL> alter session set deferred_segment_creation=false;
Session altered.
SQL> create table t0822 as select * from dba_objects where 1=2;
Table created.
之后查询 dba_extents,表T0822已经分配extent
2. 10g中测试,10.2.0.1.0,
10g中没有 参数
deferred_segment_creation
select * FROM v$parameter a WHERE lower(a.name) like '%deferred_segment_creation%'; --0
SQL> CREATE TABLE t0821 STORAGE(INITIAL 0k) AS SELECT * from dba_objects WHERE 1 = 2;
Table created.
SQL> select count(*) from dba_extents where segment_name = 'T0821';
COUNT(*)
----------
2
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23700752/viewspace-741514/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23700752/viewspace-741514/