我的Oracle 9i学习日志(9)--数据字典与动态性能视图及练习-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

我的Oracle 9i学习日志(9)--数据字典与动态性能视图及练习

简介:

一、数据字典

不仅是每个Oracle数据库的核心组件之一,也是所有数据库用户重要的信息资源;描述数据与对象的数据;包含只读的表和视图;存储在系统表空间;所有者为sys;由Oracle server维护;通过select语句访问。
执行数据定义语言(definition language,DDL)语句或有些数据操纵语言 (data manipulation language ,DML) 语句时会更新数据字典。
包括两部分:
1、 基表(base tables),在create database执行时(执行sql.bsq脚本)创建;用户不能直接访问因为它们是被加密的,无法用DML语句直接更新这些表,有一个例外,AUD$。基表举例:IND$,包含数据的索引信息。
2、数据字典视图,在执行$ORACLE_HOME/rdbms/admin/catalog.sql,catproc.sql脚本时创建。
数据字典的内容:审计、用户被赋予的特权和角色、用户名字、完整性约束、资源的分配情况、逻辑和物理结构。
数据字典视图种类:
 

数据字典举例:
概览: DICTIONARY, DICT_COLUMNS
Schema objects: DBA_TABLES, DBA_INDEXES,DBA_TAB_COLUMNS, DBA_CONSTRAINTS
空间分配: DBA_SEGMENTS, DBA_EXTENTS
数据库结构: DBA_TABLESPACES,DBA_DATA_FILES
Data dictionary views are static views that answer questions such as:
• Was the object ever created?
• What is the object a part of?
• Who owns the object?
• What privileges do users have?
• What restrictions are on the object?
二、动态性能视图
虚表;记录当前数据库活动及活动状态;在数据库运行期间持续更新;可通过访问内存和控制文件获得的信息;用于数据库的监视和性能调优;所有者为sys;synonymous以v$开头;在V$FIXED_TABLE里列出所有这些表的名字。
DBA可以在这些视图上再创建视图。
The dynamic performance tables answer questions such as:
• Is the object online and available?
• Is the object open?
• What locks are being held?
• Is the session active?
举例:
•V$CONTROLFILE: Lists the names of the control files
•V$DATABASE: Contains database information from the control file.
•V$DATAFILE: Contains data file information from the control file
•V$INSTANCE: Displays the state of the current instance
•V$PARAMETER: Lists parameters and values currently in effect for the session
•V$SESSION: Lists session information for each current session
•V$SGA: Contains summary information on the system global area (SGA)
•V$SPPARAMETER: Lists the contents of the SPFILE
•V$TABLESPACE: Displays tablespace information from the control file
•V$THREAD: Contains thread information from the control file
•V$VERSION: Version numbers of core library components in the Oracle server
三、管理脚本命名规则

 
Practice 5: Using Data Dictionary and Dynamic Performance Views
Which of the following statements are true about the data dictionary?
The data dictionary describes the database and its objects.
The data dictionary includes two types of objects: base tables and data dictionary
views.
The data dictionary is a set of tables.
The data dictionary records and verifies information about its associated database.
答案:abcd
Base tables are created using the catalog.sql script.
True
False
答案:b
解析:是在create database时创建,catalog.sql是创建视图。
Which three of the following statements are true about how the data dictionary is used?
The Oracle server modifies it when a DML statement is executed.
It is used to find information about users, schema objects, and storage structures.
It is used by users and DBAs as a reference.
The data dictionary is a necessary ingredient for the database to function.
答案:bcd
Data dictionary views are static views.
True
False
答案:a
The information for a dynamic performance view is gathered from the control file.
True
False
答案:b
Which of the following questions might a dynamic performance view answer?
Is the object online and available?
What locks are being held?
Who owns the object?
What privileges do users have?
Is the session active?
答案:abde

7 Connect as SYSTEM/MANAGER and find a list of the data dictionary views.
8 Identify the database name, instance name, and size of the database blocks.
Hint: Query the V$DATABASE, V$THREAD, and V$PARAMETER dynamic
performance views.
9 List the name of the data files.
Hint: Query the V$DATAFILE dynamic performance view.
10 Identify the data file that makes up the SYSTEM tablespace.
Hint: Query the DBA_DATA_FILES data dictionary view to identify the SYSTEM
tablespace data file.
11 How much free space is available in the database and how much is already used?
Hints
- Query the DBA_FREE_SPACE data dictionary view to show how much free
space is available in the database.
- Query the DBA_SEGMENTS data dictionary view to display how much space is
already used.
12 List the name and creation date of the database users.
Hint: Query the DBA_USERS data dictionary view to list the name and the creation
of the database users.










本文转自 d185740815 51CTO博客,原文链接:http://blog.51cto.com/luotaoyang/284100,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: