oracle面试题.docx
《oracle面试题.docx》由会员分享，可在线阅读，更多相关《oracle面试题.docx（40页珍藏版）》请在冰豆网上搜索。
oracle面试题
Oracle数据库的主要特点:
支持多用户大量务量的事务处理
数据安全性和数据完整性
支持分布式数据处理
可移植性
支持工业标准的操作系统
Oracle数据库基于客户端/服务器技术
客户端应用程序通过向服务器请求并接收信息的方式与数据库进行交互充当用户和数据库之间的接口
在多个客户端同时向服务器请求同一数据时数据库服务器或后端应用程序可对数据库进行最佳的管理它还对所有客户端应用程序强制保持强制数据的完整并控制对数据库的访问和其他安全要求
SHOWSGA查看SGA的大小
创建表空间
CREATETABLESPACEts
DATAFILE'E:
\oracle\oradata\accp\ts_01.dbf'
SIZE50M;
创建用户
CREATEUSERACCP
IDENTIFIEDBYACCP
DEFAULTTABLESPACEUSERS;
授予可传递的权限
GRANTSELECTONEMPTOACCPWITHGRANTOPTION;
彻底清除Oracle软件
1.删除Oracle注册表信息
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle
2.删除Oracle服务
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
3.删除事件日志
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application
4.删除Oracle环境变量
控制面板->系统->高级->环境变量删除相关
5.删除Oracle菜单
开始->程序->Oracle-OraHome92
6.删除ProgramFile\Oracle目录
7.重启Windows
8.删除Oracle主目录
练习
创建一个名称为DAVID的用户口令为davidpass并授予其连接数据库和创建表对象的权限同时授予其访问另一个用户MARTIN的表TEST的权限
以SYSTEM身份登录SQL*Plus
CREATEUSERDAVID
IDENTIFIEDBYdavidpass
DEFAULTTABLESPACEuser;
授予登录数据库和创建表的权限
GRANTCREATESESSIONTODAVID;
GRANTCREATETABLETODAVID;
授予使用表空间的权限
ALTERUSERDAVIDQUOTAUNLIMITEDONUSERS;
以MARTIN身份登录SQL*Plus授予DAVID对表TEST的权限
GRANTSELECTONTESTTODAVID;
GRANTUPDATEONTESTTODAVID;
系统全局区（SGA）的内存结构
Oracle实例启动时会分配该内存区SGA是构成Oracle实例的一个基本组件数据库信息存储在SGA中并由多个数据库进行共享该区用于存储信息的空间是从装有Oracle的计算机虚拟内存中分配的SGA有多个内存结构如共享池数据缓冲区及日志缓冲区决定SGA大小的一些初始化参数包括DB_CACHE_SIZELOG_BUFFER和SHARED_POOL_SIZE.
Oracle的用户
当创建一个数据库时Oracle将创建几个默认的数据库用户模式如SYSSYSTEM和SCOTT用户SYS是Oracle中的超级用户它的默认口令是change_on_install数据库中数据字典的所有表和视图都存储在SYS模式中这些表和视图对Oracle的操作非常重要用户SYSTEM也有管理员的权限它的默认口令是manager该用户创建显示管理信息的表和视图以及Oracle工具所使用的4个示范表其中一个是EMP表SCOTT的默认口令是tiger
查询ROWID伪列
SELECTROWID,T.*FROMEMPT;
SELECT*FROMEMPWHEREROWID=:
VARIBLE;
删除表时同时删除相关的约束和索引
DROPTABLEtable_nameCASCADECONSTRAINTS;
临时改变会话的默认日期格式
ALTERSESSIONsetnls_date_format='YYYY-MM-DD';
'AMERICAN'->12-MAY-05
'SIMPLIFIEDCHINESE'->12-5月-05
使用DATE关键字把字符串表示日期值
SELECTDATE'2005-10-25'FROMDUAL;
查看当前系统时间
SELECTSYSDATEFROMDUAL;
运算符IN的用法
SELECTENAMEFROMEMPWHEREEMPNOIN（7788,7369,7900）;
将日期四舍五入为最接近的那一天
SELECTdateofjoiningROUND（MONTHS_BETWEEN（CURRENT_DATE,dateofjoining））FROMemployee;
提取日期时间类型的特定部分
SELECTEXTRACT（MONTHFROMSYSDATE）FROMDUAL;
Oracle支持字符数值日期时间RAWLONGROW和LOB等数据类型
Oracle的数据定义语言命令
CREATEALTERDROP
DMLDataManipulationLanguage（数据操纵语言）
用于检索插入和修改数据库信息
包括SELECTINSERTUPDATE和DELETE
事务控制语言
COMMITSAVEPOINT和ROLLBACK都是事务控制命令事务以执行更新操作的SQL语句开始并以ROLLBACK或COMMIT语句显式结束但使用DDL语句时事务处理将隐式地自动结束
连接操作符用于将两个以上的字符串或者一个字符串与一个数据值合并
单行函数或标量函数对于在表中查询的每一行只返回一个值
单行函数可用在SELECT子句中也可用在WHERE子句中
锁的优点
一致性一次只允许一个用户更改数据
完整性一个用户进行了更改并保存相应的更改将反映给所有用户
并行性允许多个用户访问同一数据
表级锁定
LOCKTABLEdeptINSHAREMODE;
错误ORA-00060:
等待资源时检测到死锁
分区优点
简化维护操作允许用户对一个分区而非整个表执行维护操作
改进性能允许用户决定存储表分区的位置
提高可维护性用户可以只备份和恢复特定的分区
范围分区根据表中的值的范围进行分区
散列分区对不需要包含逻辑值的数据进行分区
列表分区允许对不相关的数据进行分区
复合分区范围分区和散列分区的结合
在表中应该行级锁定之后其他用户访问未被事务锁定的任何行要访问已被锁定的行用户必须等待锁被释放
在对列进行分区或子分区时列表最多可以包含16列
模式是数据库的整体结构
对象可以为表视图程序包序列同义词和索引
表是数据库的基本对象
同义词是数据库对象的别名
用于隐藏对象的名称和所有者
创建视图所依赖的表称为基表
视图隐藏了数据的复杂性简化了用户的SQL命令
视图将应用程序与基表定义的修改分隔开来
视图通过重命名列相对于基表提供了数据不影响基表
使用WITHCHECKOPTION选项创建视图确保对视图执行更新操作后的行满足视图指定的WHERE子句的约束条件
创建带有错误的视图
定义的查询引用了一个不存在的表或现有表中的无效的列
视图的所有者没有所需的权限
联接视图上DML语句只能修改了一个基表
视图只能在作为键保留表时才能更新
Oracle索引便于更快地访问数据
索引是与表关联的一种可选结构
创建索引可以明显加快在表上执行SQL语句的速度
当在SQL语句与WHERE子句中使用索引键值列时该索引将直接指向包含这些值的行的位置合理使用索引是减少磁盘I/O的主要方法
CREATEINDEX语句用于在表的一个或多个列上创建索引
不管索引是否存在都无需修改任何SQL语句的定义
索引只是一种快速访问数据的途径它只影响执行的速度
一旦创建了索引则无需再对索引执行任何操作Oracle会自动完成所需的维护操作
唯一索引可以确保在定义索引的列中表的任意两行的值都不相同
Oracle自动为表的主键或唯一键列创建唯一索引
使用CREATEUNIQUEINDEX命令来明确地创建唯一索引
组合索引在表上的两个或多个列上共同创建索引
组合索引中列的顺序是任意的也无须是表中相邻的列
SELECT语句的WHERE子句中引用了组合索引中所有或大多数列可以提高数据检索速度
在创建索引时定义使用的列的顺序应该将最频繁访问的列放在最前面
反向索引反转索引列中的每一个字节同时要保持列的顺序
如果对索引的修改仅集中在少数几块上则这种排列有助于避免索引性能的降低
通过反转索引的键可以将插入操作分布在整个索引上
REVERSE与CREATEINDEX命令一起使用可以创建反向键索引
ALTERINDEX命令和REBUILDNOREVERSE选项将反向键索引转换为标准索引
不能将标准索引重建为反向索引
位图索引适用于列中的值的数目比表的行数少的列
CREATEBITMAPINDEX命令来创建位图索引
使用ORGANZATIONINDEX子句表示创建的表为索引组织表
基于函数的索引可用与提高WHERE子句中的使用此函数的查询的检索速度
局部分区索引使用LOCAL关键字
全局分区索引使用GLOCAL关键字
全局非分区索引同标准索引不是使用关键字
公有同义词可被同一模式的用户以及其他模式的用户访问
私有同义词只能被同一模式的用户访问同时应具有唯一的名称
创建视图的语法结构
CREATE[ORREPLACE][FORCE]VIEW
view_name[（alias[,alias]...）]
ASselect_statement
[WITHCHECKOPTION]
[WITHREADONLY];
Oracle支持的索引的类型
唯一索引
组合索引
位图索引
反向键索引
基于函数的索引
游标是在显示结果前将检索到的行存储在其中的内存位置
使用PL/SQL的优点
支持SQL所有的数据类型和NULL值与SQL集成提供了SQL的大部分功能简化对Oracle数据的操作
支持OOP提供了面向对象编程工具节省了构建复杂应用程序所消耗的成本和时间PL/SQL还允许将SQL语句和代码块组合在一起并将其发送给
以可执行的形式存储经过编译的存储过程能够快速有效地调用过程
可以移植到任何运行Oracle的操作系统上使用
最重要的是要保护数据数据安全性方法就是禁止用户直接操作敏感的数据受限访问重要的数据
PL/SQL块分为声明可执行和异常处理3个部分
声明块中使用的变量游标和自定义异常局部子程序
可执行部分必需的操作声明部分中声明的变量不允许执行DDL语句
异常处理部分指定发生错误时要执行的操作
标量数据类型不含内部组件标量数据类型包括数字字符BOOLEAN和DATE等数据类型
PL/SQL接受作为输入的PL/SQL块或子程序并对其进行编译PL/SQL再使用运行时系统来执行程序
PL/SQL块的执行过程
PL/SQL块发送到Oracle服务器Oracle服务器将接受输入发送到其中的一个过程语句执行程序执行之后将输出结果发送给用户
在可执行语句或表达式中引用变量和常量前必须先对其进行声明
标量LOB和属性类型都是PL/SQL块中使用的数据类型
LOB数据类型用于存储非结构化数据块包括文本图形图像视频剪辑和声音波形
属性类型
%TYPE将某种数据类型的变量或列提供给其他变量
%ROWTYPE提供一种表示表中的某行的记录类型
顺序控制
GOTO语句将控制全转给GOTO语句中指定的标签名称后面的可执行语句
NULL语句用于将控制权转给下一条语句
动态SQL就是在PL/SQL程序执行中生成SQL语句字符串然后执行
PL/SQL中不能直接使用DDL语句
动态SQL可以执行DDL和会话控制语句（ALTERSESSION）
通过EXECUTEIMMEDIATE命令执行动态SQL只能用于处理返回单行或没有返回的SQL语句要处理返回多行的动态SQL应该使用REF游标的OPEN...FOR语句
还可以使用内置包DBMS_SQL来执行动态SQL它可以获得更多的执行性能但是使用起来比较繁琐
使用OTHERS关键字可以捕捉任何类型的异常以处理运行时引发的错误
错误处理
在执行程序期间出现扰乱程序正常流程的运行时的错误
DUP_VAL_ON_INDEX违反唯一索引
INVALID_NUMBER数字转换异常
NO_DATA_FOUND不存在请求行
TOO_MANY_ROWS返回多条记录行
ZERO_DIVIDE除零异常
自定义异常是用户在PL/SQL块或子程序的声明部分定义的异常
使用RAISE语句显式引发自定义异常
自定义异常使用与PL/SQL引擎处理错误相同的方式来定制错误处理代码可以获得直观的可管理的代码
RAISE_APPLICATION_ERROR用于自定义异常的错误信息将其报告给PL/SQL子程序或客户端应用程序
游标是PL/SQL程序中用来获得查询结果集的指针
执行SQL语句在处理检索到的行之前会先将这些行存储在内存中游标指向这个内存位置从而逐行访问记录集
Oracle使用内存的某部分来存储和更新检索到的行
可以以编程的方式访问数据从而完成需要分别在结果集中每个记录上执行的过程代码的任务
Oracle支持的游标类型
隐式游标检索一行
显式游标检索多行
REF游标动态绑定查询语句与游标变量一起使用
游标的4种属性%FOUND%NOTFOUND%ROWCOUNT和%ISOPEN
隐式游标
执行PL/SQL块时自动声明自动与正在执行的DML语句像关联
主要用于管理INSERTDELETE和UPDATE语句以及返回单行的SELECTINTO语句
隐式游标的%ISOPEN属性始终为假因为会自动关闭
显式游标管理步骤
声明游标
打开游标
提取记录
关闭游标
控制显式游标
OPEN打开游标执行查询标识结果集和将游标置于第一行之前
FETCH检索结果集的行并将游标向前移动一行
CLOSE处理完所有行之后关闭游标
声明带参数的显式游标可以增强游标的灵活性
在声明游标之后就会立即传递参数
在WHERE子句中的条件得到满足将显示这些行
在SELECT语句中使用FORUPDATE子句声明显式游标更新行
循环游标可以简化游标管理的代码它自动打开游标提取行和关闭游标
如果要检索查询返回的所有记录则可以使用循环游标
循环游标每次只检索一次
游标变量用于处理运行时动态决定的查询声明REF游标不需要指定SELECT查询语句使用OPENFOR语句可以打开游标变量
游标变量使用的相应限制
不能在程序包中声明游标变量
远程子程序不能接受游标变量的值
不能使用比较操作符对游标变量进行相等或不相等测试
不能将空值赋予游标变量
数据列无法存储游标变量的值
EXECUTEIMMEDIATE语句只能用户处理返回单行或没有返回的SQL语句REF游标则可以处理返回结果集的动态SQL
隐式游标和显式游标的区别
隐式游标在执行SQL语句时由Oracle自动生成用户不能控制隐式游标隐式游标用与检索一行
显式游标由用户显式声明用户对显式游标有完全控制权显式游标用于返回多行的查询
显式游标的属性
%ISOPEN如果游标已打开则返回true否则返回false
%FOUND如果成功处理了最后一行则返回true
%NOTFOUND如果提取最后一行失败则返回true
%ROWCOUNT返回处理的行数
控制显式游标所用的语句
OPEN执行查询标识结果集并将游标置于第一行之前
FETCH检索当前行并将游标向前移动一行
CLOSE在处理完最后一行之后关闭游标
子程序的3个部分包括声明部分可执行部分和异常处理部分
子程序的优点
将代码分割成小模块
带吗可重用和维护
可以提供安全性
过程用于系统地完成特定任务的一组有序指令
过程参数模式3种
参数列表中的默认值赋给IN参数
使用EXECUTE语句和过程名在PL/SQL程序中调用过程
函数与过程类似只是函数会返回值
RETURN子句在定义函数时指示返回值的数据类型
过程和函数的比较
过程和函数都是子程序的类型
过程和函数的执行方式是不同的
过程是作为PL/SQL语句执行的
函数是作为表达式的一部分被调用的
RETURN子句只在函数中指定而且必须这样做
过程不使用RETURN语句返回值但函数必须始终返回值
过程可以具有RETURN语句但是函数必须存在RETURN语句
自动事务处理是由主事务处理启动的启动自主事务处理之后将暂停主事务处理
自主事务处理SQL操作最后通过恢复主事务处理来终止自主事务处理PARGMAAUTONOMOUS_TRANSACTION语句包括在子程序的声明部分中
将该子程序标记为自主的
自主事务处理不依赖于主事务处理的状态
自主事务处理中的ROLLBACK和COMMIT命令的使用不影响主事务处理的结果
自主事物处理能够启动另一个自主事务处理
包可以是过程函数变量和SQL的集合
包规范:
声明类型变量常量异常游标和子程序
包主体:
实现在包规范中声明的子程序和游标查询这些实现对应用程序是隐藏的
如果创建子程序或程序包出现错误可以使用SHOWERRORS命令查看详细信息
程序包的优点
封装相关对象
模块化将程序分解为明确定义的逻辑模块的过程
可以轻松地对设计应用程序允许用户只对规范而不是对主体进行编码和编译
程序包主体中定义的私有子程序对其他程序包隐藏了其定义
首次调用打包的子程序时整个程序包均将加载到内存中后续的调用不需要磁盘I/O因此性能不错
程序包需要将游标规范和游标主体分开编写这样允许在更改游标主体时无需改变游标规范
在编写游标主体时应使用与游标规范相同的RETURN子句和SELECT语句
在程序包主体中定义了游标主体并在程序包的子程序中打开和使用了游标
使用程序包中的REF的游标从Oracle存储过程返回记录集Oracle的存储过程本来不能返回值
但是可以利用INOUT模式的REF游标变量参数返回结果集
USER_OBJECTS数据字典视图包含与当前用户创建的所有对象相关的数据
USER_SOURCE数据字典视图可以获得存储子过程的文本
过程和函数的区别
过程与函数类似只是函数有个RETURN子句该子句在函数规范中指示变量值的数据类型
程序包中的公有项和私有项
项是指子程序类型变量和常量
声明为公有的项可以在程序包之外使用声明为使用的项不能在程序包之外使用公有项通常是在程序包规格说明中声明在程序包主体中定义的
而私有项只能在程序包主体中声明和定义公有项可以用于全局目的而私有项只能用于局部目的只能在程序包内使用
触发器
在特定事件出现时自动执行的存储过程
是在用户执行INSERTDELETE和UPDATE等更新表或视图的DML语句或DDL语句时隐式运行的PL/SQL过程
触发器的功能
自动生成数据
强制复杂的完整性约束条件
自定义复杂的安全权限
提供审计和日志记录
应用复杂的业务逻辑
触发器组成部分
触发器语句
触发器定时BEFORE/AFTER
触发器事件INSERTUPDATEDELETE
表名表/视图
触发器类型行级/语句级
触发器限制
根据在WHEN字句中指定的条件限制触发器的执行若为假则不激活触发器
触发器操作
包含只有在激活触发器时才会执行的SQL语句和代码
ALTER触发器在用户对表执行任意DML或DDL事件并将更新的值存储在Oracle数据库之后激活
BEFORE触发器与ALTER触发器则正好相反它在将更新的值存储在Oracle数据库之前激活
DML触发器当DML语句在表中发生时执行
语句级触发器无论受触发语句影响的行数是多少都只激活一次即使没有行受影响也会激活语句触发器
行级触发器每当触发器语句影响表时就会激活行级触发器在被修改的每一行上执行一次若不存在影响行则不激活行触发器
INSTRADOF触发器允许用户修改不能使用DML语句修改的视图只可用于视图不可用于表
模式触发器在数据库模式中执行DDL语句时激活
数据库级触发器在发生LOGOFF（退出）LOGON（登录）STARTUP（打开）SHUTDOWN（关闭）数据库和SERVERROR（生成错误消息）等系统事件时执行
禁止用户执行特定的操作则使用RAISE_APPLICATION_ERROR过程抛出异常触发器会自动停止操作并回滚以前的更新
不要在触发器中使用COMMIT和ROLLBACK语句触发器与触发它的语句在同一事务中
数据库级触发器通常用于解决特殊问题只有数据库管理员才需要创建此类触发器
任何数据库用户的登录和注销信息保存在SYSTEM.SESSION_INFO表中
用户可以使用ALTER命令来修改已创建的触发器
使用DROPTRIGGER命令可删除触发器
触发器与过程的区别在于
过程是由用户应用程序甚至触发器显式运行的
触发器是有Oracle根据发生的事件而隐式激活的它不能被直接调用执行
内置程序包是Oracle数据库在安装时创建的PL/SQL对象的集合
它存储在Oracle数据库中内置程序包扩展了PL/SQL语言的功能
备份是原始数据的副本它可以防止数据意外丢失
物理备份维护物理数据库文件
逻辑备份维护逻辑数据到出数据库对象并将起存储为二进制文件
故障类型
语句故障SQL语句执行发生了逻辑故障
介质故障发生在写入或读取数据库操作所需的文件时可能会出错这些错误称为介质故障或磁盘故障
发生介质故障以后对控制文件和日志文件执行的数据库操作取决于这些文件是否已通过复用保护起来
复用是一个文件存储在其他几个磁盘上的过程如果其中一个磁盘发生介质故障则可从其他的磁盘中获取该文件
无归档日志方式可以防止数据库出现实例故障但无法避免磁盘或介质故障对于从实例故障恢复只有最近对一组联机日志文件中的数据库所做的修改才有效
对数据库采用无归档日志将禁用联机日志文件的归档在此模式下用户需定期进行数据库备份才可用于恢复在无归档日志方式下操作的数据库
Oracle中有4种类型的故障
语言故障
坏数据类型-当用户试图插入一些值而没有足够的空间来执行操作时发生
权限不足-当用户试图执行一些XX的操作时发生
用户进程故障-当用户因为程序出错而无法访问Oracle数据库时发生一个用户进程故障不会影响其他的用户进程PMON后台进程自动
升级会员 