Oracle复习资料.docx

1、Oracle复习资料 Oracle复习资料1.简答题第一章有一题，第四章有三题，7.8章各一题，第九章两题。2.编程题涉及到有参数的游标和存储过程。最后一道编程题，不仅要写出创建存储过程的语句，还得写出来调用存储过程的语句，别忘记了； 第一章简答题：Oracle 与SQL Server的区别？ 答：1、对OS的支持 支持Oracle的操作系统有Windows,Linux,Mac； 支持SQL Server的操作系统只有Windows。2、数据库的架构 Oracle中，一个实例只能管理一个数据库，集群下才能实现管理多个数据库； SQL Server中是一个实例管理多个数据库。3、数据库的安全性

2、Oracle数据库获得了ISO安全认证； SQL Server没有通过安全认证。4、内存分配 Oracle中内存分配是由INIT.ORA来决定的； SQL Server的内存分配主要有动态和静态内存分配。第三章3.1SQL数据库沟通的语言标准 3.2Oracle 11g中支持的数据类型 3.3数据定义语言（DDL） 3.4 约束的使用 3.5数据操纵语言和数据查询语言3.6数据控制语言（DCL） 3.7小结 SQL数据库沟通的语言标准1、什么是SQLStructured Query Language，结构化查询语言，是关系数据库的标准语言。SQL是一个通用的、功能极强的关系数据库语言2、 SQ

3、L的种类数据定义语言(DDL):数据库、表的创建，修改，删除，声明 (CREATE，ALTER，DROP，DECLARE)。数据操纵语言(DML):数据表中记录的查询，删除，修改，插入(SELECT，DELETE，UPDATE，INSERT）。数据控制语言(DCL):数据库用户授权，废除用户访问权限，提交当前事务，中止当前事务数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)。Oracle 11g支持的数据类型： 字符类型、数值类型、二进制类型、日期和时间类型、LONG类型、LOB类型。字符类型 1、 Oracle中的字符数据类型包括CHAR、VARCHAR2以及带“N”

4、的相应变体（NCHAR和NVARCHAR2），这些字符数据类型能存储2000字节或4000字节的文本。2、这些文本会由数据库根据需要在不同的字符集（character set）之间进行转换。3、CHAR、VARCHAR2、NCHAR、NVARCHAR2都是以同样的格式进行存储 NLS（国家语言支持）NLS全称是National Language Support。NLS是数据库的一个非常强大的特性，控制着数据的许多方面，最重要的两个方面是：文本数据持久存储在磁盘上时如何编码透明地将数据从一个字符集转换到另一个字符集 CHAR是定长字符串类型，总会用空格填充来达到其最大长度。CHAR字段最多可以存

