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)
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)
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