Wednesday 22 June 2016

Apps Read Only schema creation in R12.2

check the invalid objects before creating apps read only schema

sqlplus / as sysdba

create user appsro identified by appsro;
grant connect, resource to appsro;
grant create synonym to appsro;
commit;

sqlplus apps/oracle1234
set head off
set newpage none
set pagesize 9999
spool create_synonyms.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');
spool off

spool grant_select.sql
select 'grant 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');
spool off
exit


sqlplus / as sysdba
@grant_select.sql
exit;

sqlplus SYSTEM/manager121
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

sqlplus appsro/appsro
@create_synonyms.sql
exit;

after completion of above steps validate the invalid objects count.

Issue:
======
SQL> @create_synonyms.sql

But many synonyms creation failed while executing create_synonyms.sql. like
ORA-38818: illegal reference to editioned object APPS.OE_ENDECA_FULFILL_DELAY_V..


Solution:
========
Before running create_synonym pls do the below steps.

sqlplus SYSTEM
SQL> alter session set current_schema=APPS;
SQL> exec AD_ZD_PREP.ENABLE_CUSTOM_USER('CUSTOM_SCHEMA');

Ref: Create And Register CUSTOM Schema On EBS 12.2 (Doc ID 1929668.1)

1 comment:

  1. In 12.2 version for editioned objects, grant statement would cause lots on invalidations in the database and AD_ZD.GRANT_PRIVS procedure should be used instead.

    ReplyDelete