Oracle的数据字典的查询.doc

第12讲 Oracle的数据字典的查询目的：1. 掌握Oracle9i数据字典的作用和意义。2. 掌握常用对象的数据字典的查询。授课内容：11.1 什么是数据字典：1. 数据字典(Oracle data dictionary): Oracle9i数据库的所有对象，权限，系统信息的视图，称为数据字典。2. Oracle9i数据的所有信息，系统的权限，用户，角色，表，视图等等所有对象在创建后都保存在数据字典的表中。这些表是归系统管理员DBA所有的，其他用户没有权限查询这些表。3. 但Oracle提供了访问这些表的视图，供其他用户访问这些数据字典。4. 数据库中存放数据字典的表的是非常关键的了，不能随意访问。5. Oracle数据字典存储了用来管理数据库对象的所有信息。6. Oracle内部字典：从用户角度出发的数据字典。7. 数据字典不是按字母来排序的，而是按其功能进行(表，安全性，等)分组的。11.2 数据字典的分组：在查询数据字典时，Oracle把不同的信息分别使用了不同的视图，如用户自己的，所有的用户，和DBA的信息。1. USER： 以USER_开头的视图只可以查询出登录用户自己的对象信息。2. ALL： 以ALL_开头的视图可以查询所有用户的对象信息。这些对象要满足条件是：你创建的，或你由权限的，或授权给public 组的。(1) 你创建的对象。(2) 对象的所有者已经授予了这些对象的某个权限给你。(3) public角色有权限的所有对象。3. DBA: 以DBA_开头的视图查询数据库中所有的对象信息。 数据字典的常用分组：1. 导航图：DICTIONARY （DICT）和DICT_COLUMNS2. 可选择的内容：表(和列)，视图，同义词，序列3. 约束和注释：4. 索引和簇：5. 抽象数据类型，与ORDBMS相关的结构和LOB：6. 数据库连接，实体化视图：7. 存储过程，函数，程序包，触发器：8. 维：9. 空间分配和空间使用：10. 用户，权限：11. 角色：12. 审计： 数据字典的名称规定： 除了特殊的情况外，Oracle数据字典对象的所有名称都以如下3个前缀开头： USER: 查询用户所拥有的对象的信息 ALL：纪录了USER的信息以及授予了public或用户的对象的信息。 DBA：包含数据库的所有对象，不论拥有者是谁。 常见的数据字典：13. Oracle9i的数据字典：Dictionary : 同义词是DICT: 可以查询Oracle9i有那些数据字段。DICT_CLOUMNS: 每个数据字典视图的列14. 系统的信息查询系统的版本信息select * from v$version15. 数据库的对象信息USER_CATALOG: 显示用户拥有的所有表，视图，同义词，序列ALL_CATELOG: 显示User以及public权限的所有的表，视图，同义词，序列。USER_OBJECTS: 查询所有类型对象，包括簇，数据库连接，目录函数，存储过程，函数，包，触发器，Java类，抽象数据类型，序列，同义词，LOB，视图。ALL_OBJECTS: 16. 表信息USER_TABLES: 查询用户拥有的表的信息。ALL_TABLES：USER_TAB_COLUMNS: 查询表的列的专门信息。17. 视图信息USER_VIEWS:ALL_VIEWS18. 序列信息USER_SEQUENCES:ALL_SEQUENCES19. 同义词对象信息USER_SYNONYMS: 列出了用户所拥有的全部同义词。ALL_SYNONYMS:20. 索引USER_INDEXES:ALL_INDEXES:21. 用户USER_USERS: 当前的用户ALL_USERS: 数据库中的所有用户22. 角色：USER_ROLES: ALL_ROLES:23. 约束USER_CONSTRAINTS:ALL_CONSTRAINTS：24. 表的字段USER_TAB_COLUMNS:ALL_TAB_COLUMNS:USER_TAB_COMMENTS: 字段的注释25. 索引的字段USER_IND_COLUMNS:ALL_IND_COLUMNS:26. 约束的字段USER_CONS_COLUMNS: 与约束相关的列。ALL_CONS_COLUMNS:27. 用户的对象级权限USER_TAB_PRIVS： 当前用户的对象级权限ALL_TAB_PRIVS: 所有用户的对象级权限28. 用户的系统级权限USER_SYS_PRIVS: 当前用户的系统级权限ALL_SYS_PRIVS: 所有用户的系统级权限29. PL/SQL存储过程和函数，包对象的源代码USER_SOURCE ALL_SOURCE30. 触发器对象信息USER_TRIGGERS, ALL_TRIGGERS31. 对象权限，系统权限，角色权限ROLE_TAB_PRIVS： 角色的对象权限ROLE_SYS_PRIVS: 角色的系统权限ROLE_ROLE_PRIVS： 角色的角色权限32. 抽象的数据类型：USER_TYPES:ALL_TYPES:33. 大对象的信息USER_LOBS:ALL_LOBS:34. aaaOracle数据库字典介绍 Oracle数据字典是有表和视图组成的，存储有关数据库结构信息的一些数据库对象。数据库字典描述了实际数据是如何组织的。对它们可以象处理其他数据库表或视图一样进行查询，但不能进行任何修改。 Oracle数据库字典通常是在创建和安装数据库时被创建的，Oracle数据字典是Oracle数据库系统工作的基础，没有数据字典的支持，Oracle数据库系统就不能进行任何工作。 在Oracle数据库字典中，许多视图都有三个不同的实例，它们的前缀分别为USER_、ALL_及DBA_。USER_为前缀的数据库字典视图通常记录执行查询的帐户所拥有的对象的信息，ALL_为前缀的数据库字典视图通常记录包括执行查询的帐户所拥有的对象的信息及授权至PUBLIC的帐户用户所拥有的对象的信息，DBA_为前缀的数据库字典视图则包含所有数据库对象的信息，而不管其所有者。其他的字典视图中主要的是V$视图，之所以这样叫是因为他们都是以V$或GV$开头的。V$视图是基于X$虚拟视图的。V$视图是SYS用户所拥有的，在缺省状况下，只有SYS用户和拥有DBA系统权限的用户可以看到所有的视图，没有DBA权限的用户可以看到USER_和ALL_视图，但不能看到DBA_视图。与DBA_,ALL,和USER_视图中面向数据库信息相反，这些视图可视的给出了面向实例的信息。 在Oracle的绝大多数数据字典视图中都有象DBA_TABLES,ALL_TABLES和USER_TABLES这样的视图家族。Oracle中有超过100个视图家族，下表列出了最重要和最常用的视图家族，需要注意的是每个视图家族都有一个DBA_,一个ALL_一个USER_视图。视图家族 描述 COL_PRIVS 包含了表的列权限，包括授予者、被授予者和权限 EXTENTS 数据范围信息，比如数据文件，数据段名(segment_name)和大小 INDEXES 索引信息，比如类型、唯一性和被涉及的表 IND_COLUMNS 索引列信息，比如索引上的列的排序方式 OBJECTS 对象信息，比如状态和DDL time ROLE_PRIVS 角色权限，比如GRANT和ADMIN选项 SEGMENTS 表和索引的数据段信息，比如tablespace和storage SEQUECNCES 序列信息，比如序列的cache、cycle和ast_number SOURCE 除触发器之外的所有内置过程、函数、包的源代码 SYNONYMS 别名信息，比如引用的对象和数据库链接db_link SYS_PRIVS 系统权限，比如grantee、privilege、admin选项 TAB_COLUMNS 表和视图的列信息，包括列的数据类型 TAB_PRIVS 表权限，比如授予者、被授予者和权限 TABLES 表信息，比如表空间(tablespace),存储参数(storage parms)和数据行的数量 TRIGGERS 触发器信息，比如类型、事件、触发体(trigger body) USERS 用户信息，比如临时的和缺省的表空间 VIEWS 视图信息，包括视图定义 在Oracle中还有一些不常用的数据字典表，但这些表不是真正的字典家族，他们都是一些重要的单一的视图。这些视图见下表:视图名称 描述 USER_COL_PRIVS_MADE 用户授予他人的列权限 USER_COL_PRIVS_RECD 用户获得的列权限 USER_TAB_PRIVS_MADE 用户授予他人的表权限 USER_TAB_PRIVS_RECD 用户获得的表权限 第12讲 事务处理目的：1. 掌握Oracle事务的基本概念2. 掌握Oracle事务处理的基本的语句授课内容：1.1 事务(Transaction):1. 在Oracle9i中，将多个DML语句（insert, update, delete）作为一个整体来执行，当全部执行成功是，将执行结果提交到数据库，否则恢复到没有执行这些语句的状态。2. 将执行的结果对数据库更新，叫提交(commit)。在commit之前，只有你才能看到语句对表的影响，访问表的其他用户，依然看到的是旧的数据。3. 恢复到没有执行这些语句的时候，叫回滚(rollback)。1.2 Oracle9i中的事务处理语句：1. commit: 提交：提交所有的在上一次commit以后的所有DML语句。2. rollback: 回滚： 回滚到上次commit的状态的结果。3. savepoint 标号： 保存事务处理点。如果执行了commit语句，则所有的回滚点将清除了。标号必须是唯一的，如果有2个保存点的标号相同，则前一个保存点被消除了。4. rollback to savepoint 标号：将标号以后的处理语句回滚。5. set transaction read only: 建立只读的事务6. set transaction read write：建立可读可写的事务。注：If you use a SET TRANSACTION statement, then it must be the first statement in your transaction特别注意：在执行一个DDL语句前和语句后，Oracle9i隐含提交当前的事务。Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement事务处理例子：update emp set job=student where empno=9912savepoint aupdate emp set job=TEACHER where empno=9901savepoint brollback to savepoint acommit1.3 有些计算机语言中有自动提交功能。如Java的JDBC接口，在创建默认的数据库连接时就是自动提交的工作模式。这时要修改它为手动提交方式，为事务做准备。SET TRANSACTION COMMIT ROLLBACK SAVEPOINT ROLLBACK TO SAVEPOINT 事务的一致性：一致性是事物控制的关键慨念。掌握了oracle 的一致性模型，能使您更好的，更恰当的使用事务控制。oracle通过一致性保证数据只有在事务全部完成后才能被用户看见和使用。这项技术对多用户数据库有巨大的作用。用户会话只是用户到数据库的一个连接，而用户对数据库的操作则是通过会话中的一个个事务来进行的。对事务的管理不仅仅是DBA的任务，每个发布事务的用户也能够对自已的事务进行管理。事务的开始：Oracle中的事务由遇到的第一条可执行SQL语句开始。DDL (Data Definition Language)DML(Data Manipulation Language ) 事务的结束：用户执行commit或rollback语句用户执行一条DDL语句，比如create,drop,rename,alter。如果当前事务中包含有已经执行的DML语句，Oracle首先提交事务，然后再将DDL语句作为一个新的只包含一条语句的事务执行并提交。用户主动断开到oracle的连接。用户进程意外中止。提交事务前完成的工作：在SGA区的回退缓存中生成该事务的回退条目。在回退条目中保存有该事务所修改的数据的原始版本。在SGA区的重做日志缓存中生成该事务的重做记录。重做记录中记载了该事务对数据块所进行的修改，并且还记载了对回退段中的数据块所进行的修改。缓存中的重做记录有可能在事务提交之前就写入硬盘中。在SGA区的数据库缓丰中记录了事务对数据库所进行的修改。这些修改也有可能在事务提交之前就写入硬盘中。提交事务时完成的工作：在为该事务指定的回退段中的内部事务表内记录下这个事务已经被提交，并且生成一个惟一的SCN记录在内部事务表中，用于惟一标识这个事务。LGWR后进进程将SGA区重做日志缓存中的重做记录写入联机重做日志文件。在写入重做日志的同时还将写入该事务的SCN。Oracle服务进程释放事务所使用的所有记录锁与表锁。Oracle通知用户事务提交完成。Oracle将该事务标记为已完成。回退事务完成的工作：Oracle通过使用回退段中的回退条目，撤销事务中所有SQL语句对数据库所做的修改。Oracle服务进程释放事务所使用的所有锁Oracle通知事务回退成功。Oracle将该事务标记为已完成 事务处理编程要点：、对于UPDATE和DELETE操作，应只封锁要做改动的行，在完成修改后立即提交。 、当多个事务正利用共享更新的方式进行更新，则不要使用共享封锁，而应采用共享更新封锁，这样其它用户就能使用行级锁，以增加并行性。 、尽可能将对一个表的操作的并发事务施加共享更新锁，从而可提高并行性。 、在应用负荷较高的期间，不宜对基础数据结构（表、索引、簇和视图）进行修改补充资料：Oracle的锁设立封锁机制主要是为了对并发操作进行控制，对干扰进行封锁，保证数据的一致性和准确性。Oracle数据库封锁方式有三种：共享封锁，独占封锁，共享更新封锁 b:8f4f63b9bb封锁类型/b:8f4f63b9bb OracleRDBMS的封锁类型可分为如下三类： 、内部级封锁 内部级封锁是用于保护ORACLE内部结构，由系统内部实现，用户不能访问，因此我们不必对此做过多的了解。 、DDL级封锁（字典/语法分析封锁） DDL级封锁也是由ORACLERDBMS来控制，它用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对SQL定义语句作语法分析时自动地加锁，无需用户干予。字典/语法分析封锁共分三类： （）、字典操作锁：用于对字典操作时，锁住数据字典，此封锁是独占的，从而保护任何一个时刻仅能对一个字典操作。 （）、字典定义锁：用于防止在进行字典操作时又进行语法分析，这样可以避免在查询字典的同时改动某个表的结构。 （）、表定义锁：用于一个SQL语句正当访问某个表时，防止字典中与该表有关的项目被修改。 、DML级封锁 DML级封锁用于控制并发事务中的数据操纵，保证数据的一致性和完整性，其封锁对象可以是表或行。 对用户的数据操纵，Oracle可以自动为操纵的数据进行封锁，但如果有操纵授权，则为满足并发操纵的需要另外实施封锁。DML封锁可由一个用户进程以显式的方式加锁，也可通过某些 SQL语句隐含方式实现。 DML锁有如下三种封锁方式： （1）、共享封锁方式（SHARE） （2）、独占封锁方式（EXCLUSIVE） （3）、共享更新封锁（SHARE UPDATE） 其中SHARE，EXCLUSIVE用于表封锁，SHARE UPDATE用于行封锁。 1、共享方式的表封锁 共享方式的表封锁是对表中的所有数据进行封锁，该锁用于保护查询数据的一致性，防止其它用户对已封锁的表进行更更新。其它用户只能对该表再施加共享方式的锁，而不能再对该表施加独占方式的封锁，共享更新锁可以再施加，但不允许持有共享更新封锁的进程做更新。共享该表的所有用户只能查询表中的数据，但不能更新。共享方式的表封锁只能由用户用SQL语句来设置，基语句格式如下： quote:04b72348bdLOCK TABLE ,. IN SHARE MODE NOWAIT /quote:04b72348bd 执行该语句，对一个或多个表施加共享方式的表封锁。当指定了选择项NOWAIT，若该封锁暂时不能施加成功，则返回并由用户决定是进行等待，还是先去执行别的语句。 持有共享锁的事务，在出现如下之一的条件时，便释放其共享锁： A、执行COMMIT或ROLLBACK语句。 B、退出数据库（LOGOFF）。 C、程序停止运行。 共享方式表封锁常用于一致性查询过程，即在查询数据期间表中的数据不发生改变。 2、独占方式表封锁 独占方式表封锁是用于封锁表中的所有数据，拥有该独占方式表封锁的用户，即可以查询该表，又可以更新该表，其它的用户不能再对该表施加任何封锁（包括共享、独占或共享更新封锁）。其它用户虽然不能更新该表，但可以查询该表。 独占方式的表封锁可通过如下的SQL语句来显示地获得： LOCK TABLE ,. IN EXCLUSIVE MODE NOWAIT 独占方式的表封锁也可以在用户执行DML语句INSERT、UPDATE、DELETE时隐含获得。 拥有独占方式表封锁的事务，在出现如下条件之一时，便释放该封锁： （1）、执行COMMIT或ROLLBACK语句。 （2）、退出数据库（LOG OFF） （3）、程序停止运行。 独占方式封锁通常用于更新数据，当某个更新事务涉及多个表时，可减少发生死锁。 DML锁有如下三种封锁方式： （1）、共享封锁方式（SHARE） （2）、独占封锁方式（EXCLUSIVE） （3）、共享更新封锁（SHARE UPDATE） 其中SHARE，EXCLUSIVE用于表封锁，SHARE UPDATE用于行封锁。 1、共享方式的表封锁 共享方式的表封锁是对表中的所有数据进行封锁，该锁用于保护查询数据的一致性，防止其它用户对已封锁的表进行更更新。其它用户只能对该表再施加共享方式的锁，而不能再对该表施加独占方式的封锁，共享更新锁可以再施加，但不允许持有共享更新封锁的进程做更新。共享该表的所有用户只能查询表中的数据，但不能更新。共享方式的表封锁只能由用户用SQL语句来设置，基语句格式如下： quote:04b72348bdLOCK TABLE ,. IN SHARE MODE NOWAIT /quote:04b72348bd 执行该语句，对一个或多个表施加共享方式的表封锁。当指定了选择项NOWAIT，若该封锁暂时不能施加成功，则返回并由用户决定是进行等待，还是先去执行别的语句。 持有共享锁的事务，在出现如下之一的条件时，便释放其共享锁： A、执行COMMIT或ROLLBACK语句。 B、退出数据库（LOGOFF）。 C、程序停止运行。 共享方式表封锁常用于一致性查询过程，即在查询数据期间表中的数据不发生改变。 2、独占方式表封锁 独占方式表封锁是用于封锁表中的所有数据，拥有该独占方式表封锁的用户，即可以查询该表，又可以更新该表，其它的用户不能再对该表施加任何封锁（包括共享、独占或共享更新封锁）。其它用户虽然不能更新该表，但可以查询该表。 独占方式的表封锁可通过如下的SQL语句来显示地获得： LOCK TABLE ,. IN EXCLUSIVE MODE NOWAIT 独占方式的表封锁也可以在用户执行DML语句INSERT、UPDATE、DELETE时隐含获得。 拥有独占方式表封锁的事务，在出现如下条件之一时，便释放该封锁： （1）、执行 COMMIT或ROLLBACK语句。 （2）、退出数据库（LOG OFF） （3）、程序停止运行。 独占方式封锁通常用于更新数据，当某个更新事务涉及多个表时，可减少发生死锁。 3、共享更新封锁方式 共享更新封锁是对一个表的一行或多行进行封锁，因而也称作行级封锁。表级封锁虽然保证了数据的一致性，但却减弱了操作数据的并行性。行级封锁确保在用户取得被更新的行到该行进行更新这段时间内不被其它用户所修改。因而行级锁即可保证数据的一致性又能提高数据操作的迸发性。 可通过如下的两种方式来获得行级封锁： （1）、执行如下的SQL封锁语句，以显示的方式获得： LOCK TABLE ,. IN SHARE UPDATEMODE NOWAIT （2）、用如下的SELECT .FOR UPDATE语句获得： SELECT ,. FROM WHERE FOR UPDATE OF ,.NOWAIT 一旦用户对某个行施加了行级封锁，则该用户可以查询也可以