Re: Is it possible to peek at the definition of x$tables?

From: Tanel Poder <tanel_at_poderc.com>
Date: Wed, 3 Mar 2010 19:08:02 +0800
Message-ID: <4602f23c1003030308g70d5cb02u73fef2e404e99ca4_at_mail.gmail.com>

You can just DESC X$ASH to see its columns. X$ tables are not views, so they don't have view SQL hardcoded like V$ do.

Also, you can use my x$ describe script (xde2.sql, attached), which describes x$ tables, shows you the data offsets and also any fixed indexes available for the X$ table (the last column):

SQL> _at_xde2 x$kglna%
Describe X$ tables and show indexed columns...

TABLE_NAME             COLUMN_NAME                    DATA_TYPE
 KQFCOSIZ     OFFSET OFF_HEX    IDX
---------------------- ------------------------------ --------------------
---------- ---------- --------- ----
X$KGLNA                ADDR                           RAW(8)
       8          0      0x0
                       INDX                           NUMBER(4)
        4          0      0x0
                       INST_ID                        NUMBER(4)
        4          0      0x0
                       KGLHDADR                       RAW(8)
       8          0      0x0
                       KGLNAHSH                       NUMBER(4)
        4          8      0x8     1
                       KGLOBOCT                       NUMBER(2)
        2         12      0xC
                       KGLNASQLID                     VARCHAR2(13)
      13         14      0xE     2
                       PIECE                          NUMBER(2)
        2         28     0x1C
                       NAME                           VARCHAR2(64)
      64         31     0x1F
X$KGLNA1               ADDR                           RAW(8)
       8          0      0x0
                       INDX                           NUMBER(4)
        4          0      0x0
                       INST_ID                        NUMBER(4)
        4          0      0x0
                       KGLHDADR                       RAW(8)
       8          0      0x0
                       KGLNAHSH                       NUMBER(4)
        4          8      0x8     1
                       KGLOBOCT                       NUMBER(2)
        2         12      0xC
                       KGLNASQLID                     VARCHAR2(13)
      13         14      0xE     2
                       PIECE                          NUMBER(2)
        2         28     0x1C
                       NAME                           VARCHAR2(64)
      64         31     0x1F

18 rows selected.

The OCI command types are documented in Oracle docs and from Oracle 11.2 there's also a new view V$SQLCOMMAND which lists all commands and their types.

--
Tanel Poder
http://tech.e2sn.com



On Wed, Mar 3, 2010 at 6:38 PM, Martin Bach
<development_at_the-playground.de>wrote:



> Dear list members,

>

> I am trying to find out more about the sql_opcode column in

> gv$active_session_history. The reference is not very helpful by stating

> the obvious (command type executed).

>

> I was hoping to get more clues from the view defition, but all it does

> (from v$fixed_view_definition) is telling me that

> v$active_session_history references x$ash and x$kewash.

>

> I found x$ash in v$fixed_table but can't see how it's defined. Maybe

> that's a dead end anyway...

>

> Any pointers appreciated.

>

> Martin

>

> --

> Martin Bach

> OCM 10g

> http://martincarstenbach.wordpress.com

> http://www.linkedin.com/in/martincarstenbach

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>





--
http://www.freelists.org/webpage/oracle-l




application/octet-stream attachment: xde2.sql

Received on Wed Mar 03 2010 - 05:08:02 CST