#!/usr/bin/ksh

ORACLE_BASE=/oracle export ORACLE_BASE
ORACLE_HOME=/oracle/product/8.1.7 export ORACLE_HOME
ORACLE_SID=DEVAPP1 export ORACLE_SID

/oracle/product/8.1.7/bin/sqlplus -s / << EOF
set head off
set pause off
set pages 0
set feedback off
set term off
set echo off
set verify off
spool /u01/app/analyze_schema.lst
select 'exec dbms_utility.analyze_schema('||''''||username||''''||','||'''COMPUTE'''||');'
FROM DBA_USERS
where username not in ('SYSTEM','SYS');
spool off
start /u01/app/analyze_schema.lst
exit
EOF

#!/bin/sh
# NAME            :  scr_test
# SYNTAX          :  scr_test <param 1> <param2> ...
# *******************************************************************

# comment 1
#
echo   "drop user myuser cascade;
  create user myuser identified by pswd default tablespace mytblspc;
  grant connect to myuser;
  grant dba to myuser;
  exit; "| sqlplus -s produser/pswd

# create tables in myuser user (if required)
#
sqlplus -s myuser/pswd @$CODEHOME/sql/create_my_tables

# create synonym in produser
echo   "drop synonym produser.user_tab_columns_me;
  create synonym produser.user_tab_columns_me for sys.user_tab_columns;
  exit; "| sqlplus -s produser/pswd

# create synonym in myuser
echo   "drop synonym myuser.user_tab_columns_me;
  create synonym myuser.user_tab_columns_me for sys.user_tab_columns;
  exit; "| sqlplus -s myuser/pswd

# get the latest batch number
#
echo  "set pagesize 0 feedback off echo off trims on termout off
  spool /tmp/bnr
  select 'export BATCHNR='
  ||to_char(nvl(max(transmission_batch_nr)+1,1)) from trnsmsn_dtl;
  exit;" | sqlplus -s produser/pswd

chmod 777 /tmp/bnr.lst 
. /tmp/bnr.lst
rm -f /tmp/bnr.lst

# export the data 
#
exp myuser/pswd owner=myuser \
  file=$CODEHOME/transdata/$BATCHNR.DAT \
  log=$CODEHOME/$BATCHNR.log

if [ $? -ne 0 ]
then
    echo "EXPORTING DATA - ERROR - CONTACT SUPPORT"
    exit 1
else
    # clear out the myuser user
    echo "EXPORTING DATA - Successful"
    echo "drop user myuser cascade;
      exit;"|sqlplus -s produser/pswd
    exit 0
fi