eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Oracle10gR2 autotrace function was changed and enhanced | Blog首页 | Oracle10gR2 New Feature:V$SESSION change tracking »

Oracle数据字典说明
modb.pro

作者：eygle | 【转载请注】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：
逐渐整理记录Oracle数据字典表的创建、内容及含义。

是自己学习的一个过程，权当记录，给自己参考。











View_Name Meanings Come from
GV$ENQUEUE_LOCK This view displays
all locks owned
by enqueue state
objects. 
SELECT /*+ ORDERED USE_NL(L), USE_NL(S), USE_NL(R) +*/
            s.inst_id, l.addr, l.ksqlkadr, s.ksusenum, r.ksqrsidt, r.ksqrsid1,
            r.k sqrsid2, l.ksqlkmod, l.ksqlkreq, l.ksqlkctim, l.ksqlklblk
            FROM x$ksqeq l, x$ksuse s, x$ksqrs r
            WHERE l.ksqlkses = s.addr
            AND BITAND (l.kssobflg, 1) != 0
            AND (l.ksqlkmod != 0 OR l.ksqlkreq != 0)
            AND l.ksqlkres = r.addr
GV$PARAMETER Displays information
about the
initialization
parameters. 
            SELECT x.inst_id, x.indx + 1, ksppinm, ksppity, ksppstvl, ksppstdf,
            DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE'),
            DECODE (BITAND (ksppiflg / 65536, 3),
            1, 'IMMEDIATE',
            2, 'DEFERRED',
            3, 'IMMEDIATE',
            'FALSE'
            ),
            DECODE (BITAND (ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'),
            DECODE (BITAND (ksppstvf, 2), 2, 'TRUE', 'FALSE'), ksppdesc,
            ksppstcmnt
            FROM x$ksppi x, x$ksppcv y
            WHERE (x.indx = y.indx)
            AND ((TRANSLATE (ksppinm, '_', '#') NOT LIKE '#%') OR (ksppstdf = 'FALSE')
            )
GV$RESOURCE_LIMIT Global resource
use for some of
the system resources 
SELECT inst_id, ksurlmnm, ksurlmcv, ksurlmmv,
            LPAD (DECODE (BITAND (ksurlmfg, 1),
            0, TO_CHAR (ksurlmia),
            'UNLIMITED'
            ),
            10
            ),
            LPAD (DECODE (BITAND (ksurlmfg, 2),
            0, TO_CHAR (ksurlmlv),
            'UNLIMITED'
            ),
            10
            )
            FROM x$ksurlmt
GV$SEGSTAT Displays information
about segment-level
statistics 
select inst_id,
            fts_tsn,
            fts_objn,
            fts_objd,
            fts_statnam,
            fts_statid,
            fts_staval
            from x$ksolsfts
            where fts_inte = 0
GV$SESSION (Oracle9iR2 Version) Lists session
information for
each current
session. 
SELECT inst_id, addr, indx, ksuseser, ksuudses, ksusepro, ksuudlui, ksuudlna,
            ksuudoct, ksusesow, DECODE (ksusetrn, HEXTORAW ('00'), NULL, ksusetrn),
            DECODE (ksqpswat, HEXTORAW ('00'), NULL, ksqpswat),
            DECODE (BITAND (ksuseidl, 11),
            1, 'ACTIVE',
            0, DECODE (BITAND (ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
            2, 'SNIPED',
            3, 'SNIPED',
            'KILLED'
            ),
            DECODE (ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'),
            ksuudsid, ksuudsna, ksuseunm, ksusepid, ksusemnm, ksusetid, ksusepnm,
            DECODE (BITAND (ksuseflg, 19),
            17, 'BACKGROUND',
            1, 'USER',
            2, 'RECURSIVE',
            '?'
            ),
            ksusesql, ksusesqh, ksusepsq, ksusepha, ksuseapp, ksuseaph, ksuseact,
            ksuseach, ksusecli, ksusefix, ksuseobj, ksusefil, ksuseblk, ksuseslt,
            ksuseltm, ksusectm, DECODE (BITAND (ksusepfl, 16), 0, 'NO', 'YES'),
            DECODE (ksuseft,
            2, 'SESSION',
            4, 'SELECT',
            8, 'TRANSACTIONAL',
            'NONE'
            ),
            DECODE (ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
            DECODE (ksusefs, 1, 'YES', 'NO'), ksusegrp,
            DECODE (BITAND (ksusepfl, 16),
            16, 'ENABLED',
            DECODE (BITAND (ksusepfl, 32), 32, 'FORCED', 'DISABLED')
            ),
            DECODE (BITAND (ksusepfl, 64),
            64, 'FORCED',
            DECODE (BITAND (ksusepfl, 128), 128, 'DISABLED', 'ENABLED')
            ),
            DECODE (BITAND (ksusepfl, 512),
            512, 'FORCED',
            DECODE (BITAND (ksusepfl, 256), 256, 'DISABLED', 'ENABLED')
            ),
            ksusecqd, ksuseclid
            FROM x$ksuse
            WHERE BITAND (ksspaflg, 1) != 0 AND BITAND (ksuseflg, 1) != 0
GV$SESSION (Oracle10gR1 Version) Lists session
information for
each current
session. 
SELECT s.inst_id, s.addr, s.indx, s.ksuseser, s.ksuudses, s.ksusepro,
            s.ksuudlui, s.ksuudlna, s.ksuudoct, s.ksusesow,
            DECODE (s.ksusetrn, HEXTORAW ('00'), NULL, s.ksusetrn),
            DECODE (s.ksqpswat, HEXTORAW ('00'), NULL, s.ksqpswat),
            DECODE (BITAND (s.ksuseidl, 11),
            1, 'ACTIVE',
            0, DECODE (BITAND (s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
            2, 'SNIPED',
            3, 'SNIPED',
            'KILLED'
            ),
            DECODE (s.ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'),
            s.ksuudsid, s.ksuudsna, s.ksuseunm, s.ksusepid, s.ksusemnm, s.ksusetid,
            s.ksusepnm,
            DECODE (BITAND (s.ksuseflg, 19),
            17, 'BACKGROUND',
            1, 'USER',
            2, 'RECURSIVE',
            '?'
            ),
            s.ksusesql, s.ksusesqh, s.ksusesqi,
            DECODE (s.ksusesch, 65535, TO_NUMBER (NULL), s.ksusesch), s.ksusepsq,
            s.ksusepha, s.ksusepsi,
            DECODE (s.ksusepch, 65535, TO_NUMBER (NULL), s.ksusepch), s.ksuseapp,
            s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj,
            s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,
            DECODE (BITAND (s.ksusepxopt, 12), 0, 'NO', 'YES'),
            DECODE (s.ksuseft,
            2, 'SESSION',
            4, 'SELECT',
            8, 'TRANSACTIONAL',
            'NONE'
            ),
            DECODE (s.ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
            DECODE (s.ksusefs, 1, 'YES', 'NO'), s.ksusegrp,
            DECODE (BITAND (s.ksusepxopt, 4),
            4, 'ENABLED',
            DECODE (BITAND (s.ksusepxopt, 8), 8, 'FORCED', 'DISABLED')
            ),
            DECODE (BITAND (s.ksusepxopt, 2),
            2, 'FORCED',
            DECODE (BITAND (s.ksusepxopt, 1), 1, 'DISABLED', 'ENABLED')
            ),
            DECODE (BITAND (s.ksusepxopt, 32),
            32, 'FORCED',
            DECODE (BITAND (s.ksusepxopt, 16), 16, 'DISABLED', 'ENABLED')
            ),
            s.ksusecqd, s.ksuseclid,
            DECODE (s.ksuseblocker,
            4294967295, 'UNKNOWN',
            4294967294, 'GLOBAL',
            4294967293, 'UNKNOWN',
            4294967292, 'NO HOLDER',
            4294967291, 'NOT IN WAIT',
            'VALID'
            ),
            DECODE (s.ksuseblocker,
            4294967295, TO_NUMBER (NULL),
            4294967294, TO_NUMBER (NULL),
            4294967293, TO_NUMBER (NULL),
            4294967292, TO_NUMBER (NULL),
            4294967291, TO_NUMBER (NULL),
            s.ksuseblocker
            ),
            s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1, s.ksusep1, s.ksusep1r,
            e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r,
            e.ksledclassid, e.ksledclass#, e.ksledclass,
            DECODE (s.ksusetim,
            0, 0,
            -1, -1,
            -2, -2,
            DECODE (ROUND (s.ksusetim / 10000),
            0, -1,
            ROUND (s.ksusetim / 10000)
            )
            ),
            s.ksusewtm,
            DECODE (s.ksusetim,
            0, 'WAITING',
            -2, 'WAITED UNKNOWN TIME',
            -1, 'WAITED SHORT TIME',
            DECODE (ROUND (s.ksusetim / 10000),
            0, 'WAITED SHORT TIME',
            'WAITED KNOWN TIME'
            )
            ),
            s.ksusesvc
            FROM x$ksuse s, x$ksled e
            WHERE BITAND (s.ksspaflg, 1) != 0
            AND BITAND (s.ksuseflg, 1) != 0
            AND s.ksuseopc = e.indx
GV$SESSION (Oracle10gR2 Version) Lists session
information for
each current
session. 
SELECT s.inst_id, s.addr, s.indx, s.ksuseser, s.ksuudses, s.ksusepro,
            s.ksuudlui, s.ksuudlna, s.ksuudoct, s.ksusesow,
            DECODE (s.ksusetrn, HEXTORAW ('00'), NULL, s.ksusetrn),
            DECODE (s.ksqpswat, HEXTORAW ('00'), NULL, s.ksqpswat),
            DECODE (BITAND (s.ksuseidl, 11),
            1, 'ACTIVE',
            0, DECODE (BITAND (s.ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),
            2, 'SNIPED',
            3, 'SNIPED',
            'KILLED'
            ),
            DECODE (s.ksspatyp, 1, 'DEDICATED', 2, 'SHARED', 3, 'PSEUDO', 'NONE'),
            s.ksuudsid, s.ksuudsna, s.ksuseunm, s.ksusepid, s.ksusemnm, s.ksusetid,
            s.ksusepnm,
            DECODE (BITAND (s.ksuseflg, 19),
            17, 'BACKGROUND',
            1, 'USER',
            2, 'RECURSIVE',
            '?'
            ),
            s.ksusesql, s.ksusesqh, s.ksusesqi,
            DECODE (s.ksusesch, 65535, TO_NUMBER (NULL), s.ksusesch), s.ksusepsq,
            s.ksusepha, s.ksusepsi,
            DECODE (s.ksusepch, 65535, TO_NUMBER (NULL), s.ksusepch), s.ksuseapp,
            s.ksuseaph, s.ksuseact, s.ksuseach, s.ksusecli, s.ksusefix, s.ksuseobj,
            s.ksusefil, s.ksuseblk, s.ksuseslt, s.ksuseltm, s.ksusectm,
            DECODE (BITAND (s.ksusepxopt, 12), 0, 'NO', 'YES'),
            DECODE (s.ksuseft,
            2, 'SESSION',
            4, 'SELECT',
            8, 'TRANSACTIONAL',
            'NONE'
            ),
            DECODE (s.ksusefm, 1, 'BASIC', 2, 'PRECONNECT', 4, 'PREPARSE', 'NONE'),
            DECODE (s.ksusefs, 1, 'YES', 'NO'), s.ksusegrp,
            DECODE (BITAND (s.ksusepxopt, 4),
            4, 'ENABLED',
            DECODE (BITAND (s.ksusepxopt, 8), 8, 'FORCED', 'DISABLED')
            ),
            DECODE (BITAND (s.ksusepxopt, 2),
            2, 'FORCED',
            DECODE (BITAND (s.ksusepxopt, 1), 1, 'DISABLED', 'ENABLED')
            ),
            DECODE (BITAND (s.ksusepxopt, 32),
            32, 'FORCED',
            DECODE (BITAND (s.ksusepxopt, 16), 16, 'DISABLED', 'ENABLED')
            ),
            s.ksusecqd, s.ksuseclid,
            DECODE (s.ksuseblocker,
            4294967295, 'UNKNOWN',
            4294967294, 'UNKNOWN',
            4294967293, 'UNKNOWN',
            4294967292, 'NO HOLDER',
            4294967291, 'NOT IN WAIT',
            'VALID'
            ),
            DECODE (s.ksuseblocker,
            4294967295, TO_NUMBER (NULL),
            4294967294, TO_NUMBER (NULL),
            4294967293, TO_NUMBER (NULL),
            4294967292, TO_NUMBER (NULL),
            4294967291, TO_NUMBER (NULL),
            BITAND (s.ksuseblocker, 2147418112) / 65536
            ),
            DECODE (s.ksuseblocker,
            4294967295, TO_NUMBER (NULL),
            4294967294, TO_NUMBER (NULL),
            4294967293, TO_NUMBER (NULL),
            4294967292, TO_NUMBER (NULL),
            4294967291, TO_NUMBER (NULL),
            BITAND (s.ksuseblocker, 65535)
            ),
            s.ksuseseq, s.ksuseopc, e.kslednam, e.ksledp1, s.ksusep1, s.ksusep1r,
            e.ksledp2, s.ksusep2, s.ksusep2r, e.ksledp3, s.ksusep3, s.ksusep3r,
            e.ksledclassid, e.ksledclass#, e.ksledclass,
            DECODE (s.ksusetim,
            0, 0,
            -1, -1,
            -2, -2,
            DECODE (ROUND (s.ksusetim / 10000),
            0, -1,
            ROUND (s.ksusetim / 10000)
            )
            ),
            s.ksusewtm,
            DECODE (s.ksusetim,
            0, 'WAITING',
            -2, 'WAITED UNKNOWN TIME',
            -1, 'WAITED SHORT TIME',
            DECODE (ROUND (s.ksusetim / 10000),
            0, 'WAITED SHORT TIME',
            'WAITED KNOWN TIME'
            )
            ),
            s.ksusesvc,
            DECODE (BITAND (s.ksuseflg2, 32), 32, 'ENABLED', 'DISABLED'),
            DECODE (BITAND (s.ksuseflg2, 64), 64, 'TRUE', 'FALSE'),
            DECODE (BITAND (s.ksuseflg2, 128), 128, 'TRUE', 'FALSE')
            FROM x$ksuse s, x$ksled e
            WHERE BITAND (s.ksspaflg, 1) != 0
            AND BITAND (s.ksuseflg, 1) != 0
            AND s.ksuseopc = e.indx
GV$SQL Lists statistics
on shared SQL
area without the
GROUP BY clause
and contains one
row for each child
of the original
SQL text entered. 
SELECT inst_id, kglnaobj,
            kglobhs0
            + kglobhs1
            + kglobhs2
            + kglobhs3
            + kglobhs4
            + kglobhs5
            + kglobhs6
            + kglobt16,
            kglobt08 + kglobt11, kglobt10, kglobt01, DECODE (kglobhs6, 0, 0, 1),
            DECODE (kglhdlmd, 0, 0, 1), kglhdlkc, kglobt04, kglobt05, kglobpc6,
            kglhdldc, SUBSTR (TO_CHAR (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
            kglhdivc, kglobt12, kglobt13, kglobt14, kglobt15, kglobt02,
            DECODE (kglobt32,
            0, 'NONE',
            1, 'ALL_ROWS',
            2, 'FIRST_ROWS',
            3, 'RULE',
            4, 'CHOOSE',
            'UNKNOWN'
            ),
            kglobtn0, kglobt17, kglobt18, kglhdkmk, kglhdpar, kglobtp0, kglnahsh,
            kglobt30, kglobt09, kglobts0, kglobt19, kglobts1, kglobt20, kglobt21,
            kglobts2, kglobt06, kglobt07, kglobt28, kglhdadr, kglobt29,
            DECODE (BITAND (kglobt00, 64), 64, 'Y', 'N'),
            DECODE (kglobsta,
            1, 'VALID',
            2, 'VALID_AUTH_ERROR',
            3, 'VALID_COMPILE_ERROR',
            4, 'VALID_UNAUTH',
            5, 'INVALID_UNAUTH',
            6, 'INVALID'
            ),
            kglobt31, SUBSTR (TO_CHAR (kglobtt0, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
            DECODE (kglobt33, 1, 'Y', 'N'), kglhdclt
            FROM x$kglcursor
            WHERE kglhdadr != kglhdpar AND kglobt02 != 0
GV$SQLAREA Lists statistics
on shared SQL
area and contains
one row per
SQL string. It
provides statistics
on SQL statements
that are in
memory, parsed,
and ready
for execution. . 
SELECT   inst_id, kglnaobj,
            SUM (  kglobhs0
            + kglobhs1
            + kglobhs2
            + kglobhs3
            + kglobhs4
            + kglobhs5
            + kglobhs6
            ),
            SUM (kglobt08 + kglobt11), SUM (kglobt10), SUM (kglobt01),
            COUNT (*) - 1, SUM (DECODE (kglobhs6, 0, 0, 1)),
            DECODE (SUM (DECODE (kglhdlmd, 0, 0, 1)),
            0, 0,
            SUM (DECODE (kglhdlmd, 0, 0, 1)) - 1
            ),
            SUM (kglhdlkc) / 2, SUM (kglobt04), SUM (kglobt05), SUM (kglobpc6),
            SUM (kglhdldc) - 1,
            SUBSTR (TO_CHAR (kglnatim, 'YYYY-MM-DD/HH24:MI:SS'), 1, 19),
            SUM (kglhdivc), SUM (kglobt12), SUM (kglobt13), SUM (kglobt14),
            SUM (kglobt15), SUM (DECODE (kglobt09, 0, kglobt02, 0)),
            DECODE (COUNT (*) - 1,
            1, DECODE (SUM (DECODE (kglobt09, 0, kglobt32, 0)),
            0, 'NONE',
            1, 'ALL_ROWS',
            2, 'FIRST_ROWS',
            3, 'RULE',
            4, 'CHOOSE',
            'UNKNOWN'
            ),
            'MULTIPLE CHILDREN PRESENT'
            ),
            SUM (DECODE (kglobt09, 0, kglobt17, 0)),
            SUM (DECODE (kglobt09, 0, kglobt18, 0)),
            DECODE (SUM (DECODE (kglhdkmk, 0, 0, 1)),
            0, 0,
            SUM (DECODE (kglhdkmk, 0, 0, 1)) - 1
            ),
            kglhdpar, kglnahsh, kglobts0, kglobt19, kglobts1, kglobt20,
            SUM (kglobt21), SUM (kglobt06), SUM (kglobt07),
            DECODE (kglobt33, 1, 'Y', 'N'), kglhdclt
            FROM x$kglcursor
            GROUP BY inst_id,
            kglnaobj,
            kglhdpar,
            kglnahsh,
            kglnatim,
            kglobts0,
            kglobt19,
            kglobts1,
            kglobt20,
            DECODE (kglobt33, 1, 'Y', 'N'),
            kglhdclt
            HAVING SUM (DECODE (kglobt09, 0, kglobt02, 0)) != 0

历史上的今天...
    >> 2009-09-15文章:
    >> 2007-09-15文章:
    >> 2006-09-15文章:
           今天买入的几本图书
           我的Blog Rss订阅用户分布

By eygle on 2005-09-15 09:27 | Comments (4) | FAQ | 435 |

4 Comments

lunar | January 19, 2005 9:59 AM

绝大部分GV$的信息就是V$的信息加上一个instance id，也就是说，GV$主要用户OPS或者RAC系统，呵呵，两个反映的含义大致相同。

要查询某个GV$的定义可以参考：

http://blog.csdn.net/lunar2000/archive/2004/11/24/193445.aspx

ZALBB | March 1, 2005 3:40 PM

这句话说反了：
绝大部分GV$的信息就是V$的信息加上一个instance id，
应该是：
绝大部分V$的信息就是GV$的信息加上一个instance id，

qxq_qiang | April 28, 2007 6:06 PM

我做的oracle数据库管理工具(dbtool)里面有
oracle数据库字典说明
oracle系统参数说明
oracle函数使用说明
oracle错误信息说明
oracle语法说明
sql21天精通
并且可以导出用户的数据字典
下载地址
http://www.tomdisk.com/?qxq_qiang
讨论群 12067690 oracle 第三方工具

qxq_qiang | June 24, 2008 10:30 PM

ORACLE数据库管理工具
http://www.91files.com/?TK0SMLAEX7QGK14KLHUL

自己一直以来用PLSQL Developer,但有时候用起来还不是很方便,决定给自己量身订做一个,不仅可以管理数据库，而且还是一个学习oracle的好工具主要特点有:

1.本软件的框架风格布局美观大方,对象浏览全面直观,控制操作简单方便.

2.它有个文本编辑器,可以编辑脚本,查看文本文件,保存已编辑的代码,自动注销代码.

3.它可以执行任何SQL语句,包括select,update,delete,insert,create,alter等常用语句,查询速度非常快,并且有快捷键,记录执行代码,显示执行时间,显示查询或更新记录数,并且可以打开多个SQL语句执行窗口,不用退出系统连接其他用户,查询其他用户的表.对查询出来的数据进行再过滤,排序,并且可以动态产生查询语句,直接把查询数据导出为语句,网页代码,txt文件,dbf文件,excel文件,并且可以直接打印数据,保存编辑的语句,以备日后使用,选择备份语句执行等.

4.对所有的对象操作,都可以看见源代码,可以熟悉数据库操作语句.

5.表的操作功能强大,可以通过界面创建一个表,很方便的创建索引,主键,外键.重新命名表名列名.删除表列.删除表中数据,释放表空间,显示表的授权情况,显示表的创建,索引,授权等语句,可以把文本文件,dbf文件,excel文件导入表中.可以直接修改表中的数据,导出数据为各种格式等等.

6.对于过程可以直接修改,执行,并且有错误提示,测试跟踪过程,并且可以看到参数的类型,是输出参数还是输入参数,显示 dbms_output.put_line()跟踪信息,可以追加跟踪信息,不让前一次的信息丢失,以备对照查询.并可以快速注释代码.

7.生成各种文档,如表的数据字典,导出为word文档,excel表格,txt代码文件,所有的过程,视图,触发器,同义词等.

8.查看进程,锁表情况,断开进程,死锁会话.跟踪进程的执行语句.

9.显示所有系统参数的值,并可以修改,有中文说明,oracle数据字典说明,函数说明,并且可以录入自己总结的备忘资料,以备查看,还有帮助就是一本学习oracle的书,一步一步怎么操作,对各种对象的说明,链接其他参考资料,sql语句,oracle报错信息及语法.

10.对注册表维护,查看统一tnsnames文件,查看dbf文件的结构和数据,对blob类型的大文件进行上传和下载,如果是图片,并且可以预览,方便的导出导入功能.

看了以上这十大特点,还有什么理由让你不用它呢.可以这样认为本系统：

对初学者来说，是一个很好的数据库学习工具

对开发者来说，是一个很好的数据库开发工具

对维护者来说，是一个很好的数据库管理工具

CopyRight © 2004~2020 云和恩墨，成就未来！, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话：400-600-8755 业务咨询：010-59007017-7040 or 7037 业务合作: marketing@enmotech.com