Allen 2010-03-15 9846 1 0 0 0

 

本文为TTT学习笔记,首先介绍数据字典及查看方法,然后分类总结各类数据字典的表和视图。然后列出一些附例。
 
数据字典系统表,保存在system表空间中。
由表和视图组成,由服务器在安装数据库时自动创建,用户不可以直接修改数据库字典,在执行DDL语句时,oracle会自动修改。
记录一些表和视图(只读的),新建的表不要和这空间建在一起(9i以前的版本新用户建的表默认表空间为system,注意修改)
 
--查询数据字典:
select * from dictionary
 
--数据字典导出方法:
conn / as sysdba
spool on 
spool c:dic.txt
select * from dictionary
spool off
 
主要四部分:
1,内部RDBMS表:x$……
2,数据字典表:……$
3,动态性能视图:gv$……,v$……
4,数据字典视图:user_……,all_……,dba_……
 
数据库启动时,动态创建x$,在X$基础上创建GV$,在GV$基础上创建V$X$表-->GV$(视图)--->V$(视图)
 
+++
一,内部RDBMS表 x$……,例如:x$kvit,x$bh,x$ksmsp,x$ksppi和x$ksppcv
核心部分,用于跟踪内部数据库信息,维持DB的正常运行。
是加密命名的,不允许sysdba以外的用户直接访问,显示授权不被允许。最好不要修改.
x$kvit=Kernel Layer Performance Layer V Information tables Transitory Instance parameter
数据库启动时,动态创建x$……
 
+++
二,数据字典表 ……$,如tab$,obj$,ts$……
--用来存储表、索引、约束以及其他数据库结构的信息。
--创建数据库时通过脚本sql.bsq来创建,脚本:$oracle_home/rdbms/admin/sql.bsq
 
+++
三,动态性能视图 gv$……,v$……,如V$parameter
--记录了DB运行时信息和统计数据,大部分动态性能视图被实时更新以反映DB当前状态。
--数据库创建时建立的。
--只有sysdba可以直接访问。
--查看表v$fixed_view_definition(***),可以查看GV$和V$视图的创建语句。(oracle提供一些特殊视图,用来记录其他视图的创建方式,v$fixed_view_definition就是其中之一)
--select view_definition from v$fixed_view_definition where view_name=^V$FIXED_TABLE^;
 
--gv$……=Global V$,在X$……基础上创建,是为了满足OPS环境(多个实例)的需要面产生的,可以返回多个实例的信息。
V$……,在GV$……基础上创建,只返回当前实例的信息。定义语句都带有:where inst_id =USERENV(^Instance^)
 
--GV$和V$之后,oracle建立了GV_$和V_$视图,又为这些视图建立了公用同义词。由脚本catalog.sql实现的,脚本 :$oracle_home/rdbms/admin/catalog.sql
create or replace view v_$process as select * from v$process;
create or replace public synonym v$process for v_$process;
 
create or replace view gv_$process as select * from gv$process;
create or replace public synonym gv$process for gv_$process;
 
-->可以看出:
V$(视图)-->V_$(视图)-->V$(公用同义词)
GV$(视图)-->GV_$(视图)-->GV$(公用同义词)
这样做的目的:通过V_$和GV_$,oracle把V$视图和GV视图和普通用户隔离开来。(oracle允许V_$视图权限可以授权给其他用户,但不允许任何对于V$视图的直接授权。)
所以,在非Sys用户下,我们访问的都是同义词,而不是V$视图或GV视图。
 
--oracle访问数据顺序:view-->同义词。
 
+++
四,数据库字典视图
--是在X$表和数据字典表之上建立的视图。
--创建数据库时由脚本catalog.sql创建。脚本 :$oracle_home/rdbms/admin/catalog.sql
--按前缀不同,作用范围的分为三类:
1、以user开头的数据字典: 包含当前用户所拥有的相关对象信息。--能够查到对象的所有者是当前用户的所有对象
select table_name from user_tables;  (scott) 5
2、以all开头的数据字典:  包含当前用户有权限访问的所有对象的信息。--能够查到所有当前用户有权限访问的对象
select table_name from all_tables;  (scott) 96
3、以dba开头的数据字典:  包含数据库所有相关对象的信息。--只能是有dba权限的用户查询,能查到数据库中所有对象
select table_name from dba_tables (sys system)
 
