в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:28Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Tables Views/dba objects - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия