Oracle复习资料.docx
《Oracle复习资料.docx》由会员分享,可在线阅读,更多相关《Oracle复习资料.docx(29页珍藏版)》请在冰豆网上搜索。
Oracle复习资料
Oracle复习资料
1.简答题第一章有一题,第四章有三题,7.8章各一题,第九章两题。
2.编程题涉及到有参数的游标和存储过程。
最后一道编程题,不仅要写出创建存储过程的语句,还得写出来调用存储过程的语句,别忘记了;
第一章
简答题:
Oracle与SQLServer的区别?
答:
1、对OS的支持
–支持Oracle的操作系统有Windows,Linux,Mac;
–支持SQLServer的操作系统只有Windows。
2、数据库的架构
–Oracle中,一个实例只能管理一个数据库,集群下才能实现管理多个数据库;
–SQLServer中是一个实例管理多个数据库。
3、数据库的安全性
–Oracle数据库获得了ISO安全认证;
–SQLServer没有通过安全认证。
4、内存分配
–Oracle中内存分配是由INIT.ORA来决定的;
–SQLServer的内存分配主要有动态和静态内存分配。
第三章
3.1 SQL—数据库沟通的语言标准
3.2 Oracle11g中支持的数据类型
3.3 数据定义语言(DDL)
3.4 约束的使用
3.5 数据操纵语言和数据查询语言
3.6 数据控制语言(DCL)
3.7 小结
SQL—数据库沟通的语言标准
1、什么是SQL
StructuredQueryLanguage,结构化查询语言,是关系数据库的标准语言。
SQL是一个通用的、功能极强的关系数据库语言
2、SQL的种类
数据定义语言(DDL):
数据库、表的创建,修改,删除,声明(CREATE,ALTER,DROP,DECLARE)。
数据操纵语言(DML):
数据表中记录的查询,删除,修改,插入(SELECT,DELETE,UPDATE,INSERT)。
数据控制语言(DCL):
数据库用户授权,废除用户访问权限,提交当前事务,中止当前事务数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK) 。
Oracle11g支持的数据类型:
字符类型、数值类型、二进制类型、日期和时间类型、LONG类型、LOB类型。
字符类型
1、Oracle中的字符数据类型包括CHAR、VARCHAR2以及带“N”的相应变体(NCHAR和NVARCHAR2),这些字符数据类型能存储2000字节或4000字节的文本。
2、这些文本会由数据库根据需要在不同的字符集(characterset)之间进行转换。
3、CHAR、VARCHAR2、NCHAR、NVARCHAR2都是以同样的格式进行存储
•NLS(国家语言支持)
NLS全称是NationalLanguageSupport。
NLS是数据库的一个非常强大的特性,控制着数据的许多方面,最重要的两个方面是:
文本数据持久存储在磁盘上时如何编码
透明地将数据从一个字符集转换到另一个字符集
CHAR是定长字符串类型,总会用空格填充来达到其最大长度。
CHAR字段最多可以存储2000字节的信息。
正常情况下,size的单位默认为BYTE。
VARCHAR2是变长字符串类型,不会用空格填充至最大长度。
此类型最多可存储4000字节的信息。
1、NCHAR/NVARCHAR2
两种数据类型用于包含UNICODE格式数据的字符串,以支持某些应用中对多字节数据的需求。
两者的存储字符数上界分别是2000/4000字节。
2、NVARCHAR2和NCHAR与相应的VARCHAR2和CHAR的区别在于:
(1)文本采用数据库的国家字符集来存储和管理,而不是默认字符集
(2)长度总是字符数,而CHAR/VARCHAR2可能会指定是字节还是字符。
数值类型
NUMBER
NUMBER(precision,scale)
(1)精度(precision)
也称为总位数,取值范围1~38,默认值为38,也可以用字符*表示38。
(2)小数位数(scale)
也称为小数点右边的位数,合法值-48~127,默认值取决于是否指定了精度。
如果没有指定精度,小数位数则默认有最大的取值区间。
如果指定了精度,小数位数默认为0。
判断:
num_col字段是否可以存储小数?
CREATETABLET(num_colNUMBER);
可以
如果没有指定精度,小数位数则默认有最大的取值区间,换句话说,定义NUMBER的列会存储浮点数(有小数)。
判断:
num_col字段是否可以存储小数?
CREATETABLET(num_colNUMBER(5));
不可以
如果指定小数位数为0,那么相应字段只能存储整数数据(没有小数)。
如果输入的是小数,会采取四舍五入的方式来进行截取。
判断:
num_col字段是否可以存储123456?
CREATETABLET(num_colNUMBER(5,0));
不可以
123456含有6位数字,超过了定义时所规定的精度(5位)。
因此,可以使用精度来保证某些数据完整性约束。
判断:
向num_col插入123.456,Oracle是否会报错?
CREATETABLET(num_colNUMBER(5,2));
不会
小数位数可以用于控制数值的“舍入”。
尽管123.456超过了5位,但是Oracle会利用小数位数将123.456”舍入”为只有两位小数,这就得到了123.46,再根据精度来验证123.46,满足精度要求。
判断:
向num_col插入1234,Oracle是否会报错?
CREATETABLET(num_colNUMBER(5,2));
会
数值1234.00的总位数超过了5位。
指定小数位数为2时,小数点左边最多只能有3位,右边有两位。
因此,1234超过了定义的精度。
NUMBER(5,2)列的存储范围是-999.99~999.99。
判断:
是否可以如下定义num_col字段?
CREATETABLET(num_colNUMBER(5,-2));
可以
将小数位数定义为负值,作用在于允许对小数点左边的值舍入。
判断:
向num_col插入123.456,数据库会最终存储的数值是多少?
CREATETABLET(num_colNUMBER(5,-2));
100
就像NUMBER(5,2)将值舍入为最接近的0.01量级一样,NUMBER(5,-2)会把数值舍入为与之最接近的100量级。
判断:
向num_col插入1234567,Oracle是否会报错?
CREATETABLET(num_colNUMBER(5,-2))
不会
精度指示了舍入后数值允许有多少位,并使用小数位数来确定如何舍入。
判断:
NUMBER数据类型是定长类型?
错。
NUMBER类型是一种变长格式,长度为0~22字节。
随着有效数字数字的增加,NUMBER类型需要越来越多的存储空间。
每增加两位有效数字,就需要另外一个字节的存储空间。
Oracle存储一个数时,会使用尽可能少的内容来表示。
为此,它会存储有效数字、用户指定小数点位置的一个指数,以及有关数值符号的信息。
特殊值
特殊值
描述
BINARY_FLOAT_NAN
非BINARY_FLOAT类型的数字(NaN)
BINARY_FLOAT_INFINITY
BINARY_FLOAT类型的无穷大(INF)
BINARY_DOUBLE_NAN
非BINARY_DOUBLE类型的数字(NaN)
BINARY_DOUBLE_INFINITY
BINARY_DOUBLE类型的无穷大(INF)
二进制类型
•数据库中以二进制类型存储的二进制数据不会进行字符集转换,而文本类型需要进行字符集转换。
•Oracle支持3种数据类型来存储二进制数据
–RAW类型
能够存储最多2000字节的二进制数据
–BLOB类型
也即大对象类型
–LONGRAW类型
此类型是为了支持向后兼容性提供的
RAW
RAW类型是一个变长度的二进制串,物理上限是2000字节,不能存储可直接显示的字符数据。
当使用SQL*PLUS等许多工具处理RAW数据时,这些RAW数据会被隐式地转换为一个VARCHAR2类型。
判断:
对于仅包含一个16字节长RAW类型的表T,通过SQL*Plus,Oracle会如何处理如下所示的命令?
能成功执行插入字符‘abcdef’么?
INSERTINTOTVALUES(‘abcdef’);
1、会成功执行插入操作,但是插入的数据并不是字符串‘abcdef’,而是一个3字节的RAW数据。
2、SQL*PLUS将RAW类型获取为一个串时,会隐式地调用RAWTOHEX函数,而插入串时会隐式地调用HEXTORAW函数。
判断:
对于仅包含一个16字节长RAW类型的表T,通过SQL*Plus,Oracle会如何处理如下所示的命令?
INSERTINTOTVALUES(‘abcdefgh’);
报错,错误信息为”invalidhexnumber”。
Oracle固有数据类型DATE、TIMESTAMP和INTERVAL是紧密相关的。
1、DATE和TIMESTAMP类型可以存储固定日期/时间。
INTERVAL类型可以存储一个时间量。
2、TIMESTAMP相对于DATE的两个优势:
支持小数秒
支持时区
判断:
DATE类型就是用来存储日期(年月日)的?
错
DATE类型不仅可以用来存储日期,还存储了有关时间的信息,精度可以到达整数秒。
判断:
在书写过程中使用的日期格式会以某种方式影响磁盘上存储的数据,并且会影响数据如何存储?
错
格式对数据如何存储根本没有任何影响。
格式只是用于将存储DATE所用的二进制格式转换为一个串,或者将一个串转换为用于存储DATE的二进制格式,对于TIMESTAMP和INTERVAL也是如此。
日期类型
判断:
在书写过程中使用的日期格式会以某种方式影响磁盘上存储的数据,并且会影响数据如何存储?
错
格式对数据如何存储根本没有任何影响。
格式只是用于将存储DATE所用的二进制格式转换为一个串,或者将一个串转换为用于存储DATE的二进制格式,对于TIMESTAMP和INTERVAL也是如此。
ROWID/UROWID类型
•ROWID
ROWID是数据库中一行的地址。
ROWID中编入了足够多的信息,足以在磁盘上找到行,以及标识ROWID所指向的对象。
ROWID并不真正存储在行中,而是行的一个推导属性,基于行的物理地址生成。
•Oracle中除了索引组织表(IOT)外的所有行都有ROWID。
ROWID主要用于作为物理地址来减少行更新操作的操作代价,加快访问任何表中某一行的速度。
•UROWID
UROWID是ROWID的“近亲”。
它用于比如索引组织表(IOT)和通过异构数据库网关访问的没有固定ROWID的表。
•UROWID基于行的主键生成,是行主键值的一个表示。
但是,UROWID并不作为一个单独的列存在,而只是作为现有列的一个函数。
LONG类型
Oracle中的LONG类型有两种:
–LONG文本类型:
可以存储2GB的文本
–LONGRAW类型:
可以存储2GB的原始二进制数据
大对象类型
Oracle支持LOB或大对象(LargeOBject)类型共有4种,分为两类:
–存储在数据库中的LOB,也称为内部LOB
•CLOB
•NCLOB
•BLOB
–存储在数据库之外的LOB
•BFILE
3.3数据定义语言(DDL
•使用create语句创建表
CREATETABLEtable_name
(
column_namedatatype[null|notnull],
column_namedatatype[null|notnull],
…
[constraint]
)
•注意:
创建表时,要根据需保存的数据创建相应的列,并根据数据的类型定义相应的列类型。
3.5DML和DQL
•添加数据就用insert
•INSERTINTOtable[(column[,column...])]
•VALUES(value[,value...]);
•插入的数据应与字段的数据类型相同。
•数据的大小应在列的规定范围内,例如:
不能将一个长度为80的字符串加入到长度为40的列中。
•在values中列出的数据位置必须与被加入的列的排列位置相对应。
•字符和日期型数据应包含在单引号中。
•插入空值,不指定或insertintotablevalue(null)
•删除数据就用delete
deletefromtbl_name
[WHEREwhere_definition]
•如果不使用where子句,将删除表中所有数据。
•Delete语句不能删除某一列的值(可使用update)
•使用delete语句仅删除记录,不删除表本身。
如要删除表,使用droptable语句。
•同insert和update一样,从一个表中删除记录将引起其它表的参照完整性问题,在修改数据库数据时,头脑中应该始终不要忘记这个潜在的问题。
•删除表中数据也可使用TRUNCATETABLE语句,它和delete有所不同,参看mysql文档。
•删除数据
•deletefrom表名;
•删除所有记录,表结构还在,写日志,可以恢复的,速度慢
•droptable表名;删除表的结构和数据
•deletefromstudentwherexh='A001';删除一条记录
•truncatetable表名;
•删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快
查询数据就用select
•Select指定查询哪些列的数据。
•column指定列名。
•*号代表查询所有列。
•From指定查询哪张表。
•DISTINCT可选,指显示结果时,是否剔除重复数据
3.6数据控制语言(DCL)
GrantRevoke
第四章
简答题:
1、同一SQL语句两次执行差异的原因?
•用户首次执行SQL指令,该指令从磁盘中获取用户连接信息和相关权限信息,并保存在PGA内存中,当用户再次执行该指令时,由于Session未断开连接,连接信息和权限信息在PGA内存中直接获取,避免了物理读。
•首次执行SQL结束后,SGA内存区的共享池里已经保存了该SQL唯一指令HASH值,并保留了语法语义检查及执行计划等相关解析动作的劳动成果,当再次执行时,跳过了相关操作,直接拿来使用。
•首次执行该SQL指令时,数据一般不在SGA的数据缓存区里(除非被别的SQL读入内存了),只能从磁盘中读取,不可避免地产生物理读,但是由于获取后会保存在数据缓冲区内,再次执行就直接从数据缓冲区里获取了,完全避免了物理读。
2、SQL中除了查询语句还有什么语句?
答:
更新语句。
3、新建好的数据库,完成如下三个动作:
1A动作,建立一张表T;
2B动作,插入一条数据进T表;
3C动作,将该数据更新某字段;
(1、)ABC三个动作都被记录到日志中,接下来数据库异常,比如T表记录被人误删除了,怎么办?
T表整表被删除了,怎么办?
结论:
Oracle写日志很重要。
LGWR联系日志缓冲区和日志文件。
(2)、写日志1,2,3,4,写满后怎么办?
•ARCH就是1在被重写时先备份出去的文件,命名为归档文件。
4、Oracle如何兼顾安全和效率
(1)日志缓冲区是内存区,怕断电,但是日志文件是永远保存在磁盘中的,不怕断电。
(2)Commit时,日志缓冲区一定会要操作的动作写到磁盘的日志文件里,这样Oracle就不一定非要将数据从数据缓冲区写到磁盘了。
5、数据缓冲区的数据批量越大越好吗?
(1)数据运行过程中,批量刷出的数据占数据缓存区的比例越大,效率一般来说是越高,而且不用担心断电后的恢复问题。
(2)如果批量刷出的数据占缓存区的比例很大,断电后恢复数据动作必然需要很长时间,等待的时间就越长。
(3)批量刷出的量比较小,性能就会降低,但是断电后恢复的时间就短;反之批量刷出的量大,性能提高,但是断电后恢复的时间也更长。
6、更新语句执行完毕后,如果一直不提交,最终会从数据缓存区刷进磁盘吗?
会,因为DBWR将数据缓存区数据写到磁盘,不是由COMMIT决定的,而是由CKPT进程决定的。
补充:
在CKPT的触发或者命令下,DBWR将数据缓存区数据写到磁盘,但是如果LGWR出现故障了,DBWR此时还是会不听CKPT的命令罢工的,因为Oracle在将数据缓存区数据写到磁盘之前,会先进行日志缓冲区写进日志文件的操作,并耐心等待其优先完成,才会去完成这个内存刷到磁盘的动作。
PMON:
ProcessesMonitor进程监视器
功能
(1)自动回滚操作
(用户进程出错,PMON负责清理,回滚相应事务,无需人工rollback)
(2)干预后台进程。
(如RECO异常失败了,此时PMON会重启RECO进程。
如果遇到LGWR进程失败这样的严重问题,PMON会做出终止实例这个激烈的动作,防止数据错乱。
)
SMON:
SystemMonitor系统监视器
功能:
–关注系统级别而非单个进程。
–重点工作在于intancerecovery
–清理临时表空间,清理回滚段表空间,合并空间等。
LCKn:
LOCK
•LCKn仅使用于RAC数据库,最多可有10个进程(LCK0……LCK9)。
•用于实例间的封锁。
RECO:
DistributedDatabaseRecovery分布式数据库恢复
•用于分布式数据库的恢复
•适合两段提交场景
•例如:
面临多个数据库A,B,C,某应用跨越三个数据库,在发起的过程中需要A,B,C库都提交成功,事务才会成功,只要有一个失败,就必须全部回滚。
Oracle参数fast_start_mtter_target
(1)主要用于触发DBWR从数据缓冲区中写数据到磁盘。
(2)CKPT执行越频繁,DBWR写出就越频繁,DBWR写出越频繁越不能显示批量特性,性能就越低,但是数据库异常恢复的时间会比较短。
DBWR是Oracle最核心的进程之一。
1、负责把数据从数据缓冲区写到磁盘里。
2、和CKPT相辅相成的,因为是CKPT促成DBWR去写的。
3、与LGWR密切相关,因为DBWR想将数据缓存区中的数据写到磁盘的时候,必须通知LGWR首先完成日志缓冲区写到磁盘的动作,然后才能开工。
LGWR
1、日志缓冲区内的数据从内存写入到磁盘的redo文件里,完成数据库对象的创建、更新数据等操作过程的记录。
2、REDO文件用来做DB的异常恢复,只要这个文件和后续对应文档保护好了,即使数据文件删除光了,也不用担心。
3、核心数据库进程。
7、数据库的操作
1建表
2插入记录
3修改记录
4删除记录
5增加表字段
6……
日志记录为以下情况,恢复的时候会遇到什么问题?
(1)①③②④⑤
(2)②③①④⑤
(3)记录②的时候失败,只记录了①③④⑤
(4)②③④⑤
•LGWR必须记录下所有从数据缓冲区写进数据文件的动作,由于要顺序记录才能保证日志有意义,所以LGWR只能采用单进程。
•LGWR制定了5条严格制度来适应日志记录工作:
1每个三秒钟,运行一次。
2任何COMMIT触发LGWR运行一次。
3DBWR要把数据从数据缓存写到磁盘,触发LGWR运行一次。
4日志缓冲区满1/3或记录满1MB,触发LGWR运行一次。
5联机日志文件切换也将触发LGWR。
•LGWR写日志需要覆盖重写的时候,触发ARCH进程去转移日志文件,复制出去形成归档日志文件。
回滚的研究
(1)Oracle数据库只要启动,就会开始触发各种操作,即使用户不主动读写,系统进程也要有操作,联机日志文件中就会不断记录内容。
(2)更新语句需要用户确认的一个过程,发出commit将结果保存在数据库中,或者用rollback撤销操作。
8、Oracle是如何保证查询到查询的那个时间点的数据的?
这和回滚段有关系,Oracle的回滚段不仅保证了数据的回退,还提供了保证数据库一致读的功能。
9、使用多个TableSpace优点:
•用户数据与数据字典数据分离,减少竞争。
•应用程序之间的数据分离,防止某个TableSpace脱机后对多个程序造成影响。
•不同磁盘驱动器上存储数据,减少I/O竞争。
•回滚段数据与用户数据分离,防止单磁盘故障造成数据永久丢失。
•可以控制单个TableSpace脱机,提供更高的整体可用性。
•为特定类型数据库使用保留TableSpace,可优化TableSpace的使用。
•可以备份单独的TableSpace。
10、回滚段表空间和临时表空间建多个的好处?
(1)建立多个回滚段的目的是可以瘦身,原先的回滚段一直扩展导致空间浪费很多,先建出来一小点儿,切换成功后删除原来旧的回滚表空间,磁盘空间就空余出来了。
(2)临时表空间是为了避免竞争,Oracle可以为每一个用户指定不同的临时表空间,每个临时表空间的数据文件都在磁盘的不同位置上。
11、发现存在行迁移的方法
(1)首先建立chained_rows相关表
SQL>@?
/rdbms/admin/utlchain.sql
(2)以下命令针对employees和employees_bk做分析,将产生行迁移的记录插入到chained_rows表中。
analyzetableemployeeslistchainedrowsintochained_rows;
analyzetableemployees_bklistchainedrowsintochained_rows
(3)分析
Selectcount(*)fromchained_rows
wheretable_name=‘emoloyees’
假如目前正在访问employees表,将会产生180个迁移的动作,而该表记录不过才108条。
第五章
•PL/SQL也是一种程序语言,叫做过程化SQL语言(ProceduralLanguage/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是一种