Oracle 数据字典及注释查询

最新推荐文章于 2022-11-04 16:45:33 发布

cijinli4767

最新推荐文章于 2022-11-04 16:45:33 发布

阅读量409

点赞数

文章标签：数据库

Oracle通过数据字典来管理和展现数据库信息，数据字典通常存储数据库的元数据，是数据库的数据库，其中存储的信息至关重要。正确理解这部分内容有助于提高大家对Oracle数据库的认知。

数据字典有四类

1内部RDBMS（X$）表

X$表是Oracle数据库的核心部分，这些表用于跟踪内部数据库信息，维持数据库的正常运行，X$表是加密命名的，而且Oracle不做文档说明，这部分知识是ORACLE公司的技术机密，ORACLE公司通过这些X$建立起其它大量视图提供用户查询管理数据库之用。
内部X$表可以用v$fixed_table来查询

2数据字典表

数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据。为了方便的区别这些表，这些表的名字都是用"$"结尾，这些表属于SYS用户。

数据字典表由$ORACLE_HOME/rdbms/admin/sql.bsq 脚本创建，这个脚本里又调用了其他的脚本来创建这些数据字典表。在那些创建脚本里有基表的创建SQL。

Oracle 对数据字典表的说明：

These underlying tables store information about the database. Only Oracle Database should write to and read these tables. Users rarely access the base tables directly because they are normalized and most data is stored in a cryptic format.

这些数据字典表，只有Oracle 能够进行读写。

SYS用户下的这些数据字典表，存放在system 表空间下面，表名都用"$"结尾，为了便于用户对数据字典表的查询， Oracle对这些数据字典都分别建立了用户视图，这样即容易记住，还隐藏了数据字典表表之间的关系，Oracle针对这些对象的范围，分别把视图命名为DBA_XXXX, ALL_XXXX和USER_XXXX。

查询数据库中的字典表

select * from dba_tables where owner='SYS' and tablespace_name='SYSTEM'

注释查询:

官方文档没有注释,查询注释可以通过查询创建脚本来获得

[oracle@oracledb ~]$ cd $ORACLE_HOME/rdbms/admin/

[oracle@oracledb admin]$ ls -l *.bsq

-rw-r--r-- 1 oracle oinstall 25905 Mar 19 2009 daw.bsq

-rw-r--r-- 1 oracle oinstall 91730 Jul 22 2011 dcore.bsq

-rw-r--r-- 1 oracle oinstall 2832 Oct 23 2006 ddm.bsq

-rw-r--r-- 1 oracle oinstall 674 Jul 14 2008 ddst.bsq

-rw-r--r-- 1 oracle oinstall 17993 Feb 23 2010 denv.bsq

-rw-r--r-- 1 oracle oinstall 1364 Oct 31 2005 dexttab.bsq

-rw-r--r-- 1 oracle oinstall 4937 Oct 31 2005 dfmap.bsq

-rw-r--r-- 1 oracle oinstall 728 Oct 31 2005 djava.bsq

-rw-r--r-- 1 oracle oinstall 33697 Apr 26 2011 dlmnr.bsq

-rw-r--r-- 1 oracle oinstall 9632 Dec 8 2009 dmanage.bsq

-rw-r--r-- 1 oracle oinstall 25509 Jun 8 2007 dobj.bsq

-rw-r--r-- 1 oracle oinstall 32867 May 18 2011 doptim.bsq

-rw-r--r-- 1 oracle oinstall 47093 Nov 12 2009 dpart.bsq

-rw-r--r-- 1 oracle oinstall 16679 Jan 8 2007 dplsql.bsq

-rw-r--r-- 1 oracle oinstall 17811 Oct 9 2009 drac.bsq

-rw-r--r-- 1 oracle oinstall 128181 May 13 2011 drep.bsq

-rw-r--r-- 1 oracle oinstall 139898 Jun 11 2010 dsec.bsq

-rw-r--r-- 1 oracle oinstall 17751 Mar 9 2009 dsqlddl.bsq

-rw-r--r-- 1 oracle oinstall 19958 Jul 30 2008 dsummgt.bsq

-rw-r--r-- 1 oracle oinstall 15830 Apr 29 2011 dtools.bsq

-rw-r--r-- 1 oracle oinstall 5474 Oct 31 2006 dtxnspc.bsq

-rw-r--r-- 1 oracle oinstall 2495314 Sep 18 2011 recover.bsq

-rw-r--r-- 1 oracle oinstall 53130 Jul 14 2008 sql.bsq [oracle@oracledb admin]$

[oracle@oracledb admin]$ find . -name "*.bsq" | xargs grep "create table obj"

./dmanage.bsq:create table object_usage /* object usage statistics */

./dcore.bsq:create table obj$ /* object table */

./dcore.bsq:create table objerror$

./dcore.bsq:create table objauth$ /* table authorization table */

./dcore.bsq:create table objpriv$ /* privileges granted to objects */

[oracle@oracledb admin]$ more dcore.bsq

REM sanagara 07/18/11 - add spare columns to sqlerror$

REM yifeng 04/04/10 - add comments to opqtype$ flags

REM gravipat 10/15/09 - add sqlerror$

REM rmacnico 04/14/09 - add comments for seg$, ts$ flag bits

REM shvenugo 03/19/09 - add comments to opqtype$ flags

REM mbastawa 04/06/08 - add result cache for tab$.property

REM schakrab 03/24/08 - add comments on obj$ flag

Rem mziauddi 01/25/08 - use spare2+spare3 of cdef$ for constraint ddl SCN

REM ramekuma 03/13/08 - bug-6865413: add comments on i_obj2 index

REM jaeblee 02/06/08 - add i_syn2

REM bvaranas 01/30/08 - project 25274: Add deferred_stg$

