第5章 数据字典和动态性能视图
数据字典记录了数据库的系统信息，包括只读的表和视图，属于SYS用户，存储在system表空间；
数据字典包括数据字典基表和数据字典视图，视图基于基表建立，普通用户不能访问基表，视图包括：USER_XXX、ALL_XXX、DBA_XXX；
动态性能视图记录实例活动信息，是动态变动的；
oracle中，用户和scheme（方案）是一一对应的，scheme是用户所拥有对象的集合，用户可以被其他用户授权从而访问其他用户的对象；
USER_XXX用于显示当前用户所拥有的所有对象；
ALL_XXX用于返回当前用户可以访问的所有对象；
DBA_XXX用于显示数据库的所有对象信息，需要具有DBA角色或SELECT_CATALOG_ROLE角色的用户才能查询；
一、数据字典：记录了oracle数据库的所有系统信息，包括数据字典和数据字典视图
内容包括：
1、对象定义
执行create时，存放对象定义，使用包dbms_metadata可以取得对象定义；
D:\>sqlplus sys/sys as sysdba
SQL> select dbms_metadata.get_ddl('TABLE','EMP') ddl from dual;
ERROR:
ORA-31603: 对象 "EMP" 属于类型 TABLE, 在方案 "SYS" 中未找到
ORA-06512: 在 "SYS.DBMS_METADATA", line 1546
ORA-06512: 在 "SYS.DBMS_METADATA", line 1583
ORA-06512: 在 "SYS.DBMS_METADATA", line 1901
ORA-06512: 在 "SYS.DBMS_METADATA", line 2792
ORA-06512: 在 "SYS.DBMS_METADATA", line 4333
ORA-06512: 在 line 1
SQL> conn scott/tiger
SQL> select dbms_metadata.get_ddl('TABLE','EMP') ddl from dual;
DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
2、对象占用空间
创建表、索引、簇时，oracle分配段存放这些对象，可以分析段占用的空间；
SQL> select bytes from user_segments where segment_name='EMP';
BYTES
----------
65536
要查看段已使用空间和剩余空间需先分析表：
SQL> conn scott/tiger
已连接。
SQL> analyze table EMP compute statistics;
表已分析。
SQL> select blocks,empty_blocks from user_tables where table_name='EMP';
BLOCKS EMPTY_BLOCKS
---------- ------------
5 3
3、列信息
SQL> set linesize 1000 pagesize 1000
SQL> select column_name,data_type,data_default from user_tab_columns where table
_name='DEPT';
COLUMN_NAME DATA_TYPE
DATA_DEFAULT
------------------------------ -------------------------------------------------
--------------------------------------------------------- ----------------------
----------------------------------------------------------
DEPTNO NUMBER
DNAME VARCHAR2
LOC VARCHAR2
SQL> col column_name format a15
SQL> col data_type format a15
SQL> col data_default format a15
SQL> select column_name,data_type,data_default from user_tab_columns where
_name='DEPT';
COLUMN_NAME DATA_TYPE DATA_DEFAULT
--------------- --------------- ---------------
DEPTNO NUMBER
DNAME VARCHAR2
LOC VARCHAR2
4、约束信息
查询数据字典视图：USER_CONSTRAINTS，USER_CONS_COLUMNS；
5、用户、角色、权限信息
DBA_USERS：所有数据库用户信息；
DBA_SYS_PRIVS：用户或角色的系统权限；
DBA_TAB_PRIVS：用户或角色的对象权限；
DBA_COL_PRIVS：用户或角色的列权限；
DBA_ROLE_PRIVS：用户或角色所具有的其他角色；
SQL> select username from dba_users;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
OUTLN
MDSYS
ORDSYS
CTXSYS
ANONYMOUS
EXFSYS
DMSYS
WMSYS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
MDDATA
DIP
TSMSYS
6、其他许多系统信息
常用数据字典：
1、DICT：显示当前用户可访问的所有数据字典视图；
select table_name from dict;
已选择659行。
SQL> show user;
USER 为 "SCOTT"
SQL> select table_name from dict where comments like '%grant%';
TABLE_NAME
------------------------------
USER_COL_PRIVS
ALL_COL_PRIVS
USER_COL_PRIVS_MADE
ALL_COL_PRIVS_MADE
USER_COL_PRIVS_RECD
ALL_COL_PRIVS_RECD
USER_ROLE_PRIVS
USER_SYS_PRIVS
USER_TAB_PRIVS
ALL_TAB_PRIVS
USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_MADE
USER_TAB_PRIVS_RECD
ALL_TAB_PRIVS_RECD
USER_AUDIT_STATEMENT
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
TABLE_PRIVILEGES
2、DICT_COLUMNS：显示数据字典视图每个列的作用
SQL> select * from dict_columns where table_name='DICT';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
COMMENTS
--------------------------------------------------------------------------------
DICT TABLE_NAME
Name of the object
DICT COMMENTS
Text comment on the object
3、dual：用于取得函数的返回值
SQL> select user from dual;
USER
------------------------------
SCOTT
4、global_name：用于显示当前数据库的全名
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
JSSBOOK
5、IND：显示当前用户的索引及索引统计信息
SQL> select index_name,index_type from ind where table_name='EMP';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
PK_EMP NORMAL
6、OBJ：显示当前用户的对象信息
SQL> select object_name,object_id,created from obj where object_type='TABLE';
OBJECT_NAME OBJECT_ID CREATED
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------------
DEPT 51155 08-10月-11
EMP 51157 08-10月-11
BONUS 51159 08-10月-11
SALGRADE 51160 08-10月-11
OBJECTS 51170 09-10月-11
7、SEQ：显示当前用户的序列信息
8、SYN：显示当前用户所拥有的同义词及同义词所对应的数据库对象名
9、TAB：显示当前用户所拥有的表、视图、序列
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
OBJECTS TABLE
二、动态性能视图：记录当前实例的活动信息
动态性能视图在实例启动时创建，实例停止时删除；
数据字典从数据文件中读取，动态性能视图从SGA和控制文件中读取；
动态性能视图的所有者为SYS，多数只能由特权用户或DBA用户查询；
维护和调整数据库性能时经常用到；
常用动态性能视图：
1、v$fixed_table：列出所有可用的动态性能视图和动态性能表
SQL> select name from v$fixed_table;
----1383行；
2、V$INSTANCE：列出当前实例的详细信息
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- -------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 jssbook NCUT-ZHJ 10.2.0.1.0 26-12月-11 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
3、V$SGA：显示SGA主要组成部门
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1251196
Variable Size 637536388
Database Buffers 838860800
Redo Buffers 7135232
4、V$SGAINFO：显示SGA更详细信息
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1251196 No
Redo Buffers 7135232 No
Buffer Cache Size 838860800 Yes
Shared Pool Size 318767104 Yes
Large Pool Size 209715200 Yes
Java Pool Size 109051904 Yes
Streams Pool Size 0 Yes
Granule Size 8388608 No
Maximum SGA Size 1484783616 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available 0
5、V$PARAMETER：取得初始化参数详细信息
6、V$VERSION：取得oracle版本详细信息
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
7、V$OPTION：显示已经安装的oracle选项
true为安装，false为未安装；
8、V$SESSION：显示会话详细信息
SQL> select SID,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
126 185 SYSMAN
127 866 SYS
128 165 DBSNMP
131 34 DBSNMP
134 226 SYSMAN
137 445 SCOTT
139 52 SYSMAN
142 2
143 991 SYSMAN
146 439
150 1
156 3
157 3
158 4
160 1
161 1
162 1
163 1
164 1
165 1
166 1
167 1
168 1
169 1
170 1
----username为null表示是后台进程
9、V$PROCESS：与oracle相关的所有进程信息
10、V$BGPROCESS：显示后台进程详细信息
SQL> select * from v$bgprocess where paddr<>'00';
PADDR PSERIAL# NAME DESCRIPTION ERROR
-------- ---------- ----- ---------------------------------------------------------------- ----------
6F27A8DC 1 PMON process cleanup ##########
6F27AECC 1 PSP0 process spawner 0 ##########
6F27B4BC 1 MMAN Memory Manager ##########
6F27BAAC 1 DBW0 db writer process 0 ##########
6F2807DC 1 ARC0 Archival Process 0 ##########
6F280DCC 1 ARC1 Archival Process 1 ##########
6F2813BC 1 ARC2 Archival Process 2 ##########
6F27C09C 1 LGWR Redo etc. ##########
6F27C68C 1 CKPT checkpoint ##########
6F27CC7C 1 SMON System Monitor Process ##########
6F27D26C 1 RECO distributed recovery ##########
6F27D85C 1 CJQ0 Job Queue Coordinator ##########
6F2819AC 1 QMNC AQ Coordinator ##########
6F27DE4C 1 MMON Manageability Monitor Process ##########
6F27E43C 1 MMNL Manageability Monitor Process 2 ##########
11、v$database：当前数据库详细信息
12、v$controlfile：当前控制文件信息
SQL> col name format a30
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------ --- ---------- --------------
D:\ORACLE_DATABASE\JSSBOOK\JSS NO 16384 430
BOOK\CONTROL01.CTL
D:\ORACLE_DATABASE\JSSBOOK\JSS NO 16384 430
BOOK\CONTROL02.CTL
D:\ORACLE_DATABASE\JSSBOOK\JSS NO 16384 430
BOOK\CONTROL03.CTL
SQL> col name format a50
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\CONTROL01.CTL NO 16384 430
D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\CONTROL02.CTL NO 16384 430
D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\CONTROL03.CTL NO 16384 430
13、v$datafile：当前数据文件信息
SQL> select file#,name,bytes from v$datafile;
FILE# NAME BYTES
---------- -------------------------------------------------- ----------
1 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\SYSTEM01.DBF 513802240
2 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\UNDOTBS01.DBF 319815680
3 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\SYSAUX01.DBF 335544320
4 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\USERS01.DBF 5242880
5 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS01.DBF 104857600
6 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS02.DBF 1048576000
7 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS03.DBF 2097152000
14、v$dbfile：数据文件编号
SQL> select * from v$dbfile;
FILE# NAME
---------- --------------------------------------------------
1 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\SYSTEM01.DBF
2 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\UNDOTBS01.DBF
3 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\SYSAUX01.DBF
4 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\USERS01.DBF
5 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS01.DBF
6 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS02.DBF
7 D:\ORA_DATABASE\JSSBOOK\SCOTT_TBS03.DBF
15、v$logfile：日志文件信息
SQL> select group#,member from v$logfile;
GROUP# MEMBER
----------
1 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\REDO01.LOG
2 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\REDO02.LOG
3 D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\REDO03.LOG
16、v$log：显示日志详细信息
SQL> select group#,members,bytes,status from v$log;
GROUP# MEMBERS BYTES STATUS
---------- ---------- ---------- ----------------
1 1 52428800 INACTIVE
2 1 52428800 INACTIVE
3 1 52428800 CURRENT
17、v$thread：重做线程详细信息
18、v$lock：显示锁信息，与v$session连接可以查询占有锁的会话、等待锁的会话；
SQL> select a.username,a.machine,b.lmode,b.request
2 from v$session a, v$lock b
3 where a.sid=b.sid and a.type='USER';
SQL> 未选定行
19、v$tablespace：显示表空间信息
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- -------------------------------------------------- --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
3 TEMP NO NO YES
4 USERS YES NO YES
5 SCOTT_TBS YES NO YES
20、v$tempfile：显示当前数据库所包含的临时文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
D:\ORACLE_DATABASE\JSSBOOK\JSSBOOK\TEMP01.DBF
。。。。。。
====================================================================================================================================
启动服务OracleServiceDEMO--------运行DEMO实例
D:\>set oracle_sid=DEMO
D:\>sqlplus sys/DEMO as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 26 17:48:13 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> set lines 10000
SQL> select * from v$instance;
ORA-01034: ORACLE not available
SQL> startup open
ORACLE 例程已经启动。
Total System Global Area 1484783616 bytes
Fixed Size 1251196 bytes
Variable Size 637536388 bytes
Database Buffers 838860800 bytes
Redo Buffers 7135232 bytes
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
--------------- ---------------- ---------------------------------------------------------------- ----------------- -------------- ------------ --- ---------- ------- --------------- ---------- --- ----------------- ------------------ --------- ---
1 demo NCUT-ZHJ 10.2.0.1.0 26-12月-11 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL> select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOG LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROL CONTROLFILE_CR CONTROLFILE_SEQUENCE# CONTROLFILE_CHANGE# CONTROLFILE_TI OPEN_RESETL VERSION_TIME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_A ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVEL SWITCHOVER_STATUS DATAGUAR GUARD_S SUPPLEME SUP SUP FOR PLATFORM_ID PLATFORM_NAME RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN FLASHBACK_ON SUP SUP DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD FS_FAIL FS_FAILOVER_OBSERVER_HOST
---------- --------- -------------- ----------------- -------------- ----------------------- -------------- ------------ ------------------ --------------- ------- -------------- --------------------- ------------------- -------------- ----------- -------------- ---------- -------------------- -------------------- -------- ----------- ----------- ---------------- ------------------ -------- -------------------- -------- ------- -------- --- --- --- ----------- ----------------------------------------------------------------------------------------------------- ---------------------------- ---------------------- ----------- ------------------ --- --- ------------------------------ -------------------------- --------------------- ------------------------------ --------------------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3514387243 DEMO 19-12月-11 1 19-12月-11 0 NOARCHIVELOG 313670 289639 CURRENT 19-12月-11 326 313822 26-12月-11 NOT ALLOWED 19-12月-11 READ WRITE MAXIMUM PERFORMANCE UNPROTECTED ENABLED 3514397227 3514397227 PRIMARY 0 DISABLED SESSIONS ACTIVE DISABLED NONE NO NO NO NO 7 Microsoft Windows IA (32-bit) 1 1 313925 NO NO NO DEMO 0 DISABLED 0
SQL> select * from v$dbfile;
FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 D:\ORACLE_DATABASE\DEMO\SYSTEM01.DBF
2 D:\ORACLE_DATABASE\DEMO\UNDOTBS1.DBF
3 D:\ORACLE_DATABASE\DEMO\SYSAUX01.DBF
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --- ---------- --------------
D:\ORACLE_DATABASE\DEMO\CONTROL01.CTL NO 16384 430
D:\ORACLE_DATABASE\DEMO\CONTROL02.CTL NO 16384 430
D:\ORACLE_DATABASE\DEMO\CONTROL03.CTL NO 16384 430
SQL>
=====================================================================================================================================
启动服务OracleServiceJSSBOOK
D:\>set oracle_sid
oracle_sid=DEMO
D:\>set oracle_sid=JSSBOOK
D:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 26 17:55:35 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup open
ORA-01081: 无法启动已在运行的 ORACLE - 请首先关闭它
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
jssbook
SQL> exit
With the Partitioning, OLAP and Data Mining options 断开
D:\>
D:\>set oracle_sid
oracle_sid=JSSBOOK
D:\>set oracle_sid=DEMO
D:\>sqlplus sys/sys as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 26 17:59:01 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
demo
=====================================================================================================================
总结：
一个机器可以建多个实例，但只能有一个实例处于运行中，取决于oracle_sid；
阅读(787) | 评论(0) | 转发(0) |
