How to Create a Read Only Schema in Oracle R12

How to Create a Read Only Schema in Oracle R12. 

Method :1 

SQL> CREATE USER XXTEST IDENTIFIED BY XXTEST ;   
User created. 
SQL> 

SQL> GRANT CONNECT, RESOURCE,CREATE SYNONYM TO XXTEST ;
Grant succeeded.   
SQL> 

Generate a Script for creating database objects of Application User ‘APPS’

set echo off set
pagesize 0
set linesize 300
spool CREATEsynonyms.sql
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');   
spool off 

Generate a script for selecting database objects of Application User ‘APPS’

spool GrantSelect.sql   
select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to XXTEST ;' from all_objects  where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');   
spool off 

Connect to sqlplus  as sysdaba and execute the following script

SQL> @GrantSelect.sql 
SQL> @CREATEsynonyms.sql 

Connect to Read only schema User and check the objects.

SQL> conn oracle/oracle Connected. 
SQL> select count(*) from tab;     

How to Deregister Custom Top in EBS?

How To De-register Custom Applications (Doc ID 2085355.1)

To de-register a custom schema, you can run the script 

$AD_TOP/bin/adDeregisterCustomProd.pl 

as follows:

perl adDeregisterCustomProd.pl
  - APPS username
  - APPS Password
  - Application abbreviation for uninstall
  - Application ID (from fnd_application or fnd_product_installations)

Note - : 

Always backup!


Take a full backup (PROD) or backup the tables 
FND_APPLICATION, 
FND_ORACLE_USERID,
 FND_PRODUCT_INSTALLATIONS.

ORA-39181: Only partial table data may be exported due to fine grain access control

ORA-39181: Only partial table data may be exported due to fine grain access control

SOLUTION:

Provide the below privilege to the schema which you are trying to export.

grant EXEMPT ACCESS POLICY to <SCHEMA NAME>;