DBMS_SPACE_ADMIN 11G增加

psufnxk2000發表於2013-09-24
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部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-773320/，如需轉載，請註明出處，否則將追究法律責任。

相關文章