Oracle

Allen 2010-03-15 20:58:24 35734 1 0 0 0

網站名稱：学习笔记：oracle数据字典详解

網站地址：

[SEO信息] [Alexa信息]

-->>直達網站

本文为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

----

--可以看出，多出两个前缀分别是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)

---------------

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事件跟踪查询：

[oracle@jumper udump]$ sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 13 18:29:22 2005

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] · 前一個 · 下一個

評論

1評論, 我也要評論！ ↓

#1. Allen at 2011-02-21 12:31:53(N) 鏈接地址

頂部 1/1

Z-Blog上传错误：Request 对象错误 ^ASP 0

2010-03-15

Delphi异常处理try except语句和try fin

2010-03-15

关于庆余年最后一章的疑问

2010-03-15

为什么我的博客招来这么多广告……

2010-03-15

ORA-28040: 没有匹配的验证协议

2010-03-15

转：“中国影响论”初露端倪

2010-03-15

Oracle释放过度使用的Undo表空间

2010-03-15

互联网目睹之怪现状

2010-03-15

Oracle10g CPU核数限制  -->
歡迎評論
未登錄，
請先 [ 註冊 ] or [ 登錄 ]
(一分鍾即可完成註冊!)

返回首頁 · 返回[Oracle] · 返回頂部

萬維笑話

一堆大学生去往大山深处，欲体验最地道的原生态。他们到达了一个偏僻的山村，说明来意，村长热情的招待了他们。席间，村长指着一盆肉说：同学们，赶快尝尝这狗肉，我们村的狗都是最原生态的，全部是吃屎长大的！

網站名稱： 学习笔记：oracle数据字典详解

網站地址：

[SEO信息] [Alexa信息]

-->>直達網站

Z-Blog上传错误：Request 对象错误 ^ASP 0

Delphi异常处理try except语句和try fin

关于庆余年最后一章的疑问

为什么我的博客招来这么多广告……

ORA-28040: 没有匹配的验证协议

转：“中国影响论”初露端倪

Oracle释放过度使用的Undo表空间

互联网目睹之怪现状

Oracle10g CPU核数限制 --> 歡迎評論 未登錄，請先 [ 註冊 ] or [ 登錄 ](一分鍾即可完成註冊!) 返回首頁 · 返回[Oracle] · 返回頂部

萬維笑話

網站名稱：学习笔记：oracle数据字典详解

Oracle10g CPU核数限制 -->
歡迎評論
未登錄，
請先 [ 註冊 ] or [ 登錄 ]
(一分鍾即可完成註冊!)

返回首頁 · 返回[Oracle] · 返回頂部