+++
附:
比较user,all,dba数据字典视图
各数据字典表数量比较
表Dictionary与V$fixed_table比较
通过V$parameter视图来追踪一下数据库的架构
oracle如何通过同义词定位对象(10046事件)
 
+++
比较user,all,dba数据字典视图
---
可以查看脚本catalog.sql中的定义:
 
+++
--USER_TABLES
  --可以看到限制条件:where o.owner# = userenv(^SCHEMAID^)
 
+++
--USER_ALL_TABLES
  --扩展了关于用户有权限访问的对象信息,所以user_tables是all_tables的子集。
 
+++
--DBA_tables
  --返回数据库中所有表的信息
 
+++
各数据字典表数量比较:可以从V$fixed_table中查询。
(以下为oracle10g单机数据库,定制DB)
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
 
SQL> select count(*) from v$fixed_table where name like ^X$%^;
 
  COUNT(*)
----------
       613
 
SQL> select count(*) from v$fixed_table where name like ^GV$%^;
 
  COUNT(*)
----------
       372
 
SQL> select count(*) from v$fixed_table where name like ^V$%^;
 
  COUNT(*)
----------
       396
 
--这里:X%+GV$+V$=613+372+396=1381
 
SQL> select count(*) from v$fixed_table;
 
  COUNT(*)
----------
      1383
 
--一般情况下,这里会=X%+GV$+V$,但现在不等,我们看一下有什么其他的表:
 
SQL> select substr(name,1,2) from v$fixed_table group by substr(name,1,2);
 
SUBS
----
X$
V$
O$
GO
GV
 
--可以看出,多出两个前缀分别是O$,GO的两类表,应该每种只有一个:
 
SQL> select name from v$fixed_table where name like ^O$%^;
 
NAME
------------------------------
O$SQL_BIND_CAPTURE
 
SQL> select name from v$fixed_table where name like ^GO%^;
 
NAME
------------------------------
GO$SQL_BIND_CAPTURE
 
SQL>
 
--此外,一般情况下GV$=V$,但现在GV$=396,V$=372个,我们看一下GV$都多出什么表了:
 
SQL> select max(a.name_max) from (select length(name) as name_max from v$fixed_t
able where name like ^GV$%^ or name like ^V$%^) a;
 
MAX(A.NAME_MAX)
---------------
             30
 
select a.gv_name,b.v_name from
(select substr(name,4,30) GV_name from v$fixed_table where substr(name,1,3)=^GV$^) a,
(select substr(name,3,30) V_name from v$fixed_table where substr(name,1,2)=^V$^) b
where a.GV_name=b.V_name(+)
  and b.v_name is null
???
 
+++
表Dictionary与V$fixed_table比较(没有什么用处~~)
---
 
SQL> select count(*) from dictionary;
 
  COUNT(*)
----------
      1870
 
SQL> select count(*) from v$fixed_table;
 
  COUNT(*)
----------
      1383
 
--两表综合比较:
Dic有,Fixed无--1112
Dic无,Fixed有--625
 
dictionary中GV$-368 V$-398
V$fixed_table中GV$-372 V$-396
 
--两表中GV$比较:
Dic有,Fixed无
GV$SQL_BIND_CAPTURE
GV$AQ
GV$TEMPSEG_USAGE
 
Dic无,Fixed有
GV$_LOCK1
GV$_RESUMABLE2
GV$RMAN_STATUS_CURRENT
GV$_SEQUENCES
GV$DB_TRANSPORTABLE_PLATFORM
GV$RMAN_ENCRYPTION_ALGORITHMS
GV$TRANSPORTABLE_PLATFORM
 
--两表中V$表比较
Dic有,Fixed无
V$AQ
V$TEMPSEG_USAGE
V$SQL_BIND_CAPTURE
V$BACKUP_FILES
V$ROLLNAME
 
Dic无,Fixed有
V$_LOCK1
V$RMAN_ENCRYPTION_ALGORITHMS
V$_SEQUENCES
 
+++
通过V$parameter视图来追踪一下数据库的架构
1,V$parameter的结构:
SQL> select view_definition from v$fixed_view_definition where view_name=^V$PARA
METER^;
 
VIEW_DEFINITION
--------------------------------------------------------------------------------
 
