Oracle PL/SQL/System Tables Views/dba objects

Get all system packages

    
SQL>
SQL> select object_name
  2  from dba_objects
  3  where owner = "SYS"
  4  and object_type = "PACKAGE";
OBJECT_NAM
----------
STANDARD
DBMS_STAND
ARD
DBMS_REGIS
TRY
442 rows selected.

List all not valid database objects

   
SQL> select owner, object_name, object_type
  2  from dba_objects where status ^= "VALID";

OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------
OBJECT_TYPE
-------------------
PUBLIC
DBA_HIST_FILESTATXS
SYNONYM
PUBLIC
DBA_HIST_SQLSTAT
SYNONYM
PUBLIC
DBA_HIST_SQLBIND
SYNONYM
PUBLIC
DBA_HIST_SYSTEM_EVENT
SYNONYM
PUBLIC
DBA_HIST_WAITSTAT
SYNONYM

                          Page:   2
OWNER
------------------------------
OBJECT_NAME
------------------------------------------------------
OBJECT_TYPE
-------------------
PUBLIC
DBA_HIST_LATCH
SYNONYM
PUBLIC
DBA_HIST_LATCH_MISSES_SUMMARY
SYNONYM
PUBLIC
DBA_HIST_DB_CACHE_ADVICE
SYNONYM
PUBLIC
DBA_HIST_ROWCACHE_SUMMARY
SYNONYM
PUBLIC
DBA_HIST_SGASTAT
SYNONYM



201 rows selected.
SQL>
SQL>

List all sys owned package name

   
SQL> select object_name
  2   from dba_objects
  3   where owner = "SYS"
  4   and object_type = "PACKAGE";
Enter...
STANDARD
DBMS_STANDARD
DBMS_REGISTRY
DBMS_REGISTRY_SERVER
XML_SCHEMA_NAME_PRESENT
UTL_RAW
DBMS_ADVISOR
PLITBLM
SYS_STUB_FOR_PURITY_ANALYSIS
PIDL
DIANA
DIUTIL
DBMS_PICKLER
DBMS_JAVA_TEST
UTL_FILE
UTL_TCP
UTL_INADDR
UTL_SMTP
UTL_HTTP
UTL_URL
UTL_ENCODE
Enter...
UTL_GDK
DBMS_LOB
UTL_SYS_COMPRESS
UTL_COMPRESS
UTL_I18N
UTL_LMS
DBMS_WARNING
DBMS_WARNING_INTERNAL
DBMS_SPACE_ADMIN
DBMS_TYPES
DBMS_TRANSACTION
DBMS_SESSION
DBMS_UTILITY
DBMS_ROWID
DBMS_PCLXUTIL
DBMS_ERRLOG
DBMS_ASSERT
DBMS_SPACE
DBMS_TRANSACTION_INTERNAL_SYS
DBMS_APPLICATION_INFO
DBMS_LOCK

442 rows selected.

list table, table partition, index, index partition and lob from dba_objects

    
SQL>
SQL> set echo off
SQL> set verify off
SQL> set pagesize 9999
SQL>
SQL> column object_name format a30
SQL> column tablespace_name format a30
SQL> column object_type format a12
SQL> column status format a1
SQL>
SQL> break on object_type skip 1
SQL>
SQL> select object_type, object_name,
  2         decode( status, "INVALID", "*", "" ) status,
  3         decode( object_type,
  4                  "TABLE", (select tablespace_name
  5                              from dba_tables
  6                             where table_name = object_name
  7                               and owner = upper("&1")),
  8                  "TABLE PARTITION", (select tablespace_name
  9                                        from dba_tab_partitions
 10                                       where partition_name = subobject_name
 11                                         and owner = upper("&1")),
 12                  "INDEX", (select tablespace_name
 13                              from dba_indexes
 14                             where index_name = object_name
 15                               and owner = upper("&1")),
 16                  "INDEX PARTITION", (select tablespace_name
 17                                        from dba_ind_partitions
 18                                       where partition_name = subobject_name
 19                                         and owner = upper("&1")),
 20                  "LOB", (select tablespace_name
 21                            from dba_segments
 22                           where segment_name = object_name
 23                             and owner = upper("&1")),
 24                  null ) tablespace_name
 25    from dba_objects a
 26   where owner = upper("&1")
 27   order by object_type, object_name
 28  /
Enter value for 1: column status format a10
Enter value for 1:
Enter value for 1: --

Query dba_objects table group by object_type

    
SQL>
SQL>
SQL> select object_type, count(*)
  2  from dba_objects
  3  where rownum < 50
  4  group by object_type
  5  /
OBJECT_TYPE            COUNT(*)
-------------------- ----------
TABLE                        19
INDEX                        25
CLUSTER                       5
3 rows selected.
SQL> --

Query object name by object id against dba_objects

   
SQL> select Object_Name
  2    from DBA_OBJECTS
  3   where Object_ID = 2783;
                                                              
OBJECT_NAME
------------------------------------------------------------------------------------------------------------------------
ALL_DEF_AUDIT_OPTS