ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_SPACE_ADMIN 11G增加

DBMS_SPACE_ADMIN 11G增加

原创 Linux操作系统 作者:psufnxk2000 时间:2013-09-24 23:30:45 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: truncate table
下一篇: undo作用
请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    616755