Oracle11g温习-第五章：数据字典

1、数据字典（Data dictionary)的功能

1) central of oracle database 每个oracle数据库的核心

2) describes the databases and its objects 描述数据库和它所有的对象

3) contains read_only table and views 包括只读的表和视图

4) owner ：sys sys独有

5) oracle server 维护，ddl操作会更新数据字典

6) 通过select 访问

7) 记录数据库的物理、逻辑结构和所有对象的定义的信息

8) 存放在system tablesapce

2、数据字典的组成

基表-------------------------------视图--------------同义词-------User access

（x$) (全局用于集群) dba_XXX

（_$) all_XXX

user_XXX

---可以通过trace 查看所使用的基表

3、数据字典的内容

1) 数据库的逻辑和物理的架构

2) 用户及对象的定义和存储信息

3) 权限、角色、审计等

4) database的状态

4、数据字典分类

静态（static) 和动态（dynami)---------------------(通过dict查看所有的数据字典的视图)

static ：在数据库open状态下访问，可以通过静态视图了解database的架构(记录database的架构，object的数据定义和存储等信息)

user_：存储当前用户所拥有的对象的相关信息

all_ : 存储当前用户能够访问的对象（包括用户所拥有的对象和别的用户授权访问的对象)的信息。

dba_ : 存储所有用户对象的信息（默认只能有sys/system 用户访问)

DYNAMIC: 反映数据库当前的状态，从控制文件和内存中读出（v$视图)，可以通过v$fixed_table 这个视图查到所有的动态视图的名称；

用于调优和数据库监控

v$多用于单机，GV$全局的，多用于集群环境

V$CONTROLFILE

V$DATABASE

V$DATAFILE

SYS @ prod >desc v$fixed_table;

Name Null? Type

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

NAME VARCHAR2(30)

OBJECT_ID NUMBER

TYPE VARCHAR2(5)

TABLE_NUM NUMBER

SYS @ prod >select name,object_id,type from v$fixed_table where rownum<6;

NAME OBJECT_ID TYPE

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

X$KQFTA 4294950912 TABLE

X$KQFVI 4294950913 TABLE

X$KQFVT 4294951149 TABLE

X$KQFDT 4294950914 TABLE

X$KQFCO 4294951036 TABLE

SYS @ prod >select name from v$datafile;

NAME

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

/u01/app/oracle/oradata/prod/system01.dbf

/u01/app/oracle/oradata/prod/undotbs01.dbf

/u01/app/oracle/oradata/prod/sysaux01.dbf

/u01/app/oracle/oradata/prod/users01.dbf

/u01/app/oracle/oradata/prod/test01.dbf

/u01/app/oracle/oradata/prod/test02.dbf

/u01/app/oracle/oradata/prod/test04.dbf

V$INSTANCE

V$PARAMETER

V$SESSION

V$SGA

V$SPPARAMETER

V$TABLESPACE

V$THREAD

V$VERSION

V$TRANSACTION

V$SQLTEXT(查看SQL语句的详细内容)

SQL> select USERNAME,SID,serial# from v$session where username is not null; ——查看当前会话信息

USERNAME SID SERIAL#

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

SYS 39 1

SCOTT 37 3

SQL> conn scott/tiger

SCOTT @ prod >update emp set sal=sal/1 ;【用其他会话进行事物处理】

SQL> SELECT A.USERNAME,B.STATUS FROM V$SESSION A,V$TRANSACTION B WHERE A.SADDR =B.SES_ADDR;

——联合V$SESSION、V$TRANSACTION查看事务处理状态

USERNAME STATUS

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

SCOTT ACTIVE

SQL> SELECT A.USERNAME,B.STATUS,c.sql_text FROM V$SESSION A,V$TRANSACTION B,v$sqltext c

where a.saddr=b.ses_addr and a.prev_sql_id=c.sql_id and a.username='SCOTT'; ——联合三个视图来查询

USERNAME STATUS SQL_TEXT

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

SCOTT ACTIVE update emp set sal=sal/1

SYS@ prod> Desc dict 查看数据字典

SYS@ prod>select table_name from dict where table_name like '%INDEX%';

TABLE_NAME

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

USER_INDEXES

ALL_INDEXES

DBA_INDEXES

DBA_INDEXTYPES

