# #################################################################################################### # This script shows the creation statement of a DB user # # # # # Author: Mahmmoud ADEL # # # # ### # Created: 24-09-11 # # # # # # Modified: 31-12-13 Customized the script to run on various environments. # 15-09-14 Add Search Feature. # 20-09-20 Using dbms_metadata.get_ddl for 12c+ versions. # # #################################################################################################### # ############ # Description: # ############ echo echo "========================================================" echo "This script generates the CREATION STATEMENT for a USER." echo "========================================================" echo sleep 1 # ####################################### # Excluded INSTANCES: # ####################################### # Here you can mention the instances the script will IGNORE and will NOT run against: # Use pipe "|" as a separator between each instance name. # e.g. Excluding: -MGMTDB, ASM instances: EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline]. # ############################## # SCRIPT ENGINE STARTS FROM HERE ............................................ # ############################## # ########################### # Listing Available Databases: # ########################### # Count Instance Numbers: INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l ) # Exit if No DBs are running: if [ $INS_COUNT -eq 0 ] then echo "No Database is Running !" echo return fi # If there is ONLY one DB set it as default without prompt for selection: if [ $INS_COUNT -eq 1 ] then export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" ) # If there is more than one DB ASK the user to select: elif [ $INS_COUNT -gt 1 ] then echo echo "Select the ORACLE_SID:[Enter the number]" echo "---------------------" select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" ) do integ='^[0-9]+$' if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ] || [ ${REPLY} -eq 0 ] then echo echo "Error: Not a valid number!" echo echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]" echo "----------------------------------------------" else export ORACLE_SID=$DB_ID echo printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n" echo break fi done fi # Exit if the user selected a Non Listed Number: if [ -z "${ORACLE_SID}" ] then echo "You've Entered An INVALID ORACLE_SID" exit fi # ######################### # Getting ORACLE_HOME # ######################### ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1` USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1` # SETTING ORATAB: if [ -f /etc/oratab ] then ORATAB=/etc/oratab export ORATAB ## If OS is Solaris: elif [ -f /var/opt/oracle/oratab ] then ORATAB=/var/opt/oracle/oratab export ORATAB fi # ATTEMPT1: Get ORACLE_HOME using pwdx command: export PGREP=`which pgrep` export PWDX=`which pwdx` if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]] then PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'` export PMON_PID ORACLE_HOME=`pwdx ${PMON_PID} 2>/dev/null|awk '{print $NF}'|sed -e 's/\/dbs//g'` export ORACLE_HOME fi # ATTEMPT2: If ORACLE_HOME not found get it from oratab file: if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then ## If OS is Linux: if [ -f /etc/oratab ] then ORATAB=/etc/oratab ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'` export ORACLE_HOME ## If OS is Solaris: elif [ -f /var/opt/oracle/oratab ] then ORATAB=/var/opt/oracle/oratab ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'` export ORACLE_HOME fi #echo "ORACLE_HOME from oratab is ${ORACLE_HOME}" fi # ATTEMPT3: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate] if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'` export ORACLE_HOME #echo "ORACLE_HOME from environment is ${ORACLE_HOME}" fi # ATTEMPT4: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate] if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash_profile $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1` export ORACLE_HOME #echo "ORACLE_HOME from User Profile is ${ORACLE_HOME}" fi # ATTEMPT5: If ORACLE_HOME is still not found, search for orapipe: [Least accurate] if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'` export ORACLE_HOME #echo "ORACLE_HOME from orapipe search is ${ORACLE_HOME}" fi # ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate] if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then if [ -x /usr/bin/locate ] then ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'` export ORACLE_HOME fi fi # TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script: if [ ! -f ${ORACLE_HOME}/bin/sqlplus ] then echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly" echo "e.g." echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1" exit fi export LD_LIBRARY_PATH=${ORACLE_HOME}/lib # ################### # Checking DB Version: # ################### DB_VER_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <. # DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". # DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html