oracle@linux:/home/oracle/dba/bin% more create_public_synonym.ksh
#!/bin/ksh
#--------------------------------------------------------------------------------
#
# Procedure: create_public_synonym.ksh
#
# Description: creates public synonym for a give schema
#
# Parameters: database name, username, schema
#
#--------------------------------------------------------------------------------
# Vers| Date | Who | DA | Description
#-----+--------+-----+----+-----------------------------------------------------
# 1.0 |18/02/98| MT | | Initial Version
#--------------------------------------------------------------------------------
#
DATABASE_NAME=$1
USERNAME=$2
SCHEMA=$3
if [[ -z ${DATABASE_NAME} ]]
then
echo "Abort: $0 failed. No database name specified on command line"
exit 1
fi
if [[ -z ${USERNAME} ]]
then
echo "Abort: $0 failed. No username specified on command line"
exit 1
fi
if [[ -z ${SCHEMA} ]]
then
echo "Abort: $0 failed. No schema specified on command line"
exit 1
fi
ENVFILE=$HOME/${DATABASE_NAME}/dba/bin/environment.ksh
if [[ -f $ENVFILE ]]
then
. $ENVFILE
else
echo "Abort: $0 failed. No environment file ( $ENVFILE ) found"
export USERNAME
FILE_NAME=`basename $0 .ksh`
#
PASSWORD=$(get_password.ksh $DATABASE_NAME $USERNAME)
if [ $? != 0 ]
then
echo "Abort: $0 failed. Query for Oracle password for user $USERNAME failed"
exit 1
#
fi
IN_FILE="${LOGDIR}/${DATABASE_NAME}_${USERNAME}_${FILE_NAME}.sql"
[ -f ${IN_FILE} ] && rm -f ${IN_FILE}
sqlplus -S ${USERNAME}/${PASSWORD}@${DATABASE_NAME} << ! > ${IN_FILE}
set feedback off;
set heading off;
SELECT 'set serveroutput on' FROM dual;
SELECT 'set echo off' FROM dual -- do not display line commands;
SELECT 'PROMPT Creating public synonym for ${SCHEMA}.'||table_name||CHR(13)||CHR(10)||
'CREATE PUBLIC SYNONYM '||table_name||' FOR ${SCHEMA}.'||table_name||';' FROM ALL_TABLES
WHERE owner = UPPER('${SCHEMA}')
ORDER BY table_name;
SELECT 'exit' FROM dual;
exit
!
#
# get rid of 'rows selected' crap from ${IN_FILE}
#
cat ${IN_FILE} | egrep -v 'selected|TABLE_NAME|--'|sed -e '/^$/d'|sed -e "s/#/'/g" > temp.sql
mv temp.sql ${IN_FILE}
#
LOG_FILE="${LOGDIR}/${DATABASE_NAME}_${USERNAME}_${FILE_NAME}.log"
[ -f ${OUT_FILE} ] && rm -f ${OUT_FILE}
sqlplus ${USERNAME}/${PASSWORD}@${DATABASE_NAME} @${IN_FILE} > ${LOG_FILE}