【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结尾的文件

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

# ##################################################
# This script generate DDL for ALL USERS [Creation & Privileges]#-- WX:DBAJOE399 --<br>
# ##################################################
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
# #############