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
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