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的数据定义和存储等信息)
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)
