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