dba_objects
Name Type
---------------- -------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
Information about every object in the database.
To find an object in the database using dba_objects, run this query:
set pages 999
col owner format a15
col object_name format a40
col object_type format a20
select owner
, object_name
, object_type
from dba_objects
where lower(object_name) like lower('%&object%')
order by owner, object_type, object_name
/
It will prompt you for an object name; you can enter the whole name or part of it. It is not case sensitive.
To see how large an object is, run this query:
col segment_name format a20
select segment_name
, bytes "SIZE_BYTES"
, ceil(bytes / 1024 / 1024) "SIZE_MB"
from dba_segments
where segment_name like '&obj_name'
/
To list all obects that are owned by a user run this:
col object_name format a40
select object_name
, object_type
from dba_objects
where owner = '&user'
order by object_type, object_name
/
To list all invalid objects in the database:
set lines 200 pages 999
col "obj" format a40
select owner || '.' || object_name "obj",
object_type
from dba_objects
where status = 'INVALID'
/
For more useful DBA queries click here.
| 