USER_INDEXTYPES

ALL_INDEXTYPES

DBA_INDEXTYPE_COMMENTS

USER_INDEXTYPE_COMMENTS

ALL_INDEXTYPE_COMMENTS

DBA_INDEXTYPE_ARRAYTYPES

USER_INDEXTYPE_ARRAYTYPES

ALL_INDEXTYPE_ARRAYTYPES

DBA_INDEXTYPE_OPERATORS

USER_INDEXTYPE_OPERATORS

ALL_INDEXTYPE_OPERATORS

USER_PART_INDEXES

ALL_PART_INDEXES

DBA_PART_INDEXES

DBA_XML_INDEXES

ALL_XML_INDEXES

USER_XML_INDEXES

INDEX_HISTOGRAM

INDEX_STATS

V$INDEXED_FIXED_COLUMN

GV$INDEXED_FIXED_COLUMN

查找列的注释数据字典dict_columns

SYS@ prod>desc dict_columns;

Name Type Null?

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

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

SYS@ prod>select column_name,comments from dict_columns where table_name='DBA_TABLES';

COLUMN_NAME COMMENTS

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

BUFFER_POOL The default buffer pool to be used for table blocks

ROW_MOVEMENT Whether partitioned row movement is enabled or disabled

GLOBAL_STATS Are the statistics calculated without merging underlying partitions?

USER_STATS Were the statistics entered directly by the user?

DURATION If temporary table, then duration is sys$session or sys$transaction else NULL

SKIP_CORRUPT Whether skip corrupt blocks is enabled or disabled

MONITORING Should we keep track of the amount of modification?

CLUSTER_OWNER Owner of the cluster, if any, to which the table belongs

DEPENDENCIES Should we keep track of row level dependencies?

COMPRESSION Whether table compression is enabled or not

DROPPED Whether table is dropped and is in Recycle Bin

OWNER Owner of the table

TABLE_NAME Name of the table

TABLESPACE_NAME Name of the tablespace containing the table

CLUSTER_NAME Name of the cluster, if any, to which the table belongs

IOT_NAME Name of the index-only table, if any, to which the overflow or mapping table entry belongs

STATUS Status of the table will be UNUSABLE if a previous

DROP TABLE operation failed,

VALID otherwise

PCT_FREE Minimum percentage of free space in a block

PCT_USED Minimum percentage of used space in a block

INI_TRANS Initial number of transactions

MAX_TRANS Maximum number of transactions

INITIAL_EXTENT Size of the initial extent in bytes

NEXT_EXTENT Size of secondary extents in bytes

MIN_EXTENTS Minimum number of extents allowed in the segment

MAX_EXTENTS Maximum number of extents allowed in the segment

PCT_INCREASE Percentage increase in extent size

FREELISTS Number of process freelists allocated in this segment

FREELIST_GROUPS Number of freelist groups allocated in this segment

LOGGING Logging attribute

BACKED_UP Has table been backed up since last modification?

NUM_ROWS The number of rows in the table

BLOCKS The number of used blocks in the table

EMPTY_BLOCKS The number of empty (never used) blocks in the table

AVG_SPACE The average available free space in the table

CHAIN_CNT The number of chained rows in the table

AVG_ROW_LEN The average row length, including row overhead

AVG_SPACE_FREELIST_BLOCKS The average freespace of all blocks on a freelist

NUM_FREELIST_BLOCKS The number of blocks on the freelist

DEGREE The number of threads per instance for scanning the table

INSTANCES The number of instances across which the table isto be scanned

CACHE Whether the table is to be cached in the buffer cache

TABLE_LOCK Whether table locking is enabled or disabled

SAMPLE_SIZE The sample size used in analyzing this table

LAST_ANALYZED The date of the most recent time this table was analyzed

PARTITIONED Is this table partitioned? YES or NO

IOT_TYPE If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL

TEMPORARY Can the current session only see data that it place in this object itself?

SECONDARY Is this table object created as part of icreate for domain indexes?

NESTED Is the table a nested table?

ORACLE 存放数据都是以段的形式存放的

SYS@ prod>desc dba_segments;

Name Null? Type

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

OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(18)

TABLESPACE_NAME VARCHAR2(30)

HEADER_FILE NUMBER

HEADER_BLOCK NUMBER

