DBMS_SPACE_ADMIN 11G增加
11G的DBMS_SPACE_ADMIN包比10g的多出来两个procedure
DROP_EMPTY_SEGMENTS --把空对象分配的空间删除
MATERIALIZE_DEFERRED_SEGMENTS --给空对象分配初始空间
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t (id number primary key); --建表
Table created.
SQL> insert into t values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select index_name from user_indexes where table_name='T';
INDEX_NAME
------------------------------
SYS_C0011492
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('T','SYS_C0011492');
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
T
.0625
SYS_C0011492
.0625
SQL> col segment_name format a30
SQL> /
SEGMENT_NAME BYTES/1024/1024 --插入值之后有空间分配
------------------------------ ---------------
T .0625
SYS_C0011492 .0625
SQL> exec DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ('SONG','T');
BEGIN DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ('SONG','T'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> exec SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ('SONG','T');
BEGIN SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ('SONG','T'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn /as sysdba
Connected.
SQL> grant execute on DBMS_SPACE_ADMIN to song;
Grant succeeded.
SQL> conn song/song
Connected.
SQL> delete from t;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('T','SYS_C0011492'); --delete记录之后，还是占有空间
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
SYS_C0011492 .0625
T .0625
SQL> exec SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS ('SONG','T');
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('T','SYS_C0011492'); --删除空段之后 空间没有了
no rows selected
SQL> exec SYS.DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS ('SONG','T');
PL/SQL procedure successfully completed.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in ('T','SYS_C0011492'); --分配空间之后，有空间了
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
SYS_C0011492 .0625
T .0625
by song
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25099483/viewspace-773320/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
truncate table
下一篇：
undo作用
请登录后发表评论
登录
全部评论
