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.
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)
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;
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;
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.