Invisible Tables

A colleague said that he had some tables that showed up in one tool, but not in another, I think it was Enterprise Manager/Cloud Control. This is easy to forget if you don’t work with non-relational tables regularly. What happens is that only relational tables are listed in DBA_TABLES or USER_TABLES, but all tables can be found if you search DBA_OBJECTS / USER_OBJECTS for objects with OBJECT_TYPE=TABLE.

The documentation actually says: USER_TABLES describes the relational tables owned by the current user. You can try this code to demonstrate it:

select table_name from user_tables;

create or replace type my_object_type as object (
  brewery varchar2(100), beer varchar2(50));
/


create table tastings  of my_object_type;

select table_name from user_tables;

select object_name from user_objects where object_type='TABLE';

Note your user need the CREATE TYPE privilege to do this. This is the output I got from a newly created user:

no rows selected

Type MY_OBJECT_TYPE compiled


Table TASTINGS created.

no rows selected

OBJECT_NAME                                                                                                                     
--------------------------------------------------------------------------------------------------------------------------------
TASTINGS

Anyway, this won’t be a problem if you use SQL Developer: