Monday, 5 June 2017

How to create oracle Custom Schema in R12.2

Dear Friends,

Sorry for delay in posting as I promised to post yesterday but due to some production issues, I didn’t get time and was not able to concentrate. There was a CPU Patches and our team faces issues, believe me there was almost 48 Hrs on call meeting with customer. Working DBAs can understand my situation…

For freshers all the best and welcome to our DBA’s world…

Please find the below steps for creating a custom apps schema:

Below steps are explained and applied only on EBS R12.2 and the higher version of the oracle EBS.

These are the below steps to create APPS read only custom schema for example or schema name is ORA-DATA:

Before going through this we must know the Patching Cycle in R12.2 which I have mentioned in my previous posts.

As we know there are below 5 Phases of ADOP (online patching):

So, let’s start and follow the below steps:
$ adop phase=prepare

2. Now connect to the patch edition and to connect it, we need to source the patch env, because by default we will be in RUN env.

$. $HOME/EBSapps.env patch

3. Now, we will connect as sysdba and create the database user to be used for apps read only schema.

For Example: ORA-DATA

$ sqlplus "/ as sysdba"
SQL > create user ORA-DATA identified by ora-data default tablespace APPS_tablespace_name;

SQL> grant connect, resource to ORA-DATA;
SQL> grant create synonym to ORA-DATA;
SQL> exit;

4. Then connect as APPS user and run the below sql commands as shown:
$ sqlplus apps/<apps_password>

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> select 'exec AD_ZD.grant_privs 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> exit;

5. Now, run the below command grant privs script as shown below:
$ sqlplus "/as sysdba"

SQL> @grant_privs.sql
SQL> exit;

Here, we need to check two things as mentioned below:

1. If there is online patching enablement is not yet enabled then please execute below command:


2. And if online patching enablement has already been enabled then connect as SYSTEM and follow below command:

SQL>show user;
SQL> alter session set current_schema=APPS;

6. Now, we need to create the synonyms for ORA-DATA user as shown below.
$ sqlplus ORA-DATA /password

SQL> @create_synonyms.sql
SQL> exit;

7. Now, please complete the patching cycle as finalize, cutover and cleanup which is described in my previous posts.

Some more useful links:

R12.2 Architecture and Concepts

12c database issue and solution

How to create Database Context file

Dataguard Concepts and Setup

Adpatch patching steps in r12 with example

Job Tips

Please let us know for any concerns or suggestions, we really appreciate your comments. Put your comments either in comments box or contact us @



  1. I simply couldn't leave your web site prior to suggesting that I actually enjoyed the standard
    info an individual supply to your guests? Is gonna be again steadily to check out new posts

  2. Wow, marvelous blog layout! How lpng have you been blogging for?
    you made blogging look easy. The overall
    look of your website is fantastic, let alobe
    the content!


Thank you for your comments and suggestions. It's good to talk you.