ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle生产环境script:all.sh

oracle生产环境script:all.sh

原创 Linux操作系统 作者:tompson1983 时间:2009-07-31 10:02:02 0 删除 编辑

#!/bin/bash
######create user
source /home/oracle/.bash_profile
sqlplus / as sysdba<create user webgame identified by webgame default tablespace data temporary tablespace temp01;
grant connect,resource,unlimited tablespace,query rewrite to webgame;
EOF

####imp data
imp system/sysfsxy007 file=exp_full_fsxy007.dmp log=imp.log full=y ignore=y buffer=819200

###### truncate tables
sqlplus webgame/webgame<set heading off;
set feed off;
set echo off;
set verify off;
spool /home/oracle/spool.sql;
select 'truncate table ' || tname || ' reuse storage; '  from tab where tname like 'TB_ERR%' or tname like 'TB_GUIL%' or tname like 'TB_ONLINE%' or tname like 'TB_ROLE%' or tname like 'TB_STAT%' or tname like 'TB_USER%' or tname like 'TB_GM%' or tname like '%TB_COUPON';
spool off;
EOF

more /home/oracle/spool.sql |sed '1d'|sed '$d' >/home/oracle/truncate.sql && rm -rf spool.sql

sqlplus webgame/webgame<@/home/oracle/truncate.sql;
alter table tb_role disable primary key cascade;
truncate table tb_role reuse storage;
alter table tb_role enable primary key;

alter table tb_user disable primary key cascade;
truncate table tb_user reuse storage;
alter table tb_user enable primary key;
alter package pk_fsxy compile;
alter package pk_other compile;

!
rm -rf /home/oracle/truncate.sql


#######move tables


source /home/oracle/.bash_profile
sqlplus webgame/webgame<

set echo off;
set feed off;
set head off;
set verify off;

spool /home/oracle/mv_tb.sql;
select 'alter table ' || table_name || ' move tablespace DATA ;' from user_tables where tablespace_name <> 'DATA';

spool off;
EOF

more /home/oracle/mv_tb.sql |sed '1d'|sed '$d' |sed '$d' >>/home/oracle/mv_tab.sql && rm -rf /home/oracle/mv_tb.sql


sqlplus webgame/webgame<@/home/oracle/mv_tab.sql;
!
rm -rf /home/oracle/mv_tab.sql

 

######rebuild indexes

source /home/oracle/.bash_profile

sqlplus webgame/webgame<set echo off;
set head off;
set feed off;
set verify off;

spool /home/oracle/rb_indx.sql;
select 'alter index ' || index_name || ' rebuild tablespace INDX;' from user_indexes;
spool off;
EOF

more /home/oracle/rb_indx.sql |sed '1d' |sed '$d' >>/home/oracle/rb_index.sql && rm -rf /home/oracle/rb_indx.sql

sqlplus webgame/webgame<@/home/oracle/rb_index.sql;
!
rm -rf /home/oracle/rb_index.sql

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15072844/viewspace-610941/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 巡检历险记
请登录后发表评论 登录
全部评论

注册时间:2009-06-15

  • 博文量
    52
  • 访问量
    78223