Make Pre-Refresh script.sql as below
ORAENV_ASK=NO
export ORACLE_SID=$1
. oraenv -s
ORAENV_ASK=YES
sqlplus -s "/as sysdba" <<EOF
set echo off
set head off
set verify off
set feedback off
set pages 0
set lines 200
spool user_create.sql
select 'create user '||USERNAME||' identified by Welcome#123 default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace '||TEMPORARY_TABLESPACE||' profile REFRESH
_TEMP ;' from dba_users ;
select 'alter user '||USERNAME||' profile REFRESH_TEMP ;' from dba_users ;
spool off
spool pass_profile.sql
select 'alter user '||name||' identified by values '''||password||''' ;' from sys.user$ where password is not null;
select 'alter user '||USERNAME||' profile '||profile||' ;' from dba_users ;
spool off
spool privs.sql
select 'create role '||role||' ;' from dba_roles ;
select 'grant '||PRIVILEGE||' to '||GRANTEE||''||ADMIN||' ;' from (select grantee,PRIVILEGE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN from db
a_sys_privs ) ;
select 'grant '||GRANTED_ROLE||' to '||GRANTEE||''||ADMIN||' ;' from (select grantee,GRANTED_ROLE,(case ADMIN_OPTION when 'YES' then ' WITH ADMIN OPTION' end ) ADMIN f
rom dba_role_privs ) ;
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to '||GRANTEE||''||ADMIN||' ;' from (select grantee,PRIVILEGE,owner,table_name,(case GRANTABLE when 'YES
' then ' WITH GRANT OPTION' end ) ADMIN from dba_tab_privs ) where GRANTEE not in ('SYS') ;
spool off
spool synonyms.sql
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner not in ('PUBLIC','SYS','SYSTEM'
,'DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner not in ('PUBLIC',
'SYS','SYSTEM','DBSNMP','APPQOSSYS') and table_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||' ;' from dba_synonyms where owner in ('PUBLIC') and table_owner not in
('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is null ;
select 'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||'@'||DB_LINK||' ;' from dba_synonyms where owner in ('PUBLIC') and tabl
e_owner not in ('SYSTEM','DBAMONITOR','APPQOSSYS','SYS') and DB_LINK is not null ;
spool off
exit
EOF
echo " COMPLETED "