BYTES NUMBER

BLOCKS NUMBER

EXTENTS NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NUMBER

MAX_EXTENTS NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

RELATIVE_FNO NUMBER

BUFFER_POOL VARCHAR2(7)

SYS@ prod>select bytes,blocks,extents,header_file,header_block,tablespace_name from dba_segments where owner='SCOTT' and segment_name='EMP';

BYTES BLOCKS EXTENTS HEADER_FILE HEADER_BLOCK TABLESPACE_NAME

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

65536 8 1 4 27 USERS

查看表的大小系统为 emp 这张表分配了64K的存储空间 1个区 8个块段头在第4个文件第27个数据块

SYS@ prod>desc dba_extents;

Name Null? Type

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

OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(18)

TABLESPACE_NAME VARCHAR2(30)

EXTENT_ID NUMBER

FILE_ID NUMBER

BLOCK_ID NUMBER

BYTES NUMBER

BLOCKS NUMBER

RELATIVE_FNO NUMBER

SYS@ prod>select file_id,block_id ,blocks from dba_extents where owner='SCOTT' and segment_name='EMP';

FILE_ID BLOCK_ID BLOCKS

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

4 25 8

SCOTT@ prod>select rowid from emp;

ROWID

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

AAAMfMAAEAAAAAgAAA

AAAMfMAAEAAAAAgAAB

AAAMfMAAEAAAAAgAAC

AAAMfMAAEAAAAAgAAD

AAAMfMAAEAAAAAgAAE

AAAMfMAAEAAAAAgAAF

AAAMfMAAEAAAAAgAAG

AAAMfMAAEAAAAAgAAH

AAAMfMAAEAAAAAgAAI

AAAMfMAAEAAAAAgAAJ

AAAMfMAAEAAAAAgAAK

AAAMfMAAEAAAAAgAAL

AAAMfMAAEAAAAAgAAM

AAAMfMAAEAAAAAgAAN

SYS@ prod>desc dba_tablespaces;

Name Null? Type

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

TABLESPACE_NAME NOT NULL VARCHAR2(30)

BLOCK_SIZE NOT NULL NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NUMBER

PCT_INCREASE NUMBER

MIN_EXTLEN NUMBER

STATUS VARCHAR2(9)

CONTENTS VARCHAR2(9)

LOGGING VARCHAR2(9)

FORCE_LOGGING VARCHAR2(3)

EXTENT_MANAGEMENT VARCHAR2(10)

ALLOCATION_TYPE VARCHAR2(9)

PLUGGED_IN VARCHAR2(3)

SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)

DEF_TAB_COMPRESSION VARCHAR2(8)

RETENTION VARCHAR2(11)

BIGFILE VARCHAR2(3)

SYS@ prod>select tablespace_name,block_size,status ,logging from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING 修改数据是否会产生日志

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

SYSTEM 8192 ONLINE LOGGING

UNDOTBS1 8192 ONLINE LOGGING

SYSAUX 8192 ONLINE LOGGING

TEMP 8192 ONLINE NOLOGGING

USERS 8192 ONLINE LOGGING

EXAMPLE 8192 ONLINE NOLOGGING

动态性能视图

虚拟的表

记录当前数据库的统计信息操作

当数据库被修改的时候记录操作信息

从控制文件和内存收集信息

Sys 用户独有

v$开头的同义词

v$_ gv$_ 开头的都是视图

SYS@ prod>desc v_$log; 此乃基表

Name Null? Type

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

GROUP# NUMBER

THREAD# NUMBER

SEQUENCE# NUMBER

BYTES NUMBER

MEMBERS NUMBER

ARCHIVED VARCHAR2(3)

STATUS VARCHAR2(16)

FIRST_CHANGE# NUMBER

FIRST_TIME DATE

SYS@ prod>desc v$log; 此乃对应的视图

Name Null? Type

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

GROUP# NUMBER

THREAD# NUMBER

SEQUENCE# NUMBER

BYTES NUMBER

MEMBERS NUMBER

ARCHIVED VARCHAR2(3)

STATUS VARCHAR2(16)

FIRST_CHANGE# NUMBER

FIRST_TIME DATE

以下视图同上

V$controlfile

V$datafile

V$database

V$instance

V$parameter

V$session

V$sga