REM ssonawan 07/13/07 - bug-6020455: add comments on user$ table

REM achoi 04/20/07 - add i_obj5

REM sfeinste 04/09/07 - fix comments for type# column of obj$

REM wechen 01/22/07 - fix comments for type# column of obj$

REM krajaman 03/01/07 - add objerror$

REM achoi 11/07/06 - obj$.spare3 stores base user#

REM jaeblee 10/26/06 - moved edition$ creation from denv.bsq

REM jiyang 09/07/06 - add comments for audit vault trigger

REM akruglik 09/01/06 - replace CMV$ with EV$, CMVCOL$ with EVCOL$ +

REM rename app_edition# with edition_obj#

REM wechen 07/31/06 - add comments for type# column of obj$

REM akruglik 04/07/06 - add EV$ and EVCOL$

REM suelee 02/16/06 - bug 4956995 - moved objauth$ et al from dsec.bsq

REM achoi 12/15/05 - tab$.trigflag 0x200000 indicates read-only table

REM vmarwah 11/08/05 - Versioning Enabled flag in tab$

REM jklein 08/01/05 - creation

create tablespace SYSTEM datafile "D_DBFN"

"D_DSTG" online

create rollback segment SYSTEM tablespace SYSTEM

storage (initial 50K next 50K)

create cluster c_obj# (obj# number)

pctfree 5 size 800 /* don't waste too much space */

/* A table of 32 cols, 2 index, 2 col per index requires about 2K.

* A table of 10 cols, 2 index, 2 col per index requires about 750.

storage (initial 130K next 200k maxextents unlimited pctincrease 0)

/* avoid space management during IOR I */

create index i_obj# on cluster c_obj#

REM NOTE

REM Logminer/Streams uses contents of this table.

REM Please do not reuse any flags without verifying the impact of your

REM changes on inter-op.

create table tab$ /* table table */

( obj# number not null, /* object number */

/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE

* TRANSACTION DURING TRUNCATE */

dataobj# number, /* data layer object number */

ts# number not null, /* tablespace number */

file# number not null, /* segment header file number */

block# number not null, /* segment header block number */

bobj# number, /* base object number (cluster / iot) */

/create table obj

...skipping

REM Please do not reuse any flags without verifying the impact of your

REM changes on inter-op.

create table obj$ /* object table */

( obj# number not null, /* object number */

dataobj# number, /* data layer object number */

owner# number not null, /* owner user number */

name varchar2("M_IDEN") not null, /* object name */

namespace number not null, /* namespace of object (see KQD.H): */

/* 1 = TABLE/PROCEDURE/TYPE, 2 = BODY, 3 = TRIGGER, 4 = INDEX, 5 = CLUSTER, */

/* 8 = LOB, 9 = DIRECTORY, */

/* 10 = QUEUE, 11 = REPLICATION OBJECT GROUP, 12 = REPLICATION PROPAGATOR, */

/* 13 = JAVA SOURCE, 14 = JAVA RESOURCE */

/* 58 = (Data Mining) MODEL */

subname varchar2("M_IDEN"), /* subordinate to the name */

type# number not null, /* object type (see KQD.H): */

/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */

/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */

/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */

/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */

/* 23 = DIRECTORY , 24 = QUEUE, */

/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */

/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */

/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */

/* 35 = INDEX SUBPARTITION */

/* 82 = (Data Mining) MODEL */

/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */

/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */

ctime date not null, /* object creation time */

mtime date not null, /* DDL modification time */

stime date not null, /* specification timestamp (version) */

status number not null, /* status of object (see KQD.H): */

/* 1 = VALID/AUTHORIZED WITHOUT ERRORS, */

/* 2 = VALID/AUTHORIZED WITH AUTHORIZATION ERRORS, */

/* 3 = VALID/AUTHORIZED WITH COMPILATION ERRORS, */

/* 4 = VALID/UNAUTHORIZED, 5 = INVALID/UNAUTHORIZED */

remoteowner varchar2("M_IDEN"), /* remote owner name (remote object) */

linkname varchar2("M_XDBI"), /* link name (remote object) */

flags number, /* 0x01 = extent map checking required */

/* 0x02 = temporary object */

/* 0x04 = system generated object */

/* 0x08 = unbound (invoker's rights) */

/* 0x10 = secondary object */

/* 0x20 = in-memory temp table */

/* 0x80 = dropped table (RecycleBin) */

/* 0x100 = synonym VPD policies */

/* 0x200 = synonym VPD groups */

/* 0x400 = synonym VPD context */

/* 0x4000 = nested table partition */

oid$ raw(16), /* OID for typed table, typed view, and type */

spare1 number, /* sql version flag: see kpul.h */

spare2 number, /* object version number */

spare3 number, /* base user# */

spare4 varchar2(1000),

spare5 varchar2(1000),

spare6 date

)

storage (initial 10k next 100k maxextents unlimited pctincrease 0)

3数据字典视图

静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*。

user_*：该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）

all_*：该试图存储了当前用户能够访问的对象的信息, 而不是当前用户拥有的对象。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）

dba_*：该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）

这些视图由SYS用户创建的，所以使用需要加上SYS,为了方便， Oracle为每个数据字典表的视图头建立了同名字的公共同义词(public synonyms). 这样简单的处理就省去了写sys.的麻烦。

除了静态数据字典中三类视图，其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。这些视图会不断的进行更新，从而提供了关于内存和磁盘的运行情况，所以我们只能对其进行只读访问而不能修改它们。

Throughout its operation, Oracle Database maintains a set of virtual tables that record current database activity. These views are calleddynamic performance views because they are continuously updated while a database is open and in use. The views, also sometimes calledV$ views。

注释查询:

可以通过官方文档查询

4动态性能（V$）视图

V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。