RE: oracle-l Digest V14 #334 (sql.bsq, now modularized to dcore.bsq)

From: Mark W. Farnham <>
Date: Sun, 24 Dec 2017 14:32:29 -0500
Message-ID: <00d301d37cee$0878c6f0$196a54d0$>

Look for something like: C:/ora/p/12.1/dbhome/RDBMS/ADMIN as a directory, depending on the length of symbolic tokens you use in OFA.  

For example if you like having very long strings everywhere with all the overhead that entails, you might have:    


anyway, find that directory.  

Then, depending on vintage, you will find one or more .bsq files. For a long time there was just sql.bsq. Recently sql.bsq has become a reference to a list of .bsq files.

Don’t change any of these files unless… hmm… let’s just say don’t change any of these files.  

To conveniently look at them and avoid accidental change hilarity, I usually copy, for example, dcore.bsq to dcore_bsq.txt and never even bring the originals into an editor.  

I think most of what you’re looking for is in dcore and dobj, but these can indeed change between versions and since there can be user defined complex types. I haven’t chased down all the references in a couple decades, and once you get to oracle 8 with user defined types there is no really comprehensive final answer (unless “user defined” is good enough for you as a type.).  

An itch in my little finger is saying there might be a C .h file or something else that has exactly what you want, but I don’t believe anyone can share that with you in lieu of onerous and likely unobtainable legal agreements. And I think the general licenses still prohibit decompilation (and I’m not certain you can get a version with the symbol table unstripped), so this may be impossible.  

This all would be a nice thing for Oracle to publish as a document. Given the size of the documentation library I can’t be certain whether it is published or not. But certainly the operation of the oracle program requires that its programmers have this reference and if they add a type it is an intense integration operation to get it right everywhere.  


PS: you can learn a lot just reading these files but there is a real risk of consuming more time than it is worth. I do still love the sizing of some fundamental tables and the comments from early developers about that sizing and the number and size of columns they expect in a table.  

From: [] On Behalf Of Stefan Knecht Sent: Sunday, December 24, 2017 11:53 AM To: Alan Sterger
Cc: oracle-l-freelists
Subject: Re: oracle-l Digest V14 #334  

Thanks Alan - I'm indeed aware that there is a representation of that data in dba_objects. However, that view changes between versions, and I was looking for a more "permanent" solution, without the need to write a potentially breaking parser.  

I have since snooped around a bit more and it seems that Oracle themselves are constantly using that decode to do the mapping, which leads me to believe that such a place does not exist.  

Cheers nonetheless and have a good X-Mas yourself!  


On Sun, Dec 24, 2017 at 10:54 PM, Alan Sterger <> wrote:


DBMS_METADATA can retrieve object types within DBA_OBJECTS with this query:

SQL> set pagesize 0
SQL> set long 90000
SQL> select

  2 dbms_metadata.get_ddl('VIEW', 'DBA_OBJECTS', 'SYS')   3 from dual;

where get_ddl three arguments are:

  1- object type (table, index, view)
  2- object name
  3- schema owner

On my client's 11R2 database, query above produces a decode list (0 to 101) of o.type# with human readable object_type (varchar19).

Merry Christmas,

  • Alan Sterger

On 12/24/2017 1:05 AM, FreeLists Mailing List Manager wrote:

oracle-l Digest Sat, 23 Dec 2017 Volume: 14 Issue: 334

In This Issue:

                Oracle TYPE# to human readable name


From: Stefan Knecht <> Date: Sat, 23 Dec 2017 19:50:39 +0700
Subject: Oracle TYPE# to human readable name

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?





zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!

Visit us at | Support our Indiegogo campaign at | _at_zztat_oracle

-- Received on Sun Dec 24 2017 - 20:32:29 CET

Original text of this message