Oracle Shrink Table

来源：互联网发布：linux中的ln命令编辑：程序博客网时间：2024/05/06 09:09

今天在戴明明同学的一个回帖里给了个关于Shrink命令的帖子，转过来学习学习~

=============================================================================================

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。

如果经常在表上执行DML操作，会造成数据库块中数据分布稀疏，浪费大量空间。同时也会影响全表扫描的性能，因为全表扫描需要访问更多的数据块。从oracle10g开始，表可以通过shrink来重组数据使数据分布更紧密，同时降低HWM释放空闲数据块。

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space语句两个阶段都执行。

shrink space compact只执行第一个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

注意：alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

============================================================================================

utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

============================================================================================

语法：

alter table <table_name> shrink space [ <null> | compact | cascade ];

alter table <table_name> shrink space compcat;

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table <tablespace_name> shrink space;

收缩表，降低 high water mark;

alter table <tablespace_name> shrink space cascade;

收缩表，降低 high water mark，并且相关索引也要收缩一下下。

alter index idxname shrink space;

回缩索引

1：普通表

Sql脚本，改脚本会生成相应的语句

select'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10)from user_tables;

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;

2：分区表的处理

进行shrink space时发生ORA-10631错误.shrink space有一些限制.

在表上建有函数索引（包括全文索引）会失败。

Sql脚本，改脚本会生成相应的语句

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||table_name||' shrink space;'||chr(10) from user_tables where ;

select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes where uniqueness='NONUNIQUE' ;

select 'alter table '||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10) from user_segments where segment_type='TABLE SUBPARTITION' ';

示例

在oracle中可以使用alter table table_name shrink space收缩表,使用shrink有两个前提条件:

1、表必须启用row movement

2、表段所在表空间的段空间管理(segment space management)必须为auto

实验如下：

--建立一个segment space management auto表空间

SQL> create tablespace ts_auto datafile 'd:/ts_auto.dbf' size 100m extent management local segment space management auto;

--建议测试表

SQL> create table tb_auto tablespace ts_auto as select * from dba_objects;

--查看shrink前的块数量

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

BLOCKS

----------

768

--delete数据后，空间占用没有变化

SQL> delete from tb_auto;

已删除49823行。

SQL> commit;

提交完成。

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

BLOCKS

----------

768

--直接收缩，提示必须启动row movement选项

SQL> alter table tb_auto shrink space;

alter table tb_auto shrink space

第 1 行出现错误:

ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table tb_auto enable row movement;

表已更改。

--收缩成功，空间已经释放

SQL> alter table tb_auto shrink space;

表已更改。

SQL> select blocks from dba_segments where segment_name='TB_AUTO';

BLOCKS

----------

--shrink不能在segment space management manaual的表空间的段上执行

SQL> create tablespace ts_manual datafile 'd:/ts_mannel.dbf' size 100m extent

management local segment space management manual;

表空间已创建。

SQL> select tablespace_name,segment_space_management from dba_tablespaces;

TABLESPACE_NAME SEGMEN

------------------------------ ------

SYSTEM MANUAL

UNDOTBS1 MANUAL

SYSAUX AUTO

TEMP MANUAL

USERS AUTO

EXAMPLE AUTO

TS_AUTO AUTO

TS_MANUAL MANUAL

已选择8行。

SQL> create table tb_manual tablespace ts_manual as select * from dba_objects;

表已创建。

SQL> alter table tb_manual shrink space

2 ;

alter table tb_manual shrink space

第 1 行出现错误:

ORA-10635: Invalid segment or tablespace type