Creating SCHEMA  in oracle 
schema is the user in oracle apps



We can create schema from SYSTEM user in oracle  ( default  pwd system/manager )

Example:

CREATE USER XXTEMP IDENTIFIED BY XXTEMP 
                 DEFAULT TABLESPACE apps_ts_tx_data
                 TEMPORARY TABLESPACE temp
                 ACCOUNT UNLOCK;

Unless you dont provide below grant you wont be able to login to schema

                 GRANT CREATE SESSION TO XXTEMP ;



Below commands provide different types of grants to schema , no 8 will give select grant for all the tables from all schema to XXTEMP schema.


   1)  GRANT CREATE ANY PROCEDURE TO XXTEMP ;
   2) GRANT CREATE ANY PROCEDURE TO XXTEMP ;
   3) GRANT CREATE ANY TYPE TO XXTEMP ;
   4) GRANT CREATE SESSION TO XXTEMP ;
   5) GRANT CREATE ANY TRIGGER TO XXTEMP ;
   6) GRANT CREATE ANY PROCEDURE TO XXTEMP ;
   7) GRANT EXECUTE ANY PROCEDURE TO XXTEMP ;
   8) GRANT SELECT ANY TABLE TO XXTEMP ;
   9) GRANT CREATE ANY TABLE TO XXTEMP ;    
   10) GRANT CREATE VIEW TO XXTEMP ;

However when you create veiw  from any base table in this schema XXTEMP and try to give privileges to any other schema you will get below error , because XXETEMP schema just have select privileges and it cant give grant of select to any other schema because the underlying table used in view is owned by some other shcema, when you do so you will get below error

ORA-01720: grant option does not exist for 

this can be solved by giving

GRANT SELECT ANY TABLE TO XXTEMP WITH GRANT OPTION ]



Different Schemas in oracle applications