How to create Apps Read Only schema creation in R12.2

In this post I am sharing the steps for creating a read only apps user in EBS R12.2

1) Check the invalid objects before creating apps read only schema

Select count(*) from dba_objects where status <> 'VALID';

2) Create a user

sqlplus / as sysdba

create user appsro identified by appsro@123;
grant connect, resource to appsro;
grant create synonym to appsro;
commit;

3) Create script for the synonym creation and grant for  User

sqlplus appsro/appsro@123
set head off
set newpage none
set pagesize 9999
spool create_synonyms.sql

select ‘create synonym ‘ || OBJECT_NAME || ‘ for ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘;’ from all_objects where OWNER in (‘APPS’) and
OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);
spool off;

spool grant_select.sql
select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);
spool off
exit

4) Run Grant statement

sqlplus / as sysdba
 @grant_select.sql
 exit;

5) This step to be used only for R12.2

sqlplus SYSTEM/manager
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

6)sqlplus appsro/appsrod#xyz
@create_synonyms.sql
exit;



Please test in TEST environment before doing in Production environment


If you like please follow and comment