jump to navigation

Peeking under the Data Dictionary Hood July 7, 2009

Posted by mwidlake in internals.
Tags: ,

Have you ever wondered how some people seem to be able to find out how the Oracle database does things internally? How they work out where some of the interesting stuff is contained within the data dictionary and can pull it out?

Well, one of the simpler ways to peek under the data dictionary hood is to just look at the objects in the data dictionary itself. I’ve found some interesting things by looking at the data dictionary views. A lot of the dictionary objects areviews, like DBA_TABLES and DBA_COLUMNS. I’ll look at DBA_TABLESPACES {as it’s a bit smaller!}

You can get my script here – vw_txt.sql . As you will see, it is a terribly complex script… The only trick is to make sure that “set long 32767” as the view text is held in column of the ancient LONG datatype.

> @vw_txt
Enter value for vw_name: dba_tablespaces
old 6: where view_name like upper (nvl('&vw_name','WHOOPS')||'%')
new   6: where view_name like upper (nvl('dba_tablespaces','WHOOPS')||'%')
Any Key...>

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
                 4, 'READ ONLY', 'UNDEFINED'),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
                 'PERMANENT')), 1, 'TEMPORARY'),
decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
          decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
          decode(ts.plugged, 0, 'NO', 'YES'),
decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
                 'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
decode(bitand(ts.flags,256), 256, 'YES', 'NO'),
decode(tsattr.storattr, 1, 'STORAGE', 'HOST'),
decode(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
decode(bitand(ts.flags,64), 0, null,
decode(bitand(ts.flags,65536), 65536,'FOR ALL OPERATIONS',
                 'DIRECT LOAD ONLY'))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid

1 row selected.

You see a lot of decodes of bitand functions in these internal views. What is more interesting is to see what they are decoded into as it confirms what possible options there are.

Are you curious as to what all the possible object types in the database are? Whether your database happens to have examples of them or not? Peek inside the DBA_OBJECTS view {the following is part of that view}:-

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
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',
                               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, 'WINDOW 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',

Scanning the view definitions for something you become interested in is a little more tricky as the text is, as I said, stored in a long and you can’t run sql functions against a long. I use a bit of PL/SQL to get around the problem :-

find_vw_txt.sql .

-- find_vw_txt
-- martin widlake 13/8/08
-- can't search view text as it is in a long.
-- so let's try PL/SQL
set serveroutput on size unlimited
spool find_vw_txt.lst
set trims on  lines 120 pages 32
v_name varchar2(4000) :='&viewname';
v_search varchar2(100) :='&search_txt';
cursor get_vw_txt is
select u.name owner, o.name name, v.textlength textlen, v.text text
from sys.obj$ o
, sys.view$ v
   , sys.user$ u
where o.obj#     = v.obj#
and   o.owner#   = u.user#
and   o.name     like upper(v_name)||'%'
and v.textlength <32000; -- cant think how I stop a numeric
                          -- error trying to pull a larger long
-- into a varchar. bloody longs
v_text varchar2(32000);
v_where number;
  v_name :=upper (v_name);
  v_search := upper(v_search);
for vtr in get_vw_txt loop
--dbms_output.put_line('looking at '||vtr.name);
    v_text := vtr.text;
    v_text := upper(v_text);
    v_where := instr(v_text,v_search);
    if v_where !=0 then
dbms_output.put_line('view '||vtr.owner||'.'||vtr.name||':** '
||substr(v_text,greatest(0,v_where),80)||' **'
    end if;
  end loop;
spool off

It can be fun to dig into the internals this way. And useful. A few times I’ve been able to find out one or two things about how oracle is storing information.

It might occur to you that there are lots of internal pl/sql packages and you can look into them too:-

pkg_txt.sql will show you the contents of a package or stored function/procedure.
find_plsql_txt.sql will scan the stored PL/SQL for a text string.

However, a lot of the internal PL/SQL is wrapped, ie converted into a form you can’t peek into easily {well, in my case, at all, but some people know how}.

One last word of warning. You can find what look like interesting undocumented features when you peek under the covers. In V10 I came across the REVERSE function:

select reverse('Martin was here')
from dual

ereh saw nitraM

Nice :-).

That could be useful, yes?

I’ve checked in the 11g documentation and it is still not documented. I googled “oracle reverse function” and several people have also found it. A couple ask why such a useful thing is not documented…

Well, I was “lucky” enough to find out why it is undocumented. I tried to use the REVERSE function on a number { this wason v10.1 I think}.

My session core-dumped.

I was on a production system when I tried this {Yes I KNOW!!! It was a momentary lapse}!

It seems to work OK on and 11.1 but don’t. You have no idea if it will always work OK and no come-back to Oracle Corp if it blows up on you. My guess is they wrote the REVERSE function for internal use and did not test it for people using it “not as intended” {as I had done}.

So, my warning is, though it is fun to look under the covers and try things out, never, ever ever, ever do so on a production system. Or a test system used for proper testing. Or a development system those touchy developers are using. Or any system that you can’t afford to trash and recreate on a whim. Try it at home on your own PC, OK?


1. coskan - July 8, 2009

Hi Martin,

It is me again πŸ™‚

I like the reverse finding but for the definition of fixed views how about using

select dbms_metadata.get_ddl( object_type, object_name, owner )
from all_objects where upper(object_name) like upper(‘DBA_TABLES’);

Not my discovery πŸ™‚ Got the idea from “ddl” script of Tanel Poder

mwidlake - July 8, 2009

Hi Coskan,
As usual, a nice extra bit by you Coskan. {OK, by Tanel, but I’m sure he will be happy to have the suggestion spread around, especially as you cite your source}.
Don’t forget to “set long 32767” if using sqlplus, otherwise you just get the first line.
I never even thought to use dbms_metadata.get_ddl in that way, it’s nice as it also gives you the allocated view column names to match against the sql.

2. kurt - July 8, 2009

The real fun begins when you start rewriting some of those views for performance – like for example all_arguments .
If you use ADO/OLEDB for Oracle and use bind variables in a procedure/function call, the driver will generate a describe with dbms_describe.describe_procedure.
It basically has to find out what the datatypes of the arguments are.
This describe will then query the all_arguments view which will generate
a full tablescan on argument$. This table can become quite large.
Imagine telling a customer to use bind variables in his loops to save
cpu time on parsing and ending up bringing almost the server down πŸ˜‰
This was on 10.2 by the way …. maybe I should right a blog entry on it πŸ˜‰
I did rewrite all_arguments and cleared it with oracle support (of course).

mwidlake - July 8, 2009

Hi Kurt,

Heh! killing your customer’s system with binds. Nice one. πŸ™‚

Performance is one of the reasons I started digging down into the views too. I had issues with dba_tab_partitions {back on 9, maybe even 8} where a very large number of partitions was slow to query, so I wrote my own temporary script. I’m curious, did Oracle “borrow” your better version of ALL_ARGUMENTS for the next version? πŸ˜‰

I’ve also borrowed Oracle’s code to create “missing” views. There is DBA_TAB_STAT_HIST which shows the recent history of tables statistics gathered by the automatic job, but no equivalent to show the history of index statistics, so I pinched the view code and changed it to run for indexes:-
show_ish_full.sql .

kurt - July 8, 2009

It was similar to Bug No: 5197499
There, they rewrite the view to use a union all – i tried this by
creating my own view without replacing all_arguments.
But the ‘union all’ rewrite seemed a too extreme rewrite.
I believe in the end I added a hint /*+ use_hash(a o) */ and that
seemed to lower number of logical io’s enough to settle the server. Oracle support was ok with that.

3. B. Polarski - July 8, 2009

Nice article, gave me the idea to go a bit further and search in fixed views definitions and DBA views at same time. This script takes $V_STRING as input

sqlplus -s “$CONNECT_STRING” <<EOF

set lines 190
set serveroutput on size unlimited
pos number ;
v_text varchar2(32000);
dbms_output.put_line('Pos View Name Text');
dbms_output.put_line('—– —————————– —————————————————————-');
from v\$fixed_view_definition
where view_name in (select view_name
from v\$fixed_view_definition where substr(view_name,1,2) != 'GV'
select substr(view_name,2)
from v\$fixed_view_definition where substr(view_name,1,2) = 'GV' )
union all
select view_name,VIEW_DEFINITION
from v\$fixed_view_definition where substr(view_name,1,2) = 'GV' )
pos:=instr(c.view_definition,'$V_STRING') ;
if pos != 0 then
dbms_output.put_line(rpad(to_char(pos),5)||' '||rpad(c.view_name,30)||substr(c.view_definition,greatest(0,pos),80) );
end if;
end loop;
–dbms_output.put_line(chr(10)||'Checking DBA_% views '||chr(10));
for c in (select view_name,text
from dba_views where owner = 'SYS' and view_name like 'DBA%')
pos:=instr(v_text,'$V_STRING') ;
v_text:=regexp_replace(v_text,chr(10),' ');
if pos != 0 then
dbms_output.put_line(rpad(to_char(pos),5)||' '||rpad(c.view_name,30)||substr(v_text,greatest(0,pos),80) );
end if;

