Oracle PL/SQL/System Tables Views/dba objects
Содержание
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>