分类:
2012-06-13 22:10:23
原文地址:多用户下迁移对象的表空间 作者:zrp999
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