Create Read Only Schema in Oracle Applications

In this article we describe how to create read only schema for oracle ebs application and create read only steps are given below.

Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

SQL> Create user <USER NAME> by <PASSWORD> default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to <USER NAME>;

SQL> grant create synonym to <USER NAME>;

Step 2: Connect as APPS user and run the SQL commands

# 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

 Step 3:

SQL> spool grant_select.sql

SQL> select

'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to <USER 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> exit;

Step 4: # sqlplus /as sysdba

SQL> @grant_select.sql

SQL> exit

Step 5: # sqlplus USERNAME/PASSWORD

SQL> @create_synonyms.sql

SQL> exit