oracle生产环境如何,oracle生产环境script:all.sh

#!/bin/bash

######create user

source /home/oracle/.bash_profile

sqlplus / as sysdba

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

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

!

rm -rf /home/oracle/mv_tab.sql

######rebuild indexes

source /home/oracle/.bash_profile

sqlplus webgame/webgame

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

!

rm -rf /home/oracle/rb_index.sql