Role of APPLSYS & APPS schema in Oracle Applications:
Applsys schema contains all the tables required for administarative purpose and is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc.
In the same way APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
Applsys schema has applications technology layer products/objects like FND and AD,WF related data like tables and Indexes…etc.
Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
APPS is central Schema which holds synonyms for all other Users Database Objects.
APPLSYS and APPS should have same password.
To change password of APPS with FNDCPASS:
$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] SYSTEM APPLSYS [new_passs]
the APPS password will be updated when the APPLSYS password is changed.
Apps schema contains only Synonyms we can’t create tables in apps schema,where as other schemas contains tables, & all the objects.
Sometimes, developer/functional team might ask you access for APPS schema. In Production environments, its not at all recommended to give APPS access to developers.
In this case, we can create read_only Apps schema and give to developer team.
Here we are going to create a schema similar to the APPS schema but has only read-only priviliges.
step.1: Create the read-only schema, in this case lets call it APPSREAD.
SQL> CREATE USER APPSREAD IDENTIFIED BY xxxxxx default tablespace APPS_TS_TX_DATA;;
SQL> GRANT CONNECT, RESOURCE,CREATE SYNONYM TO APPSREAD;
step.2 : Generate a Script for creating database objects of Application User ‘APPS’
set echo off set
set linesize 300
select ‘create synonym ‘ || OBJECT_NAME || ‘ for ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘;’ from all_objects where OWNER in (‘APPS’) and
OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);
step.3: Generate a script for selecting database objects of Application User ‘APPS’
select ‘grant select on ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘ to APPSREAD;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);
step.4 : Connect to sqlplus as sysdaba and execute the following script
Step.5 : Connect to Read only schema User and check the objects.
SQL> conn APPSREAD/xxxxx
SQL> select count(*) from tab;