#!/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