Oracle数据库管理知识要点.docx

Oracle数据库管理知识要点第一章1.如果创建的数据库将应用于针对特定主题执行复杂查询的环境，在安装数据库时应优先选择哪个选项（数据仓库）。如果创建的数据库将应用于大量并发用户执行简单事务处理的环境，在安装数据库时应优先选择哪个选项（事务处理）。2.网络服务名、全局数据库名和Oracle系统标示符（SID）：用Net Configuration Assistant进行Net服务名配置时，需要输入的数据库服务名应该是（全局数据库名）。3. OracleDBConsoleorcl：OEM控制台的服务进程；OracleDb10g_home1iSQL*PLUS：网页形式的sqlplus服务。Oracle Db10g_home1TNSListener：监听器，监听程序的服务进程。OracleServiceORCL：Oracle数据库实例的服务进程。9i internet 10g grid 网格计算 12c cloud 云计算第二章1.数据块的参数是DB_BLOCK_SIZE，在创建数据库时设定后，除非重新安装数据库，其他情况无法对其进行修改。2.从物理存储结构上，Oracle数据库主要由三种类型文件组成：数据文件、日志文件和控制文件。数据文件的扩展名为.DBF；日志文件的扩展名为.LOG；控制文件的扩展名为.CTL。3.逻辑存储结构从小到大是：数据块、盘区、段和表空间。数据块(也可以简称为块)是Oracle用来管理存储空间的最基本单元，也是最小的逻辑存储单元。Oracle数据库在进行输入输出操作时，都是以块为单位进行逻辑读写操作的。数据块的默认大小，由初始化参数DB_BLOCK_SIZE确定，在创建数据库之后不可以修改。4.数据文件、表空间和数据库的关系：一个数据文件只属于一个表空间，一个表空间由一个或多个数据文件组成，一个数据库由一个或多个表空间组成。Oracle存储空间在物理上表现为数据文件，而在逻辑上表现为表空间。5. 段分为五种类型：数据段、索引段、回退段、LOB段和临时段。6.Oracle最小的逻辑存储单元是数据块，磁盘空间分配的最小单元是盘区，在Oracle中用户可以使用的最大的逻辑存储结构是表空间。数据块的默认大小，由初始化参数DB_BLOCK_SIZE确定，在创建数据库之后不可以修改。通过SHOW PARAMETER语句可以查看数据库初始化参数的信息。7.在Oracle内存管理中，如果多个用户连接到同一个数据库实例，则在实例的系统全局区（SGA）中数据可为多个用户共享。程序全局区(PGA)和系统全局区（SGA）中，非共享区域是PGA。当数据库启动时，首先启动实例，系统将自动分配SGA，并启动Oracle的多个后台进程，内存区域和后台进程合称为一个Oracle实例。8.在Oracle数据库中，用户数据的排序有两个区域，一个是内存排序区，另一个是磁盘临时段。在排序区能够满足要求的情况下，系统将优先使用排序区，如果内存不够，Oracle将自动使用磁盘临时表空间进行排序。9. DBWn(Database Writer，数据库写入)进程，是Oracle中采用LRU(Least Recently Used，最近最少使用)算法将数据缓冲区中的数据写入数据文件的进程。LGWR(Log Writer，日志写入)进程，是负责管理日志缓冲区的一个后台进程，用于将日志缓冲区中的日志数据写入磁盘的日志文件中。CKPT(Check Point，检查点或检验点)进程，一般在发生日志切换时自动产生，用于缩短实例恢复所需的时间。SMON(System Monitor，系统监控)进程，用于数据库实例出现故障或系统崩溃时，通过将联机重做日志文件中的条目应用于数据文件，执行崩溃恢复。PMON(Process Monitor，进程监控)进程，用于在用户进程出现故障时执行进程恢复操作，负责清理内存存储区和释放该进程所使用的资源。ARCn(Archive Process，归档)进程，用于将写满的日志文件复制到归档日志文件中，防止日志文件组中的日志信息由于日志文件组的循环使用而被覆盖。RECO(Recovery，恢复)进程存在于分布式数据库系统中，用于自动解决在分布式数据库中出现的事务故障。10. 数据字典是由Oracle自动创建并更新的一组表，它是Oracle数据库的重要组成部分，提供了数据库结构、数据库对象空间分配和数据库用户等有关的信息。数据字典的所有者为sys用户，而数据字典表和数据字典视图都被保存在system表空间中。第三章1.SQL语言分为数据操纵语言(DML)、数据定义语言(DDL)和数据控制语言(DCL)。DML包括SELECT、INSERT、UPDATE、DELETE、CALL、MERGE、COMMIT和ROLLBACK；DDL包括CREATE、ALTER、DROP、RENAME和TRUNCATE；DCL包括GRANT和REVOKE。2.取消上次提交以来的所有更改（即事务回退）应用SQL语句ROLLBACK；将当前事务所做的更改永久化（即事务提交）应用SQL语句COMMIT；进行更新或更改功能的SQL语句是UPDATE；修改数据库结构的SQL语句是ALTER；授予其他用户对数据库结构的访问权限的SQL语句是GRANT；收回用户访问数据库结构的权限的SQL语句是REVOKE。3. 比较操作符LIKE的匹配模式可以使用的两个通配符是下划线字符(_)和百分号字符(%)，其中下划线字符(_)用来匹配指定位置的一个字符；百分号字符(%)用来匹配从指定位置开始的任意个字符。在WHERE子句中可以使用BETWEEN操作符，用来检索列值包含在指定区间内的数据行。这个区间是闭区间，这就意味着包含区间的两个边界值。4.在使用GROUP BY子句后，在SELECT子句的后面只可以有两类表达式：统计函数（聚合函数）和进行分组的列名。HAVING子句通常与GROUP BY子句一起使用。5. SELECT子句中使用DISTINCT关键字用来限定在检索结果中显示不重复的数据，对于重复值，只显示其中一个。6.在执行数学和日期运算时，经常使用系统提供的dual表。可以使用DESCRIBE命令查看该表的结构。7.注意decode和nvl函数的功能和用法。第四章1. 如果在INSERT INTO后没有指定列名，那么VALUES子句中的值必须按照表结构中定义的列的次序为每个列提供值。如果在INSERT INTO子句中指定了列名，那么每一个指定的列只能有一个值，并且值的次序必须与表中定义的次序相同。DELETE语句只从表中删除数据，不会删除表结构，如果要删除表结构，应使用DROP TABLE语句。在删除包含主键的表时，如果有其他表的外键引用该表，则删除操作也将失败，为此可以选禁用引用该主键的外键约束。2.假设表employee有字段employeeno，name和salary分别与表emp的eno，ename和sal类型、长度等一直，编写一个SQL语句，该语句实现将emp表中所有记录的三个字段的值插入employee表中。3.写一条SQL语句，将表emp中每个员工的工资提高10%。（将emp表中工作是MANAGER的员工工资提高10%；将工作的部门在NEW YORK的员工的工资提高10%）。4.写一条SQL语句，删除emp表中工作是MANAGER的员工记录；删除工作的部门所在地为NEW YORK的员工记录。第五章1. Oracle是通过“模式”来组织和管理这些数据库对象；模式与数据库用户是一一对应的，每个数据库用户都拥有一个与用户名相同的模式。2. 模式对象主要包括表、索引、触发器、PL/SQL包、视图、存储过程和Java类等；非模式对象主要包括表空间、用户和角色等。3. 创建用户、修改用户、删除用户的命令。4. ROWID伪列类型，保存每条记录的物理地址。5. 创建表时，CACHE的作用是利用系统缓冲提高对该表的查询执行效率，创建表时默认使用NOCACHE子句。6.创建表使用create table语句，删除表使用drop table语句，修改表结构使用alter table语句。7.按照数据完整性约束的用途，可以将表的完整性约束分为NOT NULL（非空约束）、PRIMARY KEY（主键约束）、UNIQUE（唯一约束）、CHECK（检查约束）和FOREIGN KEY（外键约束）。在表中，唯一标识一条记录的一列或多列，在建表时需要将这些列设为PRIMARY KEY（主键约束）；设定了UNIQUE约束的列，是否允许其值为null；对于FOREIGN KEY约束，被引用的列或列组应该具有主键约束或唯一约束；引用列的取值只能为被引用列的值或NULL值；如果引用列中存储了被引用列的某个值，则不能直接删除被引用列中的这个值，否则会与第二条相矛盾。如果一定要删除，需要先删除引用列中的这个值，然后再删除被引用列中的这个值。第七章（编程题）1.一般不要把变量名声明与表中字段名完全一样，这样可能会得到不正确的结果。因为：SQL 语句中列名和变量名相同，列名优先级高于变量名.2.PL/SQL中SELECT 语句时，返回的每个值都必须使用 INTO 子句保存到相应变量中，且查询只能返回一条记录，返回多条或 0 条记录都会产生异常。3.如果记录变量具有相同的记录类型，那么可以将记录变量中的值赋予另一个记录变量。如果记录类型不同，即便记录类型中的字段相同，也不可以将一个记录变量赋予另一个记录变量。4.可以使用select语句对记录进行赋值，记录中的字段应该与查询选择结果列表的字段相匹配。如：Declare TYPE t_StudentRec is RECORD( FirstName students.first_name%type, LastName students.last_name%type, Major students.major%type);v_Student t_StudentRec ;Begin SELECT first_name,last_name,major into v_Student FROM students WHERE id=10000;End;(select语句中的列顺序与在记录变量中的字段顺序一致）5.PL/SQL中可以使用如下两种注释符号添加注释文本：双减号(-)：使用双减号(-)可以添加单行注释，其注释范围从双减号开始，到该行的末尾；正斜杠星号字符对(/* */)：使用正斜杠星号字符对(/* */)可以添加一行或多行注释，这种形式的注释可以位于可执行代码中间，系统只将字符对之间的文本内容作为注释。6.Oracle提供了两种条件选择语句来对程序进行逻辑控制，分别是IF条件语句和CASE表达式。掌握两种语句的基本语法和简单用法。7.下例中, 变量 v_myage 没有初始化. DECLARE v_myage NUMBER;BEGIN IF v_myage ”将实参和形参名关联在一起。6.输入参数提供默认值的两种方式是在声明参数时使用赋值运算符(:=)和使用DEFAULT 关键字为参数赋值。7. 函数是命名的 PL/SQL 块 (子程序)，可以有任意个输入（ IN ）参数，必须也只能返回一个返回值。8. 程序包主要是为了实现程序模块化，程序包可以将相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，通过这种方式可以构建供程序人员重用的代码库。包通常由两部分组成：规范和包体。9. 创建包规范需要使用CREATE PACKAGE语句；创建包体需要使用CREATE PACKAGE BODY语句。第九章1. Oracle提供的最基本的表类型是堆表，它也是默认的表类型，用于存储永久性的数据。外部表是引用在数据库以外的文件系统中存储的数据的只读表，也就是说，外部表所要读取的数据存储在Oracle数据库外部的文件中，并且只能读取这些数据，不能进行数据写入。创建外部表需要使用CREATE TABLE ORGANIZATION EXTERNAL语句。2. 索引组织表也称为IOT，就是存储在一个索引结构中的表。这种表结构不仅可以存储数据，还可以存储为表建立的索引，以提高查询性能。存储在堆中的表是无组织的（即只要有可用空间，数据可以放在任何地方），IOT中的数据则按主键存储和排序。在使用CREATE TABLE语句创建索引组织表时，必须使用ORGANIZATION INDEX子句，以指示创建的表为索引组织表。3. 为临时表分配的空间来自临时表空间，这避免了与永久对象的数据争用存储空间。在临时表中存储数据是以事务或会话为基础的。当用户当前的事务结束或会话终止时，临时表占用的存储空间将被释放，存储的数据也随着丢失。由于临时表中存储的数据只在当前事务处理或者会话进行期间有效，因此临时表主要分为两种事务级别临时表和会话级别临时表。创建临时表时，如果指定ON COMMIT DELETE ROWS子句，则表示创建的临时表是事物级别临时表；在创建临时表时，如果指定ON COMMIT PRESERVE ROWS子句，则表示创建的临时表是会话级别临时表。4. Oracle的对象类型如同PL/SQL程序包一样包括规范和主体两部分。创建对象类型规范的语句是CREATE OR REPLACE TYPE 对象名称 AS OBJECT；创建对象类型主体的语句是CREATE OR REPLACE TYPE BODY对象名称IS。5. 对象表是基于对象类型建立的表，而不是作为列的集合。对象表的每行都代表一个对象。创建对象表的语法为CREATE TABLE table_name OF object_type。6. Oracle数据库对表或索引分区的方法有5种：范围分区、散列分区、列表分区、组合范围散列分区和组合范围列表分区。范围分区就是根据数据表中的某个值的范围进行分区，根据某个值的大小或次序，决定将每条数据分别存储在哪个分区上，使用RANGE关键字；散列分区是通过HASH算法均匀分布数据的一种分区类型。通过在I/O设备上进行散列分区，可以使得分区的大小一致。创建散列分区需要使用PARTITION BY HASH子句；列表分区表是基于特定值的列表对表进行分区。创建列表分区需要使用PARTITION BY LIST子句；第十章1. 索引是数据库中用于存放表中每一条记录的位置的一种对象，它可以提高数据查询操作性能，但会降低添加和删除操作的效率。2. 唯一性约束和主键约束会隐式地创建索引。3. 位图(位映射)索引与B树索引不同，使用B树索引时，通过在索引中保存排过序的索引列的值，以及数据行的ROWID来实现快速查找。而位图索引不存储ROWID值，也不存储键值，它一般在包含少量不同值的列上创建。第十一章1. 视图是一个虚拟表，它并不存储真实的数据，视图可以建立在一个或多个表(或其他视图)上，它不占用实际的存储空间。2. 视图的种类：关系视图、内嵌视图、对象视图和物化视图。3. 内嵌视图（ inline view，内建视图，内联视图），是一个带有别名(或相关名)的、可以在SQL语句中使用的子查询。内嵌视图不是模式对象。是嵌入到父查询中的查询，能够在任何可以使用表名称的地方使用；可以出现在select语句的from子句中，也可以出现在insert into、update、delete from等语句中；是临时的，它只存在于父查询的运行期间，但是它可以让开发人员有能力在整个查询的任何部分中使用视图结果。4. 物化视图在Oracle 8i以前的版本中，这些对象被称为快照。5. 对视图执行DML操作，实际上就是对视图的基表执行DML操作。更新视图必须满足的条件：在视图中使用DML语句只能修改一个底层的基表。只能修改键值保存表。（如果基表的主键在视图中也为主键，则称这个表为键值保存表。）如果对记录的修改违反了基表的约束条件，则无法更新视图。如果创建的视图包含连接运算符、DISTINCT运算符、集合运算符、聚合函数和GROUP BY子句，则将无法更新视图。如果创建的视图包含伪列或表达式，则将无法更新视图。不能有WITH READ ONLY修饰。6. 序列是为生成唯一数字列值创建的数据库对象。序列与视图一样，并不占用实际的存储空间，创建序列需要使用CREATE SEQUENCE语句。创建了序列之后，可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。CURRVAL返回序列的当前值；第一次使用NEXTVAL时，将返回该序列的初始值，以后在引用NEXTVAL时，将使用INCRMENT BY子句的值来增加序列值，并返回这个新值。7. 同义词是数据库对象的一个别名，这些对象可以是表、视图、序列、过程、函数、程序包，甚至其他同义词。通过使用同义词，用户可以访问其它模式的数据库对象而无需指定模式前缀。同义词分为私有同义词和公有同义词两种（两种同义词的区别？）。创建私有同义词使用语句CREATE OR REPLACE SYNONYM ；创建公有同义词使用语句CREATE OR REPLACE PUBLIC SYNONYM。删除同义词，需要使用DROP SYNONYM语句。第十二章1触发器是一种特殊的存储过程，它在发生某种数据库事件时由Oracle系统自动触发。触发器通常用于加强数据的完整性约束和业务规则等，对于表来说，触发器可以实现比CHECK约束更为复杂的约束。2. 触发器分为DML触发器、INSTEAD OF触发器、系统事件触发器和DDL触发器等四种类型。DML触发器由DML语句触发，可在DML操作前或后触发，例如INSERT、UPDATE和DELETE语句。INSTEAD OF触发器又称替代触发器，用于执行一个替代操作来代替触发事件的操作。系统事件触发器在发生如数据库启动或关闭等系统事件时触发。DDL触发器由DDL语句触发，例如CREATE、ALTER和DROP语句。DDL触发器同样可以分为BEFORE触发器与AFTER触发器。3. DML触发器中的DML操作主要包括INSERT、DELETE和UPDATE操作；通常根据触发器所针对的具体事件将DML触发器分为INSERT触发器、UPDATE触发器和DELETE触发器。任何DML触发器都可以按触发时间分为BEFORE触发器与AFTER触发器。在行级触发器中，为了获取某列在DML操作前后的数据，Oracle提供了两种特殊的标识符:OLD和:NEW，通过:OLD.column_name的形式可以获取该列的旧数据，而通过:NEW.column_name则可以获取该列的新数据。DML触发器可以分为语句级触发器和行级触发器，创建触发器使用了FOR EACH ROW选项创建的是行级触发器，未使用该选项创建的是语句级触发器。语句级触发器与行级触发器的区别是：当一个DML语句操作影响数据库中的多行数据时，对于其中的每个数据行，行级触发器均被触发一次。4. INSTEAD OF触发器用于执行一个替代操作来代替触发事件的操作，而触发事件本身最终不会被执行。DML触发器一般定义在表上，而INSTEAD OF触发器一般定义在视图上（尤其复杂视图）。5. 创建DDL触发器需要用户具有DBA权限。6. 在一个单独的触发器中分别处理不同的触发事件，实现的方法（注意触发器创建语句和触发器体中的判断语句），见ppt-12章P11。7. 创建触发器语句中的WHEN选项的作用和语法，见ppt-12章P15。8. 系统事件触发器是指由数据库系统事件触发的触发器，系统事件有：系统事件 说 明 LOGOFF用户从数据库注销LOGON用户登录数据库SERVERERROR服务器发生错误SHUTDOWN关闭数据库实例STARTUP打开数据库实例第十三章1. 并发性是一个非常重要的概念，是用来解决多个用户对同一数据进行操作的问题。Oracle Database 10g系统通过使用事务和锁机制，解决了数据库的并发性问题。2. 所谓事务，就是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。事务的ACID特性指的是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。3. 使用COMMIT或ROLLBACK显式地结束一个事务，分别用于提交和回滚事务。4. 如果想回退到一个复杂事务中间某一点，需要事先设定存储点（SAVEPOINT）。5. SET TRANSACTION语句可以用来设置事务处理的各种属性，例如，它的隔离层次，它是只读的还是可以进行读写的；是否需要使用特定的回滚段等。其中选择隔离层的设置参数为：Read committed是Oracle默认的隔离层，该事务中所执行的查询语句可以获取该查询之前已经提交的数据，不读取未提交的数据；Serializable：事务与事务之间被完全隔开，事务以串行的方式执行，即执行结果与一次执行一个事务的结果是一样的；Read only：事务中不能有任何修改数据库中数据的语句以及修改数据库结构的语句，是serializable的子集，但serializable可以执行DML操作；Read write：是默认设置，表示在事务中既可以有访问语句，也可以有数据修改语句。6. Oracle采用隐式开始一个事务，但结束事务必须显式结束，以下为结束事务的情况：COMMIT语句，提交；ROLLBACK语句，回滚；用户执行了一条DDL语句，提交；用户断开了与Oracle的连接，提交；用户进程意外被终止，回滚。7. 设置约束延期性可以将Oracle对表的约束暂时失效，其语法为：SET CONSTRAINT ALL | | DEFERRED | IMMEDIATE。8. 锁是由Oracle系统自动管理的，它对用户而言是透明的，锁的持续时间等于被提交事务的处理时间。Oracle中的3类锁：DDL锁、DML锁和内部锁。为了避免事务有可能产生数据不一致，Oracle使用的锁模式包括S锁（SHARE）共享锁、X锁（EXCLUSIVE）排他锁写锁、RS锁（ROW SHARE）行级共享锁、RX锁（ROW EXCLUSIVE SHARE）行级排他锁和SRX锁（SHARE ROW EXCLUSIVE）共享行级排他锁。锁的粒度是指被锁定的数据对象的大小。锁粒度与数据库系统的并发度和并发控制的开销密切相关。锁粒度越大，数据库中所能够使用的资源也就越少，并发度也就越小，系统开销也就越小；锁粒度越小，并发度也就越大，系统开销也就越大。从概念上讲，一个数据库中锁的粒度可以分为4个级别：数据库级、表级、行级和列级。Oracle不支持列级别锁，包括了TX锁、TM锁和数据库级锁三种。第十四章1. 删除用户需要使用DROP USER语句。如果该用户已经在数据库中创建了内容，则必须指定CASCADE关键字，表示在删除该用户的同时，删除该用户创建的所有内容。2. Oracle中的权限主要分为系统权限与对象权限。系统权限是指对整个Oracle系统的操作权限，例如连接数据库、创建与管理表或视图等。对象权限是指用户对数据库中数据库对象的操作权限，例如对某一个表的插入、修改和删除记录等权限。3.授予权限需要使用GRANT语句，撤消用户的权限需要使用REVOKE语句。4. 数据库中的权限较多，为了方便对用户权限的管理，Oracle数据库允许将一组相关的权限授予某个角色，然后将这个角色授予需要的用户，拥有该角色的用户将拥有该角色包含的所有权限。5. 创建角色需要使用CREATE ROLE语句，为角色设置口令的语法。6. 创建用户配置文件示例：用DBA身份创建用户配置文件user_profile，对该文件的设置说明如下：(1) 限制用户允许拥有的会话数为1，对应的参数为SESSIONS_PER_USER。(2) 限制该用户执行的每条SQL语句可以占用的CPU总时间为百分之五秒，对应的参数为CPU_PER_CALL。(3) 限制该用户的空闲时间为10分钟，对应的参数为IDLE_TIME。(4) 限制用户登录数据库时可以失败的次数为3次，对应的参数为FAILED_LOGIN_ATTEMPTS。(5) 限制口令的有效时间为10天，对应的参数为PASSWORD_LIFE_TIME。(6) 设置用户登录失败次数达到限制要求时，用户被锁定的天数为3天，对应的参数为PASSWORD_LOCK_TIME。(7) 设置口令使用时间达到有效时间之后，口令仍然可以使用的“宽限时间”为3天，对应的参数为PASSWORD_GRACE_TIME。对照语句为：CREATE PROFILE user_profile LIMITSESSIONS_PER_USER 1CPU_PER_CALL 5IDLE_TIME 10FAILED_LOGIN_ATTEMPTS