VIEW: SYS.DBA_OBJECTS

Source

View Text - Preformatted

select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                      42, NVL((SELECT 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
                                     and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
                      100, 'FILE WATCHER', 101, 'DESTINATION',
                      114, 'SQL TRANSLATION PROFILE',
                      115, 'UNIFIED AUDIT POLICY',
                     'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       o.namespace,
       o.defining_edition,
       decode(bitand(o.flags, 196608),
              65536, 'METADATA LINK', 131072, 'OBJECT LINK', 'NONE'),
       case when o.type# in (4,5,7,8,9,11,12,13,14,22,87,114) then
           decode(bitand(o.flags, 1048576), 0, 'Y', 1048576, 'N', 'Y')
         else null end,
       decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type# !=  10 /* NON-EXISTENT */
  and o.name != '_NEXT_OBJECT'
  and o.name != '_default_auditing_options_'
  and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
       'DATABASE LINK',
       l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL,
       'NONE', NULL, 'N'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, O.SUBNAME
, O.OBJ#
, O.DATAOBJ#
, DECODE(O.TYPE#
, 0
, 'NEXT OBJECT'
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
, 4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
, 7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
, 11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
, 13
, 'TYPE'
, 14
, 'TYPE BODY'
, 19
, 'TABLE PARTITION'
, 20
, 'INDEX PARTITION'
, 21
, 'LOB'
, 22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 24
, 'QUEUE'
, 28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 30
, 'JAVA RESOURCE'
, 32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
, 34
, 'TABLE SUBPARTITION'
, 35
, 'INDEX SUBPARTITION'
, 40
, 'LOB PARTITION'
, 41
, 'LOB SUBPARTITION'
, 42
, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM SUM$ S
WHERE S.OBJ#=O.OBJ#
AND BITAND(S.XPFLAGS
, 8388608) = 8388608)
, 'MATERIALIZED VIEW')
, 43
, 'DIMENSION'
, 44
, 'CONTEXT'
, 46
, 'RULE SET'
, 47
, 'RESOURCE PLAN'
, 48
, 'CONSUMER GROUP'
, 51
, 'SUBSCRIPTION'
, 52
, 'LOCATION'
, 55
, 'XML SCHEMA'
, 56
, 'JAVA DATA'
, 57
, 'EDITION'
, 59
, 'RULE'
, 60
, 'CAPTURE'
, 61
, 'APPLY'
, 62
, 'EVALUATION CONTEXT'
, 66
, 'JOB'
, 67
, 'PROGRAM'
, 68
, 'JOB CLASS'
, 69
, 'WINDOW'
, 72
, 'SCHEDULER GROUP'
, 74
, 'SCHEDULE'
, 79
, 'CHAIN'
, 81
, 'FILE GROUP'
, 82
, 'MINING MODEL'
, 87
, 'ASSEMBLY'
, 90
, 'CREDENTIAL'
, 92
, 'CUBE DIMENSION'
, 93
, 'CUBE'
, 94
, 'MEASURE FOLDER'
, 95
, 'CUBE BUILD PROCESS'
, 100
, 'FILE WATCHER'
, 101
, 'DESTINATION'
, 114
, 'SQL TRANSLATION PROFILE'
, 115
, 'UNIFIED AUDIT POLICY'
, 'UNDEFINED')
, O.CTIME
, O.MTIME
, TO_CHAR(O.STIME
, 'YYYY-MM-DD:HH24:MI:SS')
, DECODE(O.STATUS
, 0
, 'N/A'
, 1
, 'VALID'
, 'INVALID')
, DECODE(BITAND(O.FLAGS
, 2)
, 0
, 'N'
, 2
, 'Y'
, 'N')
, DECODE(BITAND(O.FLAGS
, 4)
, 0
, 'N'
, 4
, 'Y'
, 'N')
, DECODE(BITAND(O.FLAGS
, 16)
, 0
, 'N'
, 16
, 'Y'
, 'N')
, O.NAMESPACE
, O.DEFINING_EDITION
, DECODE(BITAND(O.FLAGS
, 196608)
, 65536
, 'METADATA LINK'
, 131072
, 'OBJECT LINK'
, 'NONE')
, CASE WHEN O.TYPE# IN (4
, 5
, 7
, 8
, 9
, 11
, 12
, 13
, 14
, 22
, 87
, 114) THEN DECODE(BITAND(O.FLAGS
, 1048576)
, 0
, 'Y'
, 1048576
, 'N'
, 'Y') ELSE NULL END
, DECODE(BITAND(O.FLAGS
, 4194304)
, 4194304
, 'Y'
, 'N') FROM SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U WHERE O.OWNER# = U.USER#
AND O.LINKNAME IS NULL
AND O.TYPE# != 10 /* NON-EXISTENT */
AND O.NAME != '_NEXT_OBJECT'
AND O.NAME != '_DEFAULT_AUDITING_OPTIONS_'
AND BITAND(O.FLAGS
, 128) = 0 UNION ALL SELECT U.NAME
, L.NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'DATABASE LINK'
, L.CTIME
, TO_DATE(NULL)
, NULL
, 'VALID'
, 'N'
, 'N'
, 'N'
, NULL
, NULL
, 'NONE'
, NULL
, 'N' FROM SYS.LINK$ L
, SYS.USER$ U WHERE L.OWNER# = U.USER#