table missing

[Roshan]

Oracle Database 10g
Red Hat Linux 6.4

Hi Erman,

I have a table(CBS_SUM_DUN2) which was in a schema called GOVINDEN. Unfortunately I cannot see that table. Reports are not working because of that missing table.

I searched recyclebin for that user but could not locate that table.

select * from recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

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

BO_GL_BALANCE    BIN$dbr/mPm8nULgUNIKZRsVvA==$0 TABLE        2018-09-13:09:30:01

BO_GL_BALANCE    BIN$dbopByFFWh/gUNIKZRsMcw==$0 TABLE        2018-09-13:08:30:01

BO_GL_BALANCE    BIN$dblSdQlF3qbgUNIKZRsCrA==$0 TABLE        2018-09-13:07:30:01

BO_GL_BALANCE    BIN$dbh73KqhcaPgUNIKZRt4Cw==$0 TABLE        2018-09-13:06:30:01

BO_GL_BALANCE    BIN$dbelUmBFt1vgUNIKZRtubQ==$0 TABLE        2018-09-13:05:30:01

BO_GL_BALANCE    BIN$dbbOtaRqVnjgUNIKZRtlMA==$0 TABLE        2018-09-13:04:30:01

BO_GL_BALANCE    BIN$dX6mFn5o9j/gUNIKZRstqg==$0 TABLE        2018-09-10:09:30:01

CCBS_DOSLIG0     BIN$dbMFlTHMVSLgUNIKZRsvFw==$0 TABLE        2018-09-12:23:59:01

CCBS_DOSLIG0     BIN$dZ7ntL5oFzfgUNIKZRs2dw==$0 TABLE        2018-09-11:23:59:01

CCBS_DOSLIG0     BIN$dYrJ4zA1uUngUNIKZRs93Q==$0 TABLE        2018-09-10:23:59:01

DM_GLBALANCES    BIN$dbr/mSv/zwDgUNIKZRsVuw==$0 TABLE        2018-09-13:09:30:01

DM_GLBALANCES    BIN$dbopB1oKkt7gUNIKZRsMdA==$0 TABLE        2018-09-13:08:30:01

BO_GL_BALANCE    BIN$dbX4JLCOPIHgUNIKZRtRAg==$0 TABLE        2018-09-13:03:30:01

BO_GL_BALANCE    BIN$dbUhmQDELJXgUNIKZRtHyQ==$0 TABLE        2018-09-13:02:30:01

BO_GL_BALANCE    BIN$dbRLCJINaBTgUNIKZRs+kQ==$0 TABLE        2018-09-13:01:30:02

 

I checked alert log and no alerts.

 

Please advise.

Regards,

Roshan

[ErmanArslansOracleBlog]

What do you mean by "I cannot see that table" ?

What is the output of the following?->

select * from dba_objects where object_name='CBS_SUM_DUN2';

[Roshan]

SQL> select * from dba_objects where object_name='CBS_SUM_DUN2';

no rows selected

SQL>

[Roshan]

Should I use logminer to trace the 'DROP' entry?

[ErmanArslansOracleBlog]

Then this table is really not available.
In this situation, you should restore your db backup and restore your db from there.
If your db is 12c, then you can restore your table from rman backup directly. However, it will also take some time..
If you have export backups, you can also use them to restore your db.

[ErmanArslansOracleBlog]

Logminer is useless here.
This is a drop operation. (not a dml)
What you will see with log miner will be the drop operation itself.. So you wont find the contents of any deleted data...

[Roshan]

ok. thanks for info.

If no backup is available, is there a way I can recover it? even if there is no dump?

How do I know the table name from the table name

BIN$dZVHqyraPVDgUNIKZRs6Gw==$0

from log miner entry?

[ErmanArslansOracleBlog]

you can use SELECT object_name, original_name FROM dba_recyclebin.

original name is the human readable name of that object.

And no, there is no way.

you may try using log miner to mine the archive logs from the time you created the table till the time you dropped it (to see the dml changes) , but this is complex and there is no guarantee for that as well.

[Roshan]

Thanks.

With the information below from logminer, at least can we trace the user/process who executed the drop table purge command?

drop table "GOVINDEN"."BIN$dVZqaHvkbvfgUNIKZRsv9w==$0" purge;

 

 

login_username=GOVINDEN client_info= OS_username=oracle Machine_name=RHIS-CCBS-B

ILLDB-01 OS_terminal= OS_process_id=28702 OS_program_name=sqlplus@RHIS-CCBS-BILL

DB-01 (TNS V1-V3)

09-11-2018 17:30:24

[ErmanArslansOracleBlog]

I couldn't understand what you are trying to do and what&why you are trying to decrpyt?