5、储2000字节的信息。正常情况下，size的单位默认为BYTE。 VARCHAR2是变长字符串类型，不会用空格填充至最大长度。此类型最多可存储4000字节的信息。1、NCHAR/NVARCHAR2两种数据类型用于包含UNICODE格式数据的字符串，以支持某些应用中对多字节数据的需求。两者的存储字符数上界分别是2000/4000字节。2、NVARCHAR2和NCHAR与相应的VARCHAR2和CHAR的区别在于：（1）文本采用数据库的国家字符集来存储和管理，而不是默认字符集（2）长度总是字符数，而CHAR/VARCHAR2可能会指定是字节还是字符。数值类型NUMBERNUMBER(precisi

6、on,scale) （1）精度（precision）也称为总位数，取值范围138，默认值为38，也可以用字符*表示38。（2）小数位数（scale）也称为小数点右边的位数，合法值-48127，默认值取决于是否指定了精度。如果没有指定精度，小数位数则默认有最大的取值区间。如果指定了精度，小数位数默认为0。 判断：num_col字段是否可以存储小数？CREATE TABLE T ( num_col NUMBER);可以如果没有指定精度，小数位数则默认有最大的取值区间，换句话说，定义NUMBER的列会存储浮点数（有小数）。判断：num_col字段是否可以存储小数？CREATE TABLE T ( n

7、um_col NUMBER(5);不可以如果指定小数位数为0，那么相应字段只能存储整数数据（没有小数）。如果输入的是小数，会采取四舍五入的方式来进行截取。判断：num_col字段是否可以存储123456？CREATE TABLE T ( num_col NUMBER(5,0);不可以123456含有6位数字，超过了定义时所规定的精度（5位）。因此，可以使用精度来保证某些数据完整性约束。判断：向num_col插入123.456，Oracle是否会报错？CREATE TABLE T ( num_col NUMBER(5,2);不会小数位数可以用于控制数值的“舍入”。尽管123.456超过了5位，但

8、是Oracle会利用小数位数将123.456”舍入”为只有两位小数，这就得到了123.46，再根据精度来验证123.46，满足精度要求。判断：向num_col插入1234，Oracle是否会报错？CREATE TABLE T ( num_col NUMBER(5,2);会数值1234.00的总位数超过了5位。指定小数位数为2时，小数点左边最多只能有3位，右边有两位。因此，1234超过了定义的精度。NUMBER(5,2)列的存储范围是-999.99999.99。判断：是否可以如下定义num_col字段？CREATE TABLE T ( num_col NUMBER(5,-2);可以将小数位数定义

9、为负值，作用在于允许对小数点左边的值舍入。判断：向num_col插入123.456，数据库会最终存储的数值是多少？CREATE TABLE T ( num_col NUMBER(5,-2); 100就像NUMBER(5,2)将值舍入为最接近的0.01量级一样，NUMBER(5,-2)会把数值舍入为与之最接近的100量级。判断：向num_col插入1234567，Oracle是否会报错？CREATE TABLE T ( num_col NUMBER(5,-2)不会精度指示了舍入后数值允许有多少位，并使用小数位数来确定如何舍入。判断：NUMBER数据类型是定长类型？错。NUMBER类型是一种变长格

10、式，长度为022字节。随着有效数字数字的增加，NUMBER类型需要越来越多的存储空间。每增加两位有效数字，就需要另外一个字节的存储空间。Oracle存储一个数时，会使用尽可能少的内容来表示。为此，它会存储有效数字、用户指定小数点位置的一个指数，以及有关数值符号的信息。特殊值特殊值描述BINARY_FLOAT_NAN非BINARY_FLOAT类型的数字（NaN）BINARY_FLOAT_INFINITYBINARY_FLOAT类型的无穷大（INF）BINARY_DOUBLE_NAN非BINARY_DOUBLE类型的数字（NaN）BINARY_DOUBLE_INFINITYBINARY_DOUBL

11、E类型的无穷大（INF）二进制类型 数据库中以二进制类型存储的二进制数据不会进行字符集转换，而文本类型需要进行字符集转换。 Oracle支持3种数据类型来存储二进制数据 RAW类型能够存储最多2000字节的二进制数据 BLOB类型也即大对象类型 LONG RAW类型此类型是为了支持向后兼容性提供的RAWRAW类型是一个变长度的二进制串，物理上限是2000字节 ，不能存储可直接显示的字符数据。当使用SQL*PLUS等许多工具处理RAW数据时，这些RAW数据会被隐式地转换为一个VARCHAR2类型。判断：对于仅包含一个16字节长RAW类型的表T，通过SQL*Plus，Oracle会如何处理如下所示

12、的命令？能成功执行插入字符abcdef么？INSERT INTO T VALUES( abcdef );1、 会成功执行插入操作，但是插入的数据并不是字符串abcdef，而是一个3字节的RAW数据。2、 SQL*PLUS将RAW类型获取为一个串时，会隐式地调用RAWTOHEX函数，而插入串时会隐式地调用HEXTORAW函数。判断：对于仅包含一个16字节长RAW类型的表T，通过SQL*Plus，Oracle会如何处理如下所示的命令？ INSERT INTO T VALUES( abcdefgh );报错，错误信息为”invalid hex number”。Oracle固有数据类型DATE、TIM

13、ESTAMP和INTERVAL是紧密相关的。1、DATE和TIMESTAMP类型可以存储固定日期/时间。INTERVAL类型可以存储一个时间量。2、TIMESTAMP相对于DATE的两个优势：支持小数秒支持时区判断：DATE类型就是用来存储日期（年月日）的？错DATE类型不仅可以用来存储日期，还存储了有关时间的信息，精度可以到达整数秒。判断：在书写过程中使用的日期格式会以某种方式影响磁盘上存储的数据，并且会影响数据如何存储？错格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用的二进制格式转换为一个串，或者将一个串转换为用于存储DATE的二进制格式，对于TIMESTAMP和INT

14、ERVAL也是如此。日期类型判断：在书写过程中使用的日期格式会以某种方式影响磁盘上存储的数据，并且会影响数据如何存储？错格式对数据如何存储根本没有任何影响。格式只是用于将存储DATE所用的二进制格式转换为一个串，或者将一个串转换为用于存储DATE的二进制格式，对于TIMESTAMP和INTERVAL也是如此。ROWID/UROWID类型 ROWIDROWID是数据库中一行的地址。ROWID中编入了足够多的信息，足以在磁盘上找到行，以及标识ROWID所指向的对象。ROWID并不真正存储在行中，而是行的一个推导属性，基于行的物理地址生成。 Oracle中除了索引组织表（IOT）外的所有行都有ROW

15、ID。ROWID主要用于作为物理地址来减少行更新操作的操作代价，加快访问任何表中某一行的速度。 UROWIDUROWID是ROWID的“近亲”。它用于比如索引组织表（IOT）和通过异构数据库网关访问的没有固定ROWID的表。 UROWID基于行的主键生成，是行主键值的一个表示。但是，UROWID并不作为一个单独的列存在，而只是作为现有列的一个函数。LONG类型Oracle中的LONG类型有两种： LONG文本类型：可以存储2GB的文本 LONG RAW类型：可以存储2GB的原始二进制数据大对象类型Oracle支持LOB或大对象（Large OBject）类型共有4种，分为两类： 存储在数据库中

16、的LOB，也称为内部LOB CLOB NCLOB BLOB 存储在数据库之外的LOB BFILE3.3数据定义语言（DDL 使用create语句创建表CREATE TABLE table_name(column_name datatype null|not null，column_name datatype null|not null，constraint) 注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。 3.5 DML和DQL 添加数据就用insert INSERT INTO table (column , column.) VALUES (value ,

17、 value.); 插入的数据应与字段的数据类型相同。 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 在values中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 插入空值，不指定或insert into table value(null) 删除数据就用delete delete from tbl_name WHERE where_definition 如果不使用where子句，将删除表中所有数据。 Delete语句不能删除某一列的值（可使用update） 使用delete语句仅删除记录，不删除表本身。如要删除

18、表，使用drop table语句。 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。 删除数据 delete from 表名; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢 drop table 表名; 删除表的结构和数据 delete from student where xh=A001; 删除一条记录 truncate table 表名; 删除表中的所有记录,表结构还在,不写日志,无法找回

19、删除的记录,速度快查询数据就用select Select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。 From指定查询哪张表。 DISTINCT可选，指显示结果时，是否剔除重复数据3.6数据控制语言（DCL） Grant Revoke第四章简答题：1、同一SQL语句两次执行差异的原因？ 用户首次执行SQL指令，该指令从磁盘中获取用户连接信息和相关权限信息，并保存在PGA内存中，当用户再次执行该指令时，由于Session未断开连接，连接信息和权限信息在PGA内存中直接获取，避免了物理读。 首次执行SQL结束后，SGA内存区的共享池里已经保存了该SQL唯一指令HASH值，

20、并保留了语法语义检查及执行计划等相关解析动作的劳动成果，当再次执行时，跳过了相关操作，直接拿来使用。 首次执行该SQL指令时，数据一般不在SGA的数据缓存区里（除非被别的SQL读入内存了），只能从磁盘中读取，不可避免地产生物理读，但是由于获取后会保存在数据缓冲区内，再次执行就直接从数据缓冲区里获取了，完全避免了物理读。2、SQL中除了查询语句还有什么语句？ 答: 更新语句。3、新建好的数据库，完成如下三个动作：1 A动作，建立一张表T；2 B动作，插入一条数据进T表；3 C动作，将该数据更新某字段；（1、）ABC三个动作都被记录到日志中，接下来数据库异常，比如T表记录被人误删除了，怎么办？T表

21、整表被删除了，怎么办？结论：Oracle写日志很重要。 LGWR联系日志缓冲区和日志文件。（2）、写日志1,2,3,4，写满后怎么办？ ARCH就是1在被重写 时先备份出去的文件，命名为归档文件。4、Oracle如何兼顾安全和效率（1）日志缓冲区是内存区，怕断电，但是日志文件是永远保存在磁盘中的，不怕断电。（2）Commit时，日志缓冲区一定会要操作的动作写到磁盘的日志文件里，这样Oracle就不一定非要将数据从数据缓冲区写到磁盘了。5、数据缓冲区的数据批量越大越好吗？（1）数据运行过程中，批量刷出的数据占数据缓存区的比例越大，效率一般来说是越高，而且不用担心断电后的恢复问题。（2）如果批量刷

22、出的数据占缓存区的比例很大，断电后恢复数据动作必然需要很长时间，等待的时间就越长。（3）批量刷出的量比较小，性能就会降低，但是断电后恢复的时间就短；反之批量刷出的量大，性能提高，但是断电后恢复的时间也更长。6、更新语句执行完毕后，如果一直不提交，最终会从数据缓存区刷进磁盘吗？会，因为DBWR将数据缓存区数据写到磁盘，不是由COMMIT决定的，而是由CKPT进程决定的。补充： 在CKPT的触发或者命令下，DBWR将数据缓存区数据写到磁盘，但是如果LGWR出现故障了，DBWR此时还是会不听CKPT的命令罢工的，因为Oracle在将数据缓存区数据写到磁盘之前，会先进行日志缓冲区写进日志文件的操作，并

23、耐心等待其优先完成，才会去完成这个内存刷到磁盘的动作。PMON: Processes Monitor进程监视器功能（1）自动回滚操作（用户进程出错，PMON负责清理，回滚相应事务，无需人工rollback）（2）干预后台进程。（如RECO异常失败了，此时PMON会重启RECO进程。如果遇到LGWR进程失败这样的严重问题，PMON会做出终止实例这个激烈的动作，防止数据错乱。）SMON：System Monitor系统监视器功能： 关注系统级别而非单个进程。 重点工作在于intance recovery 清理临时表空间，清理回滚段表空间，合并空间等。 LCKn：LOCK LCKn仅使用于RAC数据

24、库，最多可有10个进程（LCK0LCK9）。 用于实例间的封锁。RECO：Distributed Database Recovery分布式数据库恢复 用于分布式数据库的恢复 适合两段提交场景 例如：面临多个数据库A,B,C,某应用跨越三个数据库，在发起的过程中需要A,B,C库都提交成功，事务才会成功，只要有一个失败，就必须全部回滚。Oracle参数fast_start_mtter_target（1）主要用于触发DBWR从数据缓冲区中写数据到磁盘。（2）CKPT执行越频繁，DBWR写出就越频繁，DBWR写出越频繁越不能显示批量特性，性能就越低，但是数据库异常恢复的时间会比较短。DBWR是Orac

25、le最核心的进程之一。1、负责把数据从数据缓冲区写到磁盘里。2、和CKPT相辅相成的，因为是CKPT促成DBWR去写的。3、与LGWR密切相关，因为DBWR想将数据缓存区中的数据写到磁盘的时候，必须通知LGWR首先完成日志缓冲区写到磁盘的动作，然后才能开工。LGWR1、日志缓冲区内的数据从内存写入到磁盘的redo文件里，完成数据库对象的创建、更新数据等操作过程的记录。2、REDO文件用来做DB的异常恢复，只要这个文件和后续对应文档保护好了，即使数据文件删除光了，也不用担心。3、核心数据库进程。7、数据库的操作1 建表2 插入记录3 修改记录4 删除记录5 增加表字段 6 日志记录为以下情况，恢

26、复的时候会遇到什么问题？（1）（2）（3）记录的时候失败，只记录了（4） LGWR必须记录下所有从数据缓冲区写进数据文件的动作，由于要顺序记录才能保证日志有意义，所以LGWR只能采用单进程。 LGWR制定了5条严格制度来适应日志记录工作：1 每个三秒钟，运行一次。2 任何COMMIT触发LGWR运行一次。3 DBWR要把数据从数据缓存写到磁盘，触发LGWR运行一次。4 日志缓冲区满1/3或记录满1MB，触发LGWR运行一次。5 联机日志文件切换也将触发LGWR。 LGWR写日志需要覆盖重写的时候，触发ARCH进程去转移日志文件，复制出去形成归档日志文件。回滚的研究（1）Oracle数据库只要启

27、动，就会开始触发各种操作，即使用户不主动读写，系统进程也要有操作，联机日志文件中就会不断记录内容。（2）更新语句需要用户确认的一个过程，发出commit将结果保存在数据库中，或者用rollback撤销操作。 8、Oracle是如何保证查询到查询的那个时间点的数据的？这和回滚段有关系，Oracle的回滚段不仅保证了数据的回退，还提供了保证数据库一致读的功能。9、使用多个TableSpace 优点： 用户数据与数据字典数据分离，减少竞争 。 应用程序之间的数据分离，防止某个TableSpace脱机后对多个程序造成影响 。 不同磁盘驱动器上存储数据，减少I/O竞争 。 回滚段数据与用户数据分离，防止

28、单磁盘故障造成数据永久丢失 。 可以控制单个TableSpace脱机，提供更高的整体可用性 。 为特定类型数据库使用保留TableSpace，可优化TableSpace的使用 。 可以备份单独的TableSpace。 10、回滚段表空间和临时表空间建多个的好处？（1）建立多个回滚段的目的是可以瘦身，原先的回滚段一直扩展导致空间浪费很多，先建出来一小点儿，切换成功后删除原来旧的回滚表空间，磁盘空间就空余出来了。（2）临时表空间是为了避免竞争，Oracle可以为每一个用户指定不同的临时表空间，每个临时表空间的数据文件都在磁盘的不同位置上。11、发现存在行迁移的方法（1）首先建立chained_ro

29、ws相关表 SQL?/rdbms/admin/utlchain.sql（2）以下命令针对employees 和employees_bk做分析，将产生行迁移的记录插入到chained_rows表中。 analyze table employees list chained rows into chained_rows; analyze table employees_bk list chained rows into chained_rows（3）分析 Select count(*) from chained_rows where table_name=emoloyees假如目前正在访问empl

30、oyees表，将会产生180个迁移的动作，而该表记录不过才108条。第五章 PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。 PL/SQL是Oracle数据库对SQL语句的扩展。 在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。SQL和PL/SQL的区别： SQL是结构化查询语言，比较接近自然语言，使用SQL，只需要说干什么，不需要说怎么干。由数据定义语言、数据操纵语言、数据控制语言构成，它不面向过程，即前一条语句与后一条语句无关。SQL是标准的语。 PL/SQL，Oracle对SQL标准的扩充，增加了面向过程的功能，所以可以用来编写存储过程、存储函数、触发器等等。 PL/SQL是结构化SQL，就是在标准SQL中加入了IF.ELSE. For.等控制过程的SQL。 PL/SQL是一种