Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1011374
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-06-13 22:10:23

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

阅读(581) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~