批量move tablespace 指令碼範例

 

--------------------------------------
SALE_DAT  unifrom size = 50M
SALE_IDX  unifrom size = 20M
--------------------------------------

IC_TRANS_DTL    
SL_SO_DTL       
IC_TRANS_DTL_SEQ  
IC_STORE_PROD     
SL_MAKE_PRICE     
IC_SCAN_LOG     
SL_SO          
PO_PR_DTL       
PO_PO_DTL       
PO_PR       

 

select ' alter table  channel.'||object_name||'  move tablespace SALE_DAT ; ' from dba_objects
where object_type='TABLE' and wner='CHANNEL' and object_name  in (
'IC_TRANS_DTL',    
'SL_SO_DTL' ,      
'IC_TRANS_DTL_SEQ',  
'IC_STORE_PROD',     
'SL_MAKE_PRICE',     
'IC_SCAN_LOG',     
'SL_SO',          
'PO_PR_DTL',      
'PO_PO_DTL',       
'PO_PR' 
)  ;

select ' alter index  channel.'||index_name||'  rebuild  tablespace SALE_IDX ;' from dba_indexes where  table_owner='CHANNEL' and wner='CHANNEL' and  table_name in (
'IC_TRANS_DTL',    
'SL_SO_DTL' ,      
'IC_TRANS_DTL_SEQ',  
'IC_STORE_PROD',     
'SL_MAKE_PRICE',     
'IC_SCAN_LOG',     
'SL_SO',          
'PO_PR_DTL',      
'PO_PO_DTL',       
'PO_PR' 
)  ;

 

 

 

--------------------------------------
LOG_DAT  unifrom size = 20M
LOG_IDX  unifrom size = 10M
--------------------------------------

PO_PO  ,
SL_SI  ,
SL_SI_DTL    ,
BD_PROD   ,
SL_PROD_PRICE ,
CP_CPY_EXT ,
RM_RO ,
RM_RO_EXPECT_DTL

select ' alter table  channel.'||object_name||'  move tablespace LOG_DAT ; ' from dba_objects
where object_type='TABLE' and wner='CHANNEL' and  object_name in (
'PO_PO',
'SL_SI',
'SL_SI_DTL',
'BD_PROD',
'SL_PROD_PRICE',
'CP_CPY_EXT',
'RM_RO',
'RM_RO_EXPECT_DTL'
)  ;

select ' alter index  channel.'||index_name||'  rebuild  tablespace LOG_IDX ;' from dba_indexes where table_owner='CHANNEL'  and wner='CHANNEL' and   table_name in (
'PO_PO',
'SL_SI',
'SL_SI_DTL',
'BD_PROD',
'SL_PROD_PRICE',
'CP_CPY_EXT',
'RM_RO',
'RM_RO_EXPECT_DTL'
)  ;

 

--------------------------------------
BASE_DAT  unifrom size = 10M
BASE_IDX  unifrom size = 2M
--------------------------------------

select ' alter table  channel.'||object_name||'  move tablespace BASE_DAT ; ' from dba_objects
where object_type='TABLE' and wner='CHANNEL' and  object_name  not in (
'IC_TRANS_DTL',    
'SL_SO_DTL' ,      
'IC_TRANS_DTL_SEQ',  
'IC_STORE_PROD',     
'SL_MAKE_PRICE',     
'IC_SCAN_LOG',     
'SL_SO',          
'PO_PR_DTL',      
'PO_PO_DTL',       
'PO_PR' ,
'PO_PO',
'SL_SI',
'SL_SI_DTL',
'BD_PROD',
'SL_PROD_PRICE',
'CP_CPY_EXT',
'RM_RO',
'RM_RO_EXPECT_DTL' 
)  ;

 

SELECT      ' alter index  channel.'
         || index_name
         || '  rebuild  tablespace BASE_IDX ;'
  FROM   dba_indexes
 WHERE   table_owner = 'CHANNEL' AND wner = 'CHANNEL'
         AND table_name NOT IN
                  ('IC_TRANS_DTL',
                   'SL_SO_DTL',
                   'IC_TRANS_DTL_SEQ',
                   'IC_STORE_PROD',
                   'SL_MAKE_PRICE',
                   'IC_SCAN_LOG',
                   'SL_SO',
                   'PO_PR_DTL',
                   'PO_PO_DTL',
                   'PO_PR',
                   'PO_PO',
                   'SL_SI',
                   'SL_SI_DTL',
                   'BD_PROD',
                   'SL_PROD_PRICE',
                   'CP_CPY_EXT',
                   'RM_RO',
                   'RM_RO_EXPECT_DTL');

 

======

select ' alter table  wm_wms.'||object_name||'  move tablespace BASE_DAT ; ' from dba_objects
where object_type='TABLE' and  wner='WM_WMS' ; 

select ' alter index  wm_wms.'||index_name||'  rebuild  tablespace  BASE_IDX ; ' from dba_indexes
where   table_owner='WM_WMS' ; 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-682590/，如需轉載，請註明出處，否則將追究法律責任。