#!/usr/bin/ksh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=/usr/bin:/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/lib:$PATH:.
export ORACLE_SID=DBTCB1

sqlplus / as sysdba << EOF
SET FEEDBACK OFF
COLUMN username FORMAT a30
COLUMN accnt_status FORMAT a30
COLUMN lock_date FORMAT a30
SPOOL /export/home/oracle/scripts/mp_idle_schemas.sql
SELECT c.username username, u.account_status accnt_status, u.lock_date lock_date FROM (SELECT username FROM dba_users WHERE username LIKE 'FC%'  MINUS SELECT schema_name username FROM test_users@dbadb WHERE database_name LIKE 'DBTCB1%') c, dba_users u WHERE c.username = u.username;
SPOOL OFF;
EXIT;
EOF

grep -v '^$' /export/home/oracle/scripts/mp_idle_schemas.sql > /export/home/oracle/scripts/mp_idle_schemas1.sql
sed '1d' /export/home/oracle/scripts/mp_idle_schemas1.sql > /export/home/oracle/scripts/mp_idle_schemas.sql
rm /export/home/oracle/scripts/mp_idle_schemas1.sql

sqlplus / as sysdba << EOF
SET HEAD OFF
SET FEEDBACK OFF
EXEC mp_idle_schemas;
EXIT;
EOF