Tuesday, 16 April 2019

Apps Read Only schema creation in R12.2

1) check the invalid objects before creating apps read only schema

2) sqlplus / as sysdba

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

3)sqlplus apps/oracle#xyz
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

4)sqlplus / as sysdba
 @grant_select.sql
 exit;

5)sqlplus SYSTEM/manager
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSROD');

6)sqlplus appsrod/appsrod#xyz
@create_synonyms.sql
exit;

after completion of above steps validate the invalid objects count.

No comments:

Post a Comment