oracle表和索引收缩

中关村村草

oracle表和索引收缩

//表
alter table TB_ORI_0073 enable row movement;
alter table TB_ORI_0073 shrink space;
//索引
alter index INDEX_73_01_02 shrink space;
alter index INDEX_0073_05 shrink space;
-------------------------------------------------
从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                                                                     
----------                                                                     
         8                                                                     
--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;
alter table tb_manual  shrink space
*
第 1 行出现错误:
ORA-10635: Invalid segment or tablespace type

我是软件狂

谢谢分享