Re: oracle-l Digest V14 #334
Date: Tue, 2 Jan 2018 18:43:19 +0000
Message-ID: <DF4PR8401MB12760E26FC865716E0D6D434CC190_at_DF4PR8401MB1276.NAMPRD84.PROD.OUTLOOK.COM>
Alan, what about just issuing select distinct object_type from dba_objects order by 1? The query will not find object types not in use but it will give you a list of all used object types without resorting to parsing a decode.
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Stefan Knecht <knecht.stefan_at_gmail.com> Sent: Sunday, December 24, 2017 11:53:14 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!
Stefan
On Sun, Dec 24, 2017 at 10:54 PM, Alan Sterger <asterger_at_earthlink.net<mailto:asterger_at_earthlink.net>> wrote: Stefan,
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 <knecht.stefan_at_gmail.com<mailto:knecht.stefan_at_gmail.com>>
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?
Cheers
Stefan
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net<http://zztat.net> | Support our Indiegogo campaign at igg.me/at/zztat<http://igg.me/at/zztat> | _at_zztat_oracle
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 02 2018 - 19:43:19 CET