ORACLE alter table storage

佚名 6年前 (2019-05-08) 随笔 1204人围观抢沙发百度已收录

--表
select 'ALTER TABLE '||owner||'.'||table_name||' MOVE TABLESPACE '||tablespace_name||' STORAGE(INITIAL 64K NEXT 32K);' 
  from dba_tables 
 where owner='TEST' and initial_extent>65536;  

 
--索引
select 'ALTER INDEX '||owner||'.'||index_name||' REBUILD STORAGE(INITIAL 64K NEXT 32K);' 
  from dba_indexes 
 where owner='TEST'  and initial_extent>65536;

 
--分区表
select 'ALTER table '||table_owner||'.'||table_name||' MOVE PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'   
  from DBA_tab_PARTITIONS 
 where table_owner='TEST' and initial_extent>65536;


--分区索引
select 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||PARTITION_NAME||' STORAGE(INITIAL 64K NEXT 32K);'   
  from DBA_ind_PARTITIONS 
 where index_owner='TEST' and initial_extent>65536;