Creating Users   «Prev 

What Objects does 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:

Categorize the Main Database Objects

There are various types of objects that can exist within a database, many more with the current release than with earlier versions. All objects have names, and all objects are owned by someone. The "someone" is a database user, such as Human Resources. The objects the user owns are their schema. An object's name must conform to certain rules.

Object Types

This query lists the object types that happen to exist in this particular database, with a count of how many there are:
SELECT object_type, count(object_type)
FROM dba_objects
GROUP BY object_type
ORDER BY object_type;

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.