Skip to Main Content
  • Questions
  • Object reference in DBA_TAB_PRIVS but no object in DBA_OBJECTS

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Glenn.

Asked: September 07, 2021 - 2:43 pm UTC

Last updated: September 08, 2021 - 10:13 am UTC

Version: 19.11

Viewed 100+ times

You Asked

I have found records in DBA_TAB_PRIVS that do not have corresponding records in DBA_OBJECTS.

select * from dba_tab_privs where owner='ODSMGR' and table_name like 'BIN$%';

Returns privileges defined against a table.

select * from dba_objects where owner='ODSMGR' and object_name like 'BIN$%';

Returns no records.

There is a process in this database that is trying to process REVOKE statements for the privs it finds in dba_tab_privs, but the statements are blowing up because the object doesn't seem to exist (in DBA_OBJECTS). How can I process these REVOKE statements (to get them out of DBA_TAB_PRIVS) when they don't show up in DBA_OBJECTS?

Thanks

and we said...

The BIN$ prefix is for tables in the recyclebin.

After you drop a table, by default the database places it in the recyclebin*. This enables you to recover it quickly (using flashback table ... to before drop) if you made a mistake!

The database preserves all privileges, indexes, etc. associated with the table to make this recovery as smooth as possible.

You can query the recyclebin to check which objects are there:

create table t (
  c1 int
);
insert into t values ( 1 );
commit;
grant select on t to u;

drop table t;

select table_name from dba_tab_privs 
where  table_name like 'BIN$%';

TABLE_NAME                       
BIN$y3nmmCjgXJXgUwsAAAqPuw==$0 

select * from dba_objects 
where  object_name like 'BIN$%';

no rows selected

select object_name 
from   recyclebin
where  original_name = 'T';

OBJECT_NAME                      
BIN$y3nmmCjgXJXgUwsAAAqPuw==$0  


How can I process these REVOKE statements (to get them out of DBA_TAB_PRIVS) when they don't show up in DBA_OBJECTS?

You shouldn't really do this, because if you want to undrop the table you almost certainly want to restore the privileges too.

If you absolutely must do this you can do so by quoting the object name:
revoke select on "BIN$y3nmmCjgXJXgUwsAAAqPuw==$0" from u;

select table_name from dba_tab_privs 
where  table_name like 'BIN$%';

no rows selected


Or - if you're sure you don't want to recover these tables - you could empty the recyclebin:

select object_name 
from   recyclebin
where  original_name = 'T';

OBJECT_NAME                      
BIN$y3nmmCjgXJXgUwsAAAqPuw==$0

purge recyclebin;

select object_name 
from   recyclebin
where  original_name = 'T';

no rows selected


You can purge specific tables, tablespaces, and indexes instead of the whole thing if you want.

* It's permanent removal if the recyclebin is disabled or you add the purge clause when dropping the table

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.