多用户下迁移对象的表空间

1.建立表空间

create tablespace ts_dvr_def datafile '/oradata/health/hts/ts_dvr_def_01.dbf' size 512m;
create tablespace ts_dvr1_def datafile '/oradata/health/hts/ts_dvr1_def_01.dbf' size 512m;
create tablespace ts_mdr_def datafile '/oradata/health/hts/ts_mdr_def_01.dbf' size 512m;
create tablespace ts_tnl_def datafile '/oradata/health/hts/ts_tnl_def_01.dbf' size 512m;
create tablespace ts_irr_def datafile '/oradata/health/hts/ts_irr_def_01.dbf' size 512m;
create tablespace ts_ehr_def datafile '/oradata/health/hts/ts_ehr_def_01.dbf' size 512m;
create tablespace ts_psr_def datafile '/oradata/health/hts/ts_psr_def_01.dbf' size 512m;
create tablespace ts_etl_def datafile '/oradata/health/hts/ts_etl_def_01.dbf' size 512m;

create tablespace ts_dvr_def_idx datafile '/oradata/health/hts/ts_dvr_def_idx_01.dbf' size 256m;
create tablespace ts_dvr1_def_idx datafile '/oradata/health/hts/ts_dvr1_def_idx_01.dbf' size 256m;
create tablespace ts_mdr_def_idx datafile '/oradata/health/hts/ts_mdr_def_idx_01.dbf' size 256m;
create tablespace ts_tnl_def_idx datafile '/oradata/health/hts/ts_tnl_def_idx_01.dbf' size 256m;
create tablespace ts_irr_def_idx datafile '/oradata/health/hts/ts_irr_def_idx_01.dbf' size 256m;
create tablespace ts_ehr_def_idx datafile '/oradata/health/hts/ts_ehr_def_idx_01.dbf' size 256m;
create tablespace ts_psr_def_idx datafile '/oradata/health/hts/ts_psr_def_idx_01.dbf' size 256m;
create tablespace ts_etl_def_idx datafile '/oradata/health/hts/ts_etl_def_idx_01.dbf' size 256m;
 

2.修改用户的默认表空间及使用配额

alter user dvr default tablespace ts_dvr_def;
alter user dvr1 default tablespace ts_dvr1_def;
alter user mdr default tablespace  ts_mdr_def;
alter user tnl default tablespace  ts_tnl_def ;
alter user irr default tablespace  ts_irr_def;
alter user ehr default tablespace  ts_ehr_def;
alter user psr default tablespace  ts_psr_def;
alter user etl default tablespace  ts_etl_def;

alter user dvr quota unlimited on ts_dvr_def;
alter user dvr1 quota unlimited on ts_dvr1_def;
alter user mdr quota unlimited on  ts_mdr_def;
alter user tnl quota unlimited on  ts_tnl_def ;
alter user irr quota unlimited on  ts_irr_def;
alter user ehr quota unlimited on  ts_ehr_def;
alter user psr quota unlimited on  ts_psr_def;
alter user etl quota unlimited on  ts_etl_def;

alter user dvr quota unlimited on ts_dvr_def_idx;
alter user dvr1 quota unlimited on ts_dvr1_def_idx;
alter user mdr quota unlimited on  ts_mdr_def_idx;
alter user tnl quota unlimited on  ts_tnl_def_idx ;
alter user irr quota unlimited on  ts_irr_def_idx;
alter user ehr quota unlimited on  ts_ehr_def_idx;
alter user psr quota unlimited on  ts_psr_def_idx;
alter user etl quota unlimited on  ts_etl_def_idx;

--迁移表对象
conn dvr/1234
spool dvr_ts.sql
select 'alter table '||tname||' move tablespace ts_dvr_def;' from tab;
spool off;
@dvr_ts.sql
conn dvr1/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_dvr1_def;' from tab;
spool off;
@mv_ts.sql

conn mdr/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_mdr_def;' from tab;
spool off;
@mv_ts.sql

conn tnl/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_tnl_def;' from tab;
spool off;
@mv_ts.sql

conn irr/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_irr_def;' from tab;
spool off;
@mv_ts.sql

conn ehr/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_ehr_def;' from tab;
spool off;
@mv_ts.sql

conn psr/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_psr_def;' from tab;
spool off;
@mv_ts.sql

conn etl/1234
spool mv_ts.sql
select 'alter table '||tname||' move tablespace ts_etl_def;' from tab;
spool off;
@mv_ts.sql

--迁移索引
conn dvr/1234
spool dvr_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_dvr_def_idx;' from user_indexes;
spool off;
@dvr_ts_idx.sql
conn dvr1/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_dvr1_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn mdr/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_mdr_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn tnl/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_tnl_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn irr/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_irr_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn ehr/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_ehr_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn psr/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_psr_def_idx;' from user_indexes;
spool off;
@mv_ts_idx.sql

conn etl/1234
spool mv_ts_idx.sql
select 'alter index '||index_name||' rebuild tablespace ts_etl_def_idx;' from user_indexes;
spool off;

@mv_ts_idx.sql