Creating Users   «Prev 

What objects does a user own?

If you ever need to know if a user owns any objects and what they are, the DBA_OBJECTS view can provide the answer.
DBA_OBJECTS returns one row for each object in the database. Query it from SQL*Plus like this:
Oracle has many types of objects in dba_objects and you can display them by querying the dba_objects view.
SQL> select distinct object_type from dba_objects;

 OBJECT_TYPE
 -------------
 CLUSTER
 FUNCTION
 INDEX
 PACKAGE
 PACKAGE BODY
 PROCEDURE
 SEQUENCE
 SYNONYM
 TABLE
 TRIGGER
 VIEW

SQL> COLUMN object_name FORMAT A30
SQL> SELECT object_name, object_type
  2  FROM dba_objects
  3  WHERE owner='SYSTEM'
  4  ORDER BY object_type, object_name;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
AQ$_QUEUES_CHECK               INDEX
AQ$_QUEUES_PRIMARY             INDEX
AQ$_QUEUE_TABLES_PRIMARY       INDEX
AQ$_SCHEDULES_CHECK            INDEX
AQ$_SCHEDULES_PRIMARY          INDEX
DEF$_CALLDEST_N2               INDEX
This query returns a list of objects owned by the SYSTEM user. The COLUMN command is not strictly necessary, but it shortens the OBJECT_NAME column so the query results do not wrap around to two lines.