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