更改Oracle数据库表的表空间-方法2

 

1 创建表空间
CREATE TABLESPACE databk
    LOGGING
    DATAFILE 'F:/oracle/product/10.1.0/oradata/FFK/databk.ORA' SIZE 300M

2 修改用户的默认表空间
ALTER USER fly DEFAULT TABLESPACE databk;
验证：
select username, default_tablespace defspace from dba_users where username='FLY';

3 批量生成更改表空间的sql语句，导出文件至 alter_tablespaces_sqls.sql
select ' alter table '|| table_name ||' move tablespace databk;' from all_tables where owner = 'FLY';

 alter table AA move tablespace databk;                                        
 alter table AAB move tablespace databk;                                       
 alter table MDMT_COM_DIC_COU move tablespace databk;                          
 alter table MDMT_COM_DIC_EDITOR move tablespace databk;                       
 alter table MDMT_COM_DIC_MAJ move tablespace databk;                          
 alter table MDMT_COM_DIC_MCOUR move tablespace databk;                        
 alter table MDMT_COM_DIC_SUBJ move tablespace databk;                         
 alter table MDMT_DATA move tablespace databk;                                 
 alter table MDMT_DATA_BAK0706 move tablespace databk;                         
 alter table MDMT_DATA_END move tablespace databk;                             
 alter table MDM_DATA_MLOG move tablespace databk;                             
 alter table MDM_DATA_MLOG_SUB move tablespace databk;                         
 alter table MSG_SYS_INFO move tablespace databk;                              

 alter table PPPP move tablespace databk;                                       
 ......         

 

4 批量生成更改索引的表空间的sql语句，导出文件至 alter_index_tbs_sqls.sql
select 'ALTER INDEX ' || owner || '.' || index_name ||' REBUILD '||'ONLINE ' || 'TABLESPACE' || ' databk ' ||';'
from all_indexes where owner = 'FLY';

ALTER INDEX FLY.FK_AA REBUILD ONLINE TABLESPACE databk ;                       
ALTER INDEX FLY.PK_AAB REBUILD ONLINE TABLESPACE databk ;                      
ALTER INDEX FLY.PK_MDMT_COM_DIC_COU REBUILD ONLINE TABLESPACE databk ;         
ALTER INDEX FLY.PK_MDMT_COM_DIC_EDITOR REBUILD ONLINE TABLESPACE databk ;      
ALTER INDEX FLY.PK_MDMT_COM_DIC_MAJ REBUILD ONLINE TABLESPACE databk ;         
ALTER INDEX FLY.PK_MDMT_COM_DIC_MCOUR REBUILD ONLINE TABLESPACE databk ;       
ALTER INDEX FLY.PK_MDMT_COM_DIC_SUBJ REBUILD ONLINE TABLESPACE databk ;        
ALTER INDEX FLY.PK_MDMT_DATA REBUILD ONLINE TABLESPACE databk ;                
ALTER INDEX FLY.PK_MDM_DATA_MLOG REBUILD ONLINE TABLESPACE databk ;            
ALTER INDEX FLY.PK_MDM_DATA_MLOG_SUB REBUILD ONLINE TABLESPACE databk ;        
ALTER INDEX FLY.PK_MSG_SYS_INFO REBUILD ONLINE TABLESPACE databk ;             
ALTER INDEX FLY.PK_PPPPP REBUILD ONLINE TABLESPACE databk ;                    
ALTER INDEX FLY.KEY_RR REBUILD ONLINE TABLESPACE databk ;                      

ALTER INDEX FLY.PK_SYS_CONFIG REBUILD ONLINE TABLESPACE databk ;               
ALTER INDEX FLY.PK_TEMPAA REBUILD ONLINE TABLESPACE databk ;                   
ALTER INDEX FLY.PK_TEST_LHJ REBUILD ONLINE TABLESPACE databk ;                 
ALTER INDEX FLY.PK_NN REBUILD ONLINE TABLESPACE databk ;                       
ALTER INDEX FLY.PK_1 REBUILD ONLINE TABLESPACE databk ;                         
.........       

 

5 执行alter_tablespaces_sqls.sql

6 执行alter_index_tbs_sqls.sql

 

 

from:http://blog.csdn.net/flowerina/archive/2008/08/05/2772438.aspx