select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE
, ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECAT
 
ED, DESCRIPTION, UPDATE_COMMENT, HASH  from GV$PARAMETER where inst_id = USERENV
 
(^Instance^)
 
--可以看出V$parameter是由GV$parameter创建的
 
SQL> select view_definition from v$fixed_view_definition where view_name=^GV$PAR
AMETER^;
 
VIEW_DEFINITION
--------------------------------------------------------------------------------
 
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,  decode
 
(bitand(ksppiflg/256,1),1,^TRUE^,^FALSE^),  decode(bitand(ksppiflg/65536,3),1,^I
 
MMEDIATE^,2,^DEFERRED^,                                  3,^IMMEDIATE^,^FALSE^),
 
  decode(bitand(ksppiflg,4),4,^FALSE^,                                     decod
 
e(bitand(ksppiflg/65536,3), 0, ^FALSE^, ^TRUE^)),     decode(bitand(ksppstvf,7),
 
1,^MODIFIED^,4,^SYSTEM_MOD^,^FALSE^),  decode(bitand(ksppstvf,2),2,^TRUE^,^FALSE
 
^),  decode(bitand(ksppilrmflg/64, 1), 1, ^TRUE^, ^FALSE^),  ksppdesc, ksppstcmn
 
t, ksppihash  from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and  ((translat
 
e(ksppinm,^_^,^#^) not like ^##%^) and    ((translate(ksppinm,^_^,^#^) not like
^#%^)      or (ksppstdf = ^FALSE^) or      (bitand(ksppstvf,5) > 0)))
 
--可以看出GV$parameter是由x$ksppi和x$ksppcv两个x$创建的
--x$ksppi和x$ksppcv基本上包含所有数据库参数,GV$parameter展现的是不包含“_”开头的参数
--“_”开头的参数为隐含参数,不建议修改,也少有人知,但很多隐含参数因为功能强大而经常使用,并不段的被探索和研究。
 
+++
oracle如何通过同义词定位对象(10046事件)
 
如果愿意的话,我们可以进一步来进行追溯,使用 10046事件,我们可以看到更多的东西。 
 
通过 10046事件跟踪查询: 
[[email protected] udump]$ sqlplus eygle/eygle 
 
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 13 18:29:22 2005 
 
Copyright (c) 1982, 2002, Oracle Corporation.    All rights reserved. 
 
 
Connected to: 
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production 
With the Partitioning option 
JServer Release 9.2.0.4.0 - Production 
 
SQL> alter session set events ^10046 trace name context forever,level 12^; 
 
Session altered. 
 
SQL> select count(*) from v$parameter;
 
  COUNT(*) 
---------- 
       262 
 
SQL> exit 
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production 
With the Partitioning option 
JServer Release 9.2.0.4.0 - Production 
 
--查看生成的跟踪文件
 
10046 事件的使用请参考: 
http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
 
Ok,在这里我们不要使用 tkprof格式化,因为 tkprof可能会隐去重要信息(本文仅摘取几段重要跟踪信息,你完全可以通过实验获得相同的输出): 
 
第一段重要代码是: 
PARSING IN CURSOR #2 len=198 dep=1 uid=0 oct=3 lid=0 tim=1092440257023120 hv=2703824309 ad=^567681f0^ 
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and 
namespace=:3 and remoteowner is null and linkname is null and subname is null 
END OF STMT 
PARSE #2:c=0,e=1601,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1092440257023088 
BINDS #2: 
  bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 
   bfp=b701cf24 bln=22 avl=02 flg=05 
   value=25 
  bind 1: dty=1 mxl=32(11) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=32 offset=0 
   bfp=b701c7b4 bln=32 avl=11 flg=05 
   value="V$PARAMETER" 
  bind 2: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 
   bfp=b701c790 bln=24 avl=02 flg=05 
   value=1 
 
Oracle 根据三个传入参数 owner#=25,name=V$PARAMETER,namespace=1,来判断对象类型,按照表、视图优
先规则来定位判断,对于本例这个查询是不会有结果的。 
 
接下来 Oracle 继续判断,那么此时需要验证同一词了: 
 
PARSING IN CURSOR #4 len=46 dep=1 uid=0 oct=3 lid=0 tim=1092440257028409 hv=3378994511 ad=^576eb040^ 
select node,owner,name from syn$ where obj#=:1 
END OF STMT 
PARSE #4:c=0,e=1278,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1092440257028379                                                           
BINDS #4: 
  bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0 
   bfp=b701b3cc bln=22 avl=03 flg=05 
   value=841 
 
传入绑定变量值是 841,我们看看 841 是什么: 
SQL> select object_name,object_id,object_type from dba_objects where object_id=841; 
 
OBJECT_NAME                     OBJECT_ID OBJECT_TYPE 
------------------------------                 ----------   ------------------ 
V$PARAMETER                           841 SYNONYM 
 
841 正是这个同义词,我们再继续看这个递归 SQL 的作用: 
 
SQL> select node,owner,name from syn$ where obj#=841; 
 
NODE     OWNER                          NAME 
-------- ------------------------------ ------------------------------ 
         SYS                            V_$PARAMETER 
 
 
原来这个 SQL 获得的是同义词的底层对象,这里得到了 V_$PARAMETER。 
 
我们继续向下看: 
PARSING IN CURSOR #8 len=37 dep=1 uid=0 oct=3 lid=0 tim=1092440257074273 hv=3468666020 ad=^576db210^ 
select text from view$ where rowid=:1 
END OF STMT 
PARSE #8:c=0,e=1214,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1092440257074242 
BINDS #8: 
  bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=16 offset=0 
   bfp=b7018770 bln=16 avl=16 flg=05 
   value=000001CD.0013.0001 
EXEC #8:c=0,e=972,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1092440257075602 
 
 
注意这里,Oracle 执行查询访问 view$视图,获得视图定义文本,我们看一下这里访问的是什么对象,绑定变
量传入的 rowid 值为000001CD.0013.0001,注意这是个受限 rowid,查询时需要转换一下处理: 
 
SQL> select obj# from view$ where dbms_rowid.rowid_to_restricted(rowid,0) = ^000001CD.0013.0001^; 
 
      OBJ# 
----------
       840 
 
 
SQL> select object_name,object_type from dba_objects where object_id=840; 
 
OBJECT_NAME                    OBJECT_TYPE 
------------------------------ ------------------ 
V_$PARAMETER                   VIEW 
 
 
这里 Oracle访问的正是 V_$PARAMETER 视图的定义方式。执行查询可以得到: 
 
select text from view$ where obj#=840; 
 
TEXT 
-------------------------------------------------------------------------------- 
select 
"NUM","NAME","TYPE","VALUE","ISDEFAULT","ISSES_MODIFIABLE","ISSYS_MODIFIABLE","ISMODIFIED","ISADJUSTE
D","DESCRIPTION","UPDATE_COMMENT" from v$parameter 
 
 
至此就完成了查询中的回溯及定位,当然,实际过程中 Oracle 后台的递归操作比这还要复杂的多,感兴趣的
朋友可以按照文中的方法测试研究一下,文中不再赘述。 
 
--sql语句中oracle对于对象名的解析顺序:
--用户表/视图-->私有同义词-->公共同义词-->返回错误ora-00942
 
参考一:盖国强<深入Oracle--DBA入门、进阶与诊断案例>之<第三章 数据字典>
参考二:张云河老师课堂笔记。
 

 


【版权声明】
本文为原创,遵循CC 4.0 BY-SA版权协议!转载请附上原文出处链接及本声明。
原文链接:https://tdlib.com/am.php?t=zICyijVKNs99
Tag: Oracle Oracle基础 Oracle笔记 TTTBLOG
我也要发一个   ·   返回首页     ·   返回[Oracle]   ·    前一个  ·   下一个
评论
Allen#1.  Allen at 2011-02-21 12:31:53(Normal)  分享链接
| 1485 | seo优化 | 2011-02-21 12:31:53 | [email protected] | http://www.szwangka.com/ |
-----
好多,看的眼睛都花了。建议博主改下排版。。[REVERT=taoether 于 2011-2-22 8:47:52 回复]呵呵,是有点乱,但好些都是从命令行复制的…… 慢慢看还是可以的吧~~[/REVERT]
顶部     1/1 
未登录,
请先 [ 注册 ] or [ 登录 ]
(一分钟即可完成注册!)
返回首页     ·   返回[Oracle]   ·   返回顶部