SYS@ prod>Show parameter cache;

NAME TYPE VALUE

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

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_cache_advice string ON

db_cache_size big integer 0

db_keep_cache_size big integer 0

db_recycle_cache_size big integer 0

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

session_cached_cursors integer 20

SYS@ prod>desc v$parameter;

Name Null? Type

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

NUM NUMBER

NAME VARCHAR2(80)

TYPE NUMBER

VALUE VARCHAR2(512)

DISPLAY_VALUE VARCHAR2(512)

ISDEFAULT VARCHAR2(9)

ISSES_MODIFIABLE VARCHAR2(5)

ISSYS_MODIFIABLE VARCHAR2(9)

ISINSTANCE_MODIFIABLE VARCHAR2(5)

ISMODIFIED VARCHAR2(10)

ISADJUSTED VARCHAR2(5)

ISDEPRECATED VARCHAR2(5)

DESCRIPTION VARCHAR2(255)

UPDATE_COMMENT VARCHAR2(255)

HASH NUMBER

SYS @ prod >desc v$session;

Name Null? Type

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

SADDR RAW(4)

SID NUMBER

SERIAL# NUMBER

AUDSID NUMBER

PADDR RAW(4)

USER# NUMBER

USERNAME VARCHAR2(30)

COMMAND NUMBER

OWNERID NUMBER

TADDR VARCHAR2(8)

LOCKWAIT VARCHAR2(8)

STATUS VARCHAR2(8)

SERVER VARCHAR2(9)

SCHEMA# NUMBER

SCHEMANAME VARCHAR2(30)

OSUSER VARCHAR2(30)

PROCESS VARCHAR2(12)

MACHINE VARCHAR2(64)

TERMINAL VARCHAR2(30)

PROGRAM VARCHAR2(48)

TYPE VARCHAR2(10)

SQL_ADDRESS RAW(4)

SQL_HASH_VALUE NUMBER

SQL_ID VARCHAR2(13)

SQL_CHILD_NUMBER NUMBER

PREV_SQL_ADDR RAW(4)

PREV_HASH_VALUE NUMBER

PREV_SQL_ID VARCHAR2(13)

PREV_CHILD_NUMBER NUMBER

MODULE VARCHAR2(48)

MODULE_HASH NUMBER

ACTION VARCHAR2(32)

ACTION_HASH NUMBER

CLIENT_INFO VARCHAR2(64)

FIXED_TABLE_SEQUENCE NUMBER

ROW_WAIT_OBJ# NUMBER

ROW_WAIT_FILE# NUMBER

ROW_WAIT_BLOCK# NUMBER

ROW_WAIT_ROW# NUMBER

LOGON_TIME DATE

LAST_CALL_ET NUMBER

PDML_ENABLED VARCHAR2(3)

FAILOVER_TYPE VARCHAR2(13)

FAILOVER_METHOD VARCHAR2(10)

FAILED_OVER VARCHAR2(3)

RESOURCE_CONSUMER_GROUP VARCHAR2(32)

PDML_STATUS VARCHAR2(8)

PDDL_STATUS VARCHAR2(8)

PQ_STATUS VARCHAR2(8)

CURRENT_QUEUE_DURATION NUMBER

CLIENT_IDENTIFIER VARCHAR2(64)

BLOCKING_SESSION_STATUS VARCHAR2(11)

BLOCKING_INSTANCE NUMBER

BLOCKING_SESSION NUMBER

SEQ# NUMBER

EVENT# NUMBER

EVENT VARCHAR2(64)

P1TEXT VARCHAR2(64)

P1 NUMBER

P1RAW RAW(4)

P2TEXT VARCHAR2(64)

P2 NUMBER

P2RAW RAW(4)

P3TEXT VARCHAR2(64)

P3 NUMBER

P3RAW RAW(4)

WAIT_CLASS_ID NUMBER

WAIT_CLASS# NUMBER

WAIT_CLASS VARCHAR2(64)

WAIT_TIME NUMBER

SECONDS_IN_WAIT NUMBER

STATE VARCHAR2(19)

SERVICE_NAME VARCHAR2(64)

SQL_TRACE VARCHAR2(8)

SQL_TRACE_WAITS VARCHAR2(5)

SQL_TRACE_BINDS VARCHAR2(5)