表move和compress

1：先收集统计信息，然后识别出待重整的表：
SELECT owner,TABLE_NAME,num_rows,blocks,100-(NUM_ROWS*AVG_ROW_LEN)/(BLOCKS *8192)*100 HWM FROM DBA_TABLES
WHERE owner in (select username from dba_users where default_tablespace not in ('SYSTEM','USERS','SYSAUX'))
and num_rows>50000 and blocks<>0 and 100-(NUM_ROWS*AVG_ROW_LEN)/(BLOCKS *8192)* 100>60 order by 5 desc;

2：摸清楚待重整表的各种信息：
SELECT owner,TABLE_NAME,num_rows,blocks,100-(NUM_ROWS*AVG_ROW_LEN)/(BLOCKS *8192)*100 HWM,DEGREE FROM DBA_TABLES where table_name in ('tab') order by 5 desc;
select * from dba_lobs where table_name in ('tab');
select owner,segment_name,segment_type,bytes/1024/1024/1024 from dba_segments where segment_name in ('tab') order by 4 desc;
select OWNER,table_name,TRIGGER_NAME,STATUS from dba_triggers where table_name in ('tab');
select owner,table_name,INDEX_NAME,INDEX_TYPE,STATUS,DEGREE from dba_indexes where table_name in ('tab') order by 2;
select table_name,count(1) from dba_indexes where table_name in ('tab') group by table_name order by 2;
select ini_trans,pct_free from dba_indexes where index_name in ('tab') and owner='ANDY';

3：停库重整注意事项：
设置job_queue_processes=0重整后恢复、在库open之前关闭监听、注意表所属空间大小及temp表空间大小、记录失效对象。
create table system.objects_status_date as select * from dba_objects where status ='INVALID' or status='UNUSABLE';

4：享受重整：
alter trigger andy.trigger disable;
alter table andy.tab move parallel 16;
alter table andy.tab noparallel;
alter index andy.index rebuild parallel 16 nologging;--如果要drop掉重新create，要注意索引之前的ini_trans,pct_free
alter index andy.index noparallel;
alter trigger andy.trigger enable;

5：重整后处理失效对象：
select object_type,object_name from dba_objects where status ='INVALID' or status='UNUSABLE'
minus
select object_type,object_name from system.objects_status_date where status ='INVALID' or status='UNUSABLE';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from
(select owner,object_type,object_name from dba_objects where status ='INVALID' or status='UNUSABLE'
minus
select owner,object_type,object_name from system.objects_status_date where status ='INVALID' or status='UNUSABLE') a
where a.object_type<>'PACKAGE BODY';

select 'alter package '||owner||'.'||object_name||' compile BODY;' from
(select owner,object_type,object_name from dba_objects where status ='INVALID' or status='UNUSABLE'
minus
select owner,object_type,object_name from system.objects_status_date where status ='INVALID' or status='UNUSABLE') a
where a.object_type='PACKAGE BODY';
6：对重整表收集统计信息：
exec dbms_stats.gather_table_stats(ownname => 'andy',tabname => 'TAB',estimate_percent => 100,degree => 16,cascade=>TRUE,no_invalidate => false);

备注1：可以通过以下语句看并行会话：
select pddl_status from v$session where sid=sys_context('userenv','sid');
select * from v$px_session
select * from v$px_process
备注2：关于分区重整和压缩
hash分区压缩：alter table andy.tab modify partition pt compress for oltp; alter table andy.tab move partition pt;
其他分区压缩：alter table andy.tab move partition pt compress for all operations nologging;
表压缩：alter table andy.tab move compress for all operations parallel 16;
只重整不压缩：alter table andy.tab move nocompress parallel 16;
分区索引重建：alter index andy.index rebuild partition pt parallel 16 nologging;
索引在线重建：alter index andy.index rebuild online parallel 16 nologging;
备注3：关于带LOB字段重整和压缩
alter TABLE andy.tab move tablespace andy_tbs Lob("LOB_SEG") store as securefile
(tablespace andy_tbs
enable storage in row
pctversion 0
nocache
nologging
compress high )
compress for all operations nologging parallel 16;
备注4：重整前后可以用show_space看效果。
Why is no space released after an ALTER TABLE ... SHRINK? (Doc ID 820043.1)