一次对普通表、分区表shrink space的实验

select * from dba_tablespaces;
select * from dba_data_files;
select * from dba_tables t where t.table_name='BIG_TABLE1';
select * from dba_tables t where t.table_name='BIG_TABLE1_HASHED';
select s.bytes/1024/1024 from dba_segments s where s.tablespace_name='BIGTBS1' and s.segment_name = 'BIG_TABLE1';
select s.segment_name,s.bytes/1024/1024 "MB",s.segment_type from dba_segments s
where s.tablespace_name='BIGTBS1' and s.segment_type LIKE 'INDEX%' ;
select max(id) from big_table;
select  f.file_name,f.bytes/1024/1024 from dba_data_files f where f.tablespace_name='BIGTBS1';
delete  big_table  where id between 1000000 and 2000000;
select count(*) from big_table;
delete big_table where id between 122 and 980;
alter table big_table enable row movement;
alter table big_table shrink space;
alter tablespace bigtbs resize 530m;

select max(block_id)  from dba_extents e where e.tablespace_name='BIGTBS1' ;
select 136585*8/1024 from dual;

select e.segment_name,e.segment_type,e.partition_name,e.block_id,e.bytes/1024/1024 "MB"  from dba_extents e
where e.block_id='136585' and e.tablespace_name='BIGTBS1';
alter index big_hash_idx1 rebuild partition SYS_P40 ;

Alter index local_index_name rebuild partition partition_name;

select cname
from (
select  rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||';' cname
from dba_segments ds
where ds.tablespace_name ='BIGTBS1'
  and ds.segment_type = 'TABLE PARTITION'
) c;
where  rm between 1 and 100

select  'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild partition '||ds.partition_name||';'
from dba_segments ds
where ds.tablespace_name ='BIGTBS'
  and ds.segment_type = 'INDEX PARTITION';
 
  select 86153*8/1024 from dual;

 

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

 

select'alter index '||index_name||' shrink space;'||chr(10)from user_indexes;
 alter table "RWKURSUS" modify partition "RNIP1308" shrink space;

select 'alter table '||table_name||' enable row movement;'||chr(10)||'alter table '||t.table_name||' modify partition '||s.partition_name||' shrink space;' 
from dba_tables t,dba_segments s where  s.segment_type='TABLE PARTITION' and t.table_name=s.segment_name and s.tablespace_name='BIGTBS1';

select 'alter index '||t.index_name||' modify partition '||s.partition_name||' shrink space;'  from dba_indexes t,dba_segments s
where  s.segment_type='INDEX PARTITION' and t.index_name=s.segment_name and s.tablespace_name='BIGTBS1';

select i.index_name,i.table_name,i.uniqueness,i.status  from dba_indexes i where i.index_name='BIG_HASH_IDX1';
alter index big_idx1 rebuild;
ALTER INDEX BIG_HASH_IDX1 REBUILD;

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OwnName    => 'SYS',  
                                TabName    => 'BIG_TABLE1_HASHED',  
                                Method_Opt => 'for all indexed columns',  
                                CASCADE    => TRUE);  
END;

 

select s.owner,s.segment_name,s.partition_name,s.segment_type,s.bytes/1024/1024 "MB",s.tablespace_name from dba_segments s where s.tablespace_name='BIGTBS1';

 

select 'alter index '||index_name||' shrink space;'||chr(10) from user_indexes i
where i.uniqueness='NONUNIQUE' and i.tablespace_name='BIGTBS1';
select * from dba_indexes i where i.tablespace_name='BIGTBS1';