Oracle PL/SQL/System Tables Views/dba objects

Материал из SQL эксперт
Перейти к: навигация, поиск

Get all system packages

   <source lang="sql">
   

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.



 </source>
   
  


List all not valid database objects

   <source lang="sql">
  

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>


 </source>
   
  


List all sys owned package name

   <source lang="sql">
  

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.


 </source>
   
  


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

   <source lang="sql">
   

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: --


 </source>
   
  


Query dba_objects table group by object_type

   <source lang="sql">
   

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> --


 </source>
   
  


Query object name by object id against dba_objects

   <source lang="sql">
  

SQL> select Object_Name

 2    from DBA_OBJECTS
 3   where Object_ID = 2783;
                                                             

OBJECT_NAME


ALL_DEF_AUDIT_OPTS


 </source>