Creating APPS READ ONLY schema in Oracle Applications

Step1:

bash $ sqlplus "/ as sysdba"

SQL > create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to appsro;

SQL> grant create synonym to appsro;

SQL> exit;

Step2:bash $ sqlplus apps/******

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

SQL> select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> spool grant_select.sql

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');

SQL> spool off

SQL> exit;

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;
bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;
Verify the newly created user with apps user:  SQL> show user
USER is "APPS"
SQL>

SQL> select count(*) from apps.fnd_user;

  COUNT(*)
----------
       164

SQL> select count(*) from apps.fnd_concurrent_requests;

  COUNT(*)
----------
     12737




SQL> conn "CONFSOFT"/apps_readonly
Connected.
SQL>
SQL>
SQL> show user
USER is "CONFSOFT"

SQL> select count(*) from apps.fnd_user;

  COUNT(*)
----------
       164

SQL> select count(*) from apps.fnd_concurrent_requests;

  COUNT(*)
----------
     12737

SQL>