Re: Oracle TYPE# to human readable name

From: Sayan Malakshinov <>
Date: Mon, 25 Dec 2017 01:21:15 +0300
Message-ID: <>

Hi Stefan,

Perhaps you just need dba_types?
Anyway, you can use the following queries to find any type:

  1. You can find every sys.obj$.type# in the same table by obj#: select o.type#,t.* from sys.obj$ o ,sys.obj$ t where o.type#=t.obj#; -- or
    • ,dba_objects t where o.type#=t.object_id;
  2. Oracle built-in datatypes: select * from dba_types where owner is null; or select typecode, owner#, name from sys.type$ t,sys.obj$ o where o.oid$ = t.tvoid and bitand(, 64) = 64 -- is null order by typecode;
  3. If you you want to find source code of objects and collections even for automatically generated types from packages: select as owner, o.obj# , o.owner# , , o.namespace, decode(t.typecode, 108, 'OBJECT', 122, 'COLLECTION', type_name, -- c.* (select listagg(source) within group(order by line) from sys.source$ s where o.obj#=s.obj#) src from sys.obj$ o, sys.user$ u,sys.type$ t,sys.collection$ c where 1=1 and o.owner#=u.user# and o.oid$ = t.tvoid(+) and c.toid(+) = t.tvoid and o.flags=1048576 and o.type# in (10,13) /

On Sat, Dec 23, 2017 at 3:50 PM, Stefan Knecht <> wrote:

> Hi all
> Does anyone know of a structure (table, fixed table, anything) that has a
> mapping of all the object types known to the present Oracle version?
> What I'm looking for is basically a list, a mapping, of all possible
> values of obj$.type# and their human-readable counterpart. If you look at
> the definition of the DBA_OBJECTS view, it's basically in there with a
> DECODE() on type#. But I'd rather refrain from parsing that if I can help
> it.
> Is this data available anywhere within Oracle?
> Cheers
> Stefan
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at | Support our Indiegogo campaign at
> | _at_zztat_oracle

Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate

Received on Sun Dec 24 2017 - 23:21:15 CET

Original text of this message