end loop;

ie : # doit kgllk

Pos View Name Text
—– —————————– —————————————————————-
750 GV$ACCESS kgllk l where l.kgllkuse=s.addr and l.kgllkhdl=d.kglhdadr and l.kglnahsh=d.kglna
16 GV$OPEN_CURSOR kgllkuse, kgllksnm, user_name, kglhdpar, kglnahsh, kgllksqlid, kglnaobj
348 DBA_DDL_LOCKS kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_
8 DBA_KGLLOCK kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype from x$kgllk union all
2137 DBA_LOCK_INTERNAL kgllktype, decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusi

But the credit remains yours

mwidlake - July 8, 2009

Hey, nice one. I like it.

I’ve pinched your script and put it in my sqltools box, with your name on top of course.

I had to filter out all views with more than 32767 characters but there is only one on my system at the moment, DBA_STREAMS_COLUMNS with 34424 characters.


4. Blogroll Report 26/06/2009 – 03/07/2009 « Coskan’s Approach to Oracle - July 11, 2009

[…] 17- How to see DBA_% table definitions Martin Widlake – Peeking Under the Data Dictionary […]

5. Martin Berger - July 15, 2009

beside checking the views to get informations how the data (data dictionary or x$) is structured, I like to see what Oracle is doing for some actions (e.g. what is dbms_xplan.display doing, or where does this ORA-1031 comes from).
For this I prefer to first flush the shared_pool (yes, I know, nothing for the production system, also not perfect for the test system where testers are doing business acceptance tests, or development where developers are hunting performance – I do it in the virtual container on my PC, ok?) and then enable event 10046 (level 4 is enough in this case, I doesn’t care of waits, only binds are of my interrest) and check for all row source queries oracle throws behind the curtain.
This gives a lot of informations how oracle access the data dictionary e.g. to check if a object exists or if the user is alowed to access it.
I guess, this is quite a good source of information, too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: