#! /bin/ksh
# 设置环境变量
############### ### UAT ### ###############
export ENVS=/test/change/env/env_test.sql
export SCHEMA_HOME=/test/change/schema/test/2015_11_20_test_1.1
export SCHEMA_HOME_test=${SCHEMA_HOME}/2015_11_20_test
################ ### PROD ### ################
#export ENVS= #export SCHEMA_HOME= #
export SCHEMA_HOME_test=
##################################### # Check DB connection is correct #####################################
#检查数据库的连接性
sqlplus /nolog <<EOF
set pagesize 500 set linesize 200
#获得环境变量
@${ENVS}
###连接数据库
connect &v_system_un/&v_system_pw@&v_conn_str
show user prompt &v_conn_str
select * from v\$instance;
EOF
echo Press any key to continue
read ANS
############################################ # Check invalid objects (before) ############################################
##检查实效对象
cd $SCHEMA_HOME sqlplus /nolog << EOF
@${ENVS}
connect &v_system_un/&v_system_pw@&v_conn_str
set pages 1000
set lines 150
col owner for a15
col object_name for a35
col object_type for a20
alter session set nls_date_format = 'YYYY-MON-DD HH24:MI:SS';
spool invalid_obj_before_rollout.lst
select owner,object_name,object_type,last_ddl_time from dba_objects where status='INVALID';
spool off
EOF
############################################ banner 'test' ############################################
## 设置中断符号,按任意键继续进行
date
echo Press any key to continue
read ANS
cd $SCHEMA_HOME_test/ sqlplus /nolog << EOF
set pagesize 500
set linesize 200
获得环境变量
@${ENVS}
connect &v_system_un/&v_system_pw@&v_conn_str
show user
select * from v\$instance;
select to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') from dual;
@01_change_rollout.sql
EOF
## 设置中断符号,按任意键继续进行
date
echo Press any key to continue
read ANS
############################################ # Check invalid objects (after) ############################################
cd $SCHEMA_HOME sqlplus /nolog << EOF
@${ENVS}
connect &v_system_un/&v_system_pw@&v_conn_str
set pages 1000
set lines 150
col owner for a15
col object_name for a35
col object_type for a20
col last_ddl_time for a20
alter session set nls_date_format = 'YYYY-MON-DD HH24:MI:SS';
spool invalid_obj_after_rollout.lst
select owner,object_name,object_type,last_ddl_time from dba_objects where status='INVALID';
spool off
EOF