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

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