更改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