【Shell】Display the ddl for all users in Oracle DB with bash script

 

脚本说明：

1、普遍用于 使用expdp/impdp 数据泵进行的数据(全库或者特定schemas)迁移

2、适用于无PDB的Oracle环境

3、适用于RAC,SI,ADG 以及多实例的环境

 

使用方法：

创建脚本为 display_all_users_ddl.sh 然后将正文内容贴入 并保存，然后执行 bash display_all_users_ddl.sh , 

若环境为多实例，那么可以显示的数字或者实例名即可，最终在当前目录会生成四个.log结尾的文件

 

# ##################################################
# This script generate DDL for ALL USERS [Creation & Privileges]

#-- WX:DBAJOE399 --

# ##################################################
SCRIPT_NAME="all_users_ddl"
# ############
# Description:
# ############
echo
echo "====================================================="
echo "This script generates the CREATION STATEMENT for ALL USERS."
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" #Excluded INSTANCES [Will not get reported offline].

# ############################
# 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 Running !
exit
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
if [ -z "${REPLY##[0-9]*}" ]
then
export ORACLE_SID=$DB_ID
echo Selected Instance:
echo
echo "********"
echo $DB_ID
echo "********"
echo
break
else
export ORACLE_SID=${REPLY}
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}|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:
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID}|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
#echo "ORACLE_HOME from PWDX is ${ORACLE_HOME}"

# #########################
# Getting DB_NAME:
# #########################
DB_NAME_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
SELECT name from v\$database
exit;
EOF
)
# Getting DB_NAME in Uppercase & Lowercase:
DB_NAME=`echo ${DB_NAME_RAW}| perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`

# ################################################
# SQLPLUS: Get the creation statement for ALL USERS:
# ###############################################
# Variables
export LOGDATE=`date +%d-%b-%y`
SPOOLLOF=${USR_ORA_HOME}/List_Of_Users_${DB_NAME}_${LOGDATE}.log
SPOOL_FILE=${USR_ORA_HOME}/ALL_USERS_DDL_${DB_NAME}_${LOGDATE}.log
#if [ -f ${SPOOL_FILE} ]
#then
echo "*****************" > ${SPOOL_FILE}
echo "ALL DB USERS DLL:" >> ${SPOOL_FILE}
echo "*****************" >> ${SPOOL_FILE}
echo "" >> ${SPOOL_FILE}
#fi

# Perpare the List of user to loop on:
${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
PROMPT
spool ${SPOOLLOF}
set pages 0
set echo off heading off feedback off
select username from dba_users where username not in ('SYS','SYSTEM','DBSNMP','EXFSYS','MDSYS','ORDDATA') order by 1;
spool off
EOF

# Loop on each user with generating its DDL:
for USERNAME in `cat ${SPOOLLOF}`
do
export USERNAME

${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
spool ${SPOOL_FILE} APPEND
set termout off
set linesize 190
set pages 50000
set feedback off
set trim on
set echo off
col USERNAME for a30
col account_status for a23

PROMPT
PROMPT ======================================

PROMPT USER [$USERNAME]
PROMPT ======================================

select a.username,a.account_status,a.profile,q.tablespace_name,q.bytes/1024/104 USED_MB, q.max_bytes "MAX_QUOTA_Bytes" from dba_users a, dba_ts_quotas q where a.username=q.username and a.username='$USERNAME';
set pages 0
set echo off heading off feedback off
--- Generate Creation Statement:
SELECT 'CREATE USER ' || u.username ||' IDENTIFIED ' ||' BY VALUES "' || c.password || "' DEFAULT TABLESPACE ' || u.default_tablespace ||' TEMPORARY TABLESPACE ' || u.temporary_tablespace ||' PROFILE ' || u.profile || case when account_status= 'OPEN' then ';' else ' Account LOCK;' end "–Creation Statement"
FROM dba_users u,user$ c where u.username=c.name and u.username=upper('$USERNAME')
UNION
--- Generate Granted Roles:
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted Roles"
from dba_role_privs where grantee= upper('$USERNAME')
UNION
--- Generate System Privileges:
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| case when ADMIN_OPTION='YES' then ' WITH ADMIN OPTION;' else ';' end "Granted System Privileges"
from dba_sys_privs where grantee= upper('$USERNAME')
UNION
--- Generate Object Privileges:
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||case when GRANTABLE='YES' then ' WITH GRANT OPTION;' else ';' end "Granted Object Privileges"
from DBA_TAB_PRIVS where GRANTEE=upper('$USERNAME');
spool off
EOF
done

echo ""
echo "[ALL USERS DDL SAVED TO: ${SPOOL_FILE}]"
echo ""


# #############
# END OF SCRIPT
# #############