Jump to Navigation

145 - Schema for EBS Apps objects

Here we are going to create a schema similar to the APPS schema but has only read-only prviliges.

1. Create the user for the required schema.

SQL> connect system/manager

SQL> create user <your_user> identified by <your_user_password> default tablespace
<tablespace_to_assign_to_user> temporary tablespace temp;

2. Grant connect and resource privileges to your user.

SQL> connect system/manager

SQL> grant connect, resource to <your_user>;

3. Use the following select statement to generate a script that will grant privileges on APPS objects to your user.

4. Use the following select statement to generate a script that will create synonyms in <your_user> schema
    for all objects owned by APPS.

SET HEAD OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGESIZE 0
SET LINESIZE 100
SET TRIMSPOOL ON
spool create_synonym.sql

SELECT 'CREATE SYNONYM MYUSER.'
|| O.OBJECT_NAME
|| ' FOR APPS.'
|| O.OBJECT_NAME
|| ';' COMMAND
FROM DBA_OBJECTS O
WHERE O.Owner = 'APPS' and object_type='TABLE';

spool off

5. Run the script you created as SYS / DBA user.

# sqlplus / as sysdba

SQL> @create_synonym.sql

 

Oracle:


Main menu 2

Story | by Dr. Radut