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.