FAQs about SCHEMA in Oracle

Oracle has several modules such as HCM, AR, AP, GL, PO etc. Each of these modules have their own schemas which oracle has created. All the tables corresponding to a module is created in the respective schema and then a synonym is created in APPS schema.

If Oracle wants to create a new table ABC_TL in HCM, they will do the following:

Step 1. Connect to hr/hr@XXTEST Create table ABC_TL.  

Step 2. Grant all on ABC_TL to APPS.   

Step 3. connect to apps/apps@XXTEST 

Step 4. Create or replace synonym ABC_TL for HR.ABC_TL 

APPS schema is not meant to have any tables in it. You should not create table in APPS Schema. It does no harm though, but just not a good practice. Hence you must be having a custom schema which is where your table must be created.

How to create a custom schema?
A schema is created in Oracle when a user is created.

We can create a new user with the CREATE USER statement as follows:

  CREATE USER muds
  IDENTIFIED BY muds1234
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called muds in the Oracle database whose password is muds1234, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would be tbs_temp_01.

The next step in setting up your schema is to assign "system privileges" to the new user muds.

These "system privileges" will allow our new user to create a session in Oracle as well as create tables, views, triggers, procedures, sequences, and synonyms in the new schema. Here is an example of how we might grant those system privileges:

GRANT create session TO muds;
GRANT create table TO muds;
GRANT create view TO muds;
GRANT create any trigger TO muds;
GRANT create any procedure TO muds;
GRANT create sequence TO muds;
GRANT create synonym TO muds;

These new privileges are now granted to the user called muds.

Now that the schema (called muds) has been created with the necessary privileges, you can create objects in the schema.