Steps to Recover dropped table from Recycle-Bin in Oracle

Scenario :

SQL> select count(*) from APPS.FND_EXECUTABLES;

select count(*) from APPS.FND_EXECUTABLES

                                 *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

Solution :

Whenever the object is dropped the object will be moved to recyclebin.

In our case we accidentally dropped fnd_executable .

Below steps shows how we recovered the dropped table

1. drop table applsys.fnd_executable;

     Table dropped

 When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts that may arise in the following circumstances:

• A user drops a table, creates another with the same name, then drops the second table.
• Two users have tables with the same name, and both users drop their tables.

e.g : BIN$$globalUID$version

• globalUID is a globally unique, 24 character long identifier generated for the object.
• version is a version number assigned by the database

2. 

SQL> show recyclebin;

ORIGINAL NAME                  RECYCLEBIN NAME                         TYPE         DROP TIME

--------------------------   --------------------------------------------------- ------------ ------------------------------

FND_EXECUTABLES    BIN$OJ50JJ9cGO/gU6EBqMDpjw==$0   TABLE   2016-07-27:18:43:25

The database also provides two views for obtaining information about objects in the recycle bin:

USER_RECYCLEBIN               - Lets users see their own dropped objects in the recycle bin. It has                                                            a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN                  - Lets administrators see all dropped objects in the recycle bin.

3. Find whether the object can be recovered or not checking can_undrop value if it is yes we can

SQL > select owner, object_name, original_name, operation, type, can_undrop, can_purge, droptime from dba_recyclebin where original_name='FND_EXECUTABLES';

4. Performing Flash back to recover table

SQL> flashback table applsys.FND_EXECUTABLES to before drop;

Flashback complete.

5.  Check now

SQL > select count(*) from APPS.FND_EXECUTABLES;

  COUNT(*)

----------

      9400

Table Recovered Successfully

For more Info https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

Creating APPS Readonly Access schema for Oracle Application Users

step 1: Connect as sysdba and create the database user to be used for apps read only schema

bash $ sqlplus "/ as sysdba"

SQL > create user apps_readonly identified by apps_readonly default tablespace APPS_TS_MEDIA;

SQL> grant connect, resource to apps_readonly;

SQL> grant create synonym to apps_readonly;

SQL> exit;


step 2: Connect as APPS user and run the SQL commands:

bash $ sqlplus apps/apps

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

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

SQL> spool off

SQL> spool grant_select.sql

SQL> select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to apps_readonly;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

SQL> spool off

SQL> exit;


step 3:

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

step 4:  connect as apps_readonly

bash $ sqlplus apps_readonly/apps_readonly

SQL> @create_synonyms.sql


SQL> exit;

Now users can use “apps_readonly” schema to have the read only access to Applications Data