oracle面试题.docx

1、oracle面试题Oracle数据库的主要特点:支持多用户 大量务量的事务处理数据安全性和数据完整性支持分布式数据处理可移植性支持工业标准的操作系统Oracle数据库基于客户端/服务器技术客户端应用程序通过向服务器请求并接收信息的方式与数据库进行交互 充当用户和数据库之间的接口在多个客户端同时向服务器请求同一数据时 数据库服务器或后端应用程序 可对数据库进行最佳的管理 它还对所有客户端应用程序强制保持强制数据的完整 并控制对数据库的访问和其他安全要求SHOW SGA 查看SGA的大小创建表空间CREATE TABLESPACE tsDATAFILE E:oracleoradataaccpts_

2、01.dbfSIZE 50M;创建用户CREATE USER ACCPIDENTIFIED BY ACCPDEFAULT TABLESPACE USERS;授予可传递的权限GRANT SELECT ON EMP TO ACCP WITH GRANT OPTION;彻底清除Oracle软件1.删除Oracle 注册表信息HKEY_LOCAL_MACHINESOFTWAREOracle2.删除Oracle服务HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices3.删除事件日志HKEY_LOCAL_MACHINESYSTEMCurrentControlS

3、etServicesEventlogApplication4.删除Oracle环境变量控制面板-系统-高级-环境变量 删除相关5.删除Oracle菜单 开始-程序-Oracle-OraHome926.删除Program FileOracle目录7.重启Windows8.删除Oracle主目录练习创建一个名称为DAVID的用户 口令为davidpass 并授予其连接数据库和创建表对象的权限 同时授予其访问另一个用户MARTIN的表TEST的权限以SYSTEM身份登录SQL*PlusCREATE USER DAVIDIDENTIFIED BY davidpassDEFAULT TABLESPACE

4、 user;授予登录数据库和创建表的权限GRANT CREATE SESSION TO DAVID;GRANT CREATE TABLE TO DAVID;授予使用表空间的权限ALTER USER DAVID QUOTA UNLIMITED ON USERS;以MARTIN身份登录SQL*Plus 授予DAVID对表TEST的权限GRANT SELECT ON TEST TO DAVID;GRANT UPDATE ON TEST TO DAVID;系统全局区(SGA)的内存结构Oracle实例启动时会分配该内存区 SGA是构成Oracle实例的一个基本组件 数据库信息存储在SGA中 并由多个数

5、据库进行共享 该区用于存储信息的空间是从装有Oracle的计算机虚拟内存中分配的 SGA有多个内存结构 如共享池 数据缓冲区及日志缓冲区 决定SGA大小的一些初始化参数包括DB_CACHE_SIZE LOG_BUFFER和SHARED_POOL_SIZE.Oracle的用户当创建一个数据库时 Oracle将创建几个默认的数据库用户模式 如SYS SYSTEM和SCOTT 用户SYS是Oracle中的超级用户 它的默认口令是change_on_install 数据库中数据字典的所有表和视图都存储在SYS模式中 这些表和视图对Oracle的操作非常重要 用户SYSTEM也有管理员的权限 它的默认口

6、令是manager 该用户创建显示管理信息的表和视图 以及Oracle工具所使用的4个示范表其中一个是EMP表 SCOTT的默认口令是tiger查询ROWID伪列SELECT ROWID,T.* FROM EMP T;SELECT * FROM EMP WHERE ROWID=:VARIBLE;删除表时同时删除相关的约束和索引DROP TABLE table_name CASCADE CONSTRAINTS;临时改变会话的默认日期格式ALTER SESSION set nls_date_format=YYYY-MM-DD;AMERICAN-12-MAY-05SIMPLIFIED CHINESE

7、-12-5月-05使用DATE关键字把字符串表示日期值SELECT DATE2005-10-25 FROM DUAL;查看当前系统时间SELECT SYSDATE FROM DUAL;运算符IN的用法SELECT ENAME FROM EMP WHERE EMPNO IN (7788,7369,7900);将日期四舍五入为最接近的那一天SELECT dateofjoining ROUND(MONTHS_BETWEEN(CURRENT_DATE,dateofjoining) FROM employee;提取日期时间类型的特定部分SELECT EXTRACT(MONTH FROM SYSDATE)

8、 FROM DUAL;Oracle支持字符 数值 日期时间 RAW LONGROW和LOB等数据类型Oracle的数据定义语言命令CREATE ALTER DROPDML Data Manipulation Language(数据操纵语言)用于检索 插入和修改数据库信息包括SELECT INSERT UPDATE和DELETE事务控制语言COMMIT SAVEPOINT和ROLLBACK都是事务控制命令 事务以执行更新操作的SQL语句开始 并以ROLLBACK或COMMIT语句显式结束 但使用DDL语句时 事务处理将隐式地自动结束连接操作符用于将两个以上的字符串 或者一个字符串与一个数据值合并

9、单行函数或标量函数对于在表中查询的每一行只返回一个值单行函数可用在SELECT子句中也可用在WHERE子句中锁的优点一致性 一次只允许一个用户更改数据完整性 一个用户进行了更改并保存 相应的更改将反映给所有用户并行性 允许多个用户访问同一数据表级锁定LOCK TABLE dept IN SHARE MODE;错误ORA-00060:等待资源时检测到死锁分区优点简化维护操作 允许用户对一个分区而非整个表执行维护操作改进性能 允许用户决定存储表分区的位置提高可维护性 用户可以只备份和恢复特定的分区范围分区 根据表中的值的范围进行分区散列分区 对不需要包含逻辑值的数据进行分区列表分区 允许对不相关的

10、数据进行分区复合分区 范围分区和散列分区的结合在表中应该行级锁定之后 其他用户访问未被事务锁定的任何行 要访问已被锁定的行 用户必须等待锁被释放在对列进行分区或子分区时 列表最多可以包含16列模式是数据库的整体结构对象可以为表 视图 程序包 序列 同义词和索引表是数据库的基本对象同义词是数据库对象的别名用于隐藏对象的名称和所有者创建视图所依赖的表称为基表视图隐藏了数据的复杂性 简化了用户的SQL命令视图将应用程序与基表定义的修改分隔开来视图通过重命名列 相对于基表 提供了数据 不影响基表使用WITH CHECK OPTION选项创建视图 确保对视图执行更新操作后的行满足视图指定的WHERE子句

11、的约束条件创建带有错误的视图定义的查询引用了一个不存在的表或现有表中的无效的列视图的所有者没有所需的权限联接视图上DML语句只能修改了一个基表视图只能在作为键保留表时才能更新Oracle索引便于更快地访问数据索引是与表关联的一种可选结构创建索引可以明显加快在表上执行SQL语句的速度当在SQL语句与WHERE子句中使用索引键值列时 该索引将直接指向包含这些值的行的位置 合理使用索引是减少磁盘I/O的主要方法CREATE INDEX语句用于在表的一个或多个列上创建索引不管索引是否存在 都无需修改任何SQL语句的定义 索引只是一种快速访问数据的途径 它只影响执行的速度一旦创建了索引 则无需再对索引执

12、行任何操作 Oracle会自动完成所需的维护操作唯一索引可以确保在定义索引的列中 表的任意两行的值都不相同Oracle 自动为表的主键或唯一键列创建唯一索引使用CREATE UNIQUE INDEX命令来明确地创建唯一索引组合索引 在表上的两个或多个列上共同创建索引 组合索引中列的顺序是任意的 也无须是表中相邻的列SELECT语句的WHERE子句中引用了组合索引中所有或大多数列 可以提高数据检索速度在创建索引时 定义使用的列的顺序 应该将最频繁访问的列放在最前面反向索引反转索引列中的每一个字节 同时要保持列的顺序如果对索引的修改仅集中在少数几块上 则这种排列有助于避免索引性能的降低通过反转索引

13、的键 可以将插入操作分布在整个索引上REVERSE与CREATE INDEX命令一起使用可以创建反向键索引ALTER INDEX命令和REBUILD NOREVERSE选项 将反向键索引转换为标准索引 不能将标准索引重建为反向索引位图索引 适用于列中的值的数目比表的行数少的列CREATE BITMAP INDEX命令来创建位图索引使用ORGANZATION INDEX子句表示创建的表为索引组织表基于函数的索引 可用与提高WHERE子句中的使用此函数的查询的检索速度局部分区索引 使用LOCAL关键字全局分区索引 使用GLOCAL关键字全局非分区索引 同标准索引 不是使用关键字公有同义词可被同一模

14、式的用户以及其他模式的用户访问私有同义词只能被同一模式的用户访问 同时应具有唯一的名称创建视图的语法结构CREATE OR REPLACE FORCE VIEWview_name (alias,alias.)AS select_statementWITH CHECK OPTIONWITH READ ONLY;Oracle支持的索引的类型唯一索引组合索引位图索引反向键索引基于函数的索引游标是在显示结果前将检索到的行存储在其中的内存位置使用PL/SQL的优点支持SQL所有的数据类型和NULL值 与SQL集成 提供了SQL的大部分功能 简化对Oracle数据的操作支持OOP 提供了面向对象编程工具

15、节省了构建复杂应用程序所消耗的成本和时间 PL/SQL还允许将SQL语句和代码块组合在一起 并将其发送给以可执行的形式存储经过编译的存储过程 能够快速有效地调用过程可以移植到任何运行Oracle的操作系统上使用最重要的是要保护数据 数据安全性 方法就是禁止用户直接操作敏感的数据 受限访问重要的数据PL/SQL块分为声明 可执行和异常处理3个部分声明块中使用的变量 游标和自定义异常 局部子程序可执行部分 必需的 操作声明部分中声明的变量 不允许执行DDL语句异常处理部分 指定发生错误时要执行的操作 标量数据类型不含内部组件 标量数据类型包括数字 字符 BOOLEAN和DATE等数据类型PL/SQ

16、L接受作为输入的PL/SQL块或子程序 并对其进行编译 PL/SQL再使用运行时系统来执行程序PL/SQL块的执行过程PL/SQL块发送到Oracle服务器 Oracle服务器将接受输入发送到其中的一个过程语句执行程序 执行之后 将输出结果发送给用户在可执行语句或表达式中引用变量和常量前 必须先对其进行声明标量 LOB和属性类型都是PL/SQL块中使用的数据类型LOB数据类型用于存储非结构化数据块 包括 文本 图形图像 视频剪辑和声音波形属性类型%TYPE 将某种数据类型的变量或列提供给其他变量%ROWTYPE 提供一种表示表中的某行的记录类型顺序控制GOTO语句将控制全转给GOTO语句中指定

17、的标签名称后面的可执行语句NULL语句用于将控制权转给下一条语句动态SQL就是在PL/SQL程序执行中生成SQL语句字符串 然后执行PL/SQL中不能直接使用DDL语句 动态SQL可以执行DDL和会话控制语句(ALTER SESSION) 通过EXECUTE IMMEDIATE命令执行动态SQL 只能用于处理返回单行或没有返回的SQL语句 要处理返回多行的动态SQL应该使用REF游标的OPEN.FOR语句还可以使用内置包DBMS_SQL来执行动态SQL 它可以获得更多的执行性能 但是使用起来比较繁琐使用OTHERS关键字可以捕捉任何类型的异常 以处理运行时引发的错误错误处理在执行程序期间出现

18、扰乱程序正常流程的运行时的错误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程序中用来获得查询结果集的指针 执行S

19、QL语句在处理检索到的行之前 会先将这些行存储在内存中 游标指向这个内存位置 从而逐行访问记录集Oracle使用内存的某部分来存储和更新检索到的行 可以以编程的方式访问数据 从而完成需要分别在结果集中每个记录上执行的过程代码的任务Oracle支持的游标类型隐式游标 检索一行显式游标 检索多行REF游标 动态绑定查询语句 与游标变量一起使用游标的4种属性 %FOUND %NOTFOUND %ROWCOUNT和%ISOPEN隐式游标执行PL/SQL块时自动声明 自动与正在执行的DML语句像关联 主要用于管理INSERT DELETE和UPDATE语句 以及返回单行的SELECT INTO 语句隐式

20、游标的%ISOPEN属性始终为假 因为会自动关闭显式游标管理步骤声明游标打开游标提取记录关闭游标控制显式游标OPEN 打开游标 执行查询 标识结果集和将游标置于第一行之前FETCH 检索结果集的行 并将游标向前移动一行 CLOSE 处理完所有行之后关闭游标声明带参数的显式游标可以增强游标的灵活性在声明游标之后 就会立即传递参数 在WHERE子句中的条件得到满足 将显示这些行在SELECT语句中使用FOR UPDATE子句声明显式游标更新行循环游标可以简化游标管理的代码 它自动打开游标 提取行和关闭游标如果要检索查询返回的所有记录 则可以使用循环游标循环游标每次只检索一次游标变量用于处理运行时动

21、态决定的查询 声明REF游标不需要指定SELECT查询语句 使用OPEN FOR 语句可以打开游标变量游标变量使用的相应限制不能在程序包中声明游标变量远程子程序不能接受游标变量的值不能使用比较操作符对游标变量进行相等或不相等测试不能将空值赋予游标变量数据列无法存储游标变量的值EXECUTE IMMEDIATE语句只能用户处理返回单行或没有返回的SQL语句 REF游标则可以处理返回结果集的动态SQL隐式游标和显式游标的区别隐式游标在执行SQL语句时由Oracle自动生成 用户不能控制隐式游标 隐式游标用与检索一行显式游标由用户显式声明 用户对显式游标有完全控制权 显式游标用于返回多行的查询显式游

22、标的属性%ISOPEN 如果游标已打开 则返回true 否则返回false%FOUND 如果成功处理了最后一行 则返回true%NOTFOUND 如果提取最后一行失败 则返回true%ROWCOUNT 返回处理的行数控制显式游标所用的语句OPEN 执行查询 标识结果集 并将游标置于第一行之前FETCH 检索当前行并将游标向前移动一行CLOSE 在处理完最后一行之后 关闭游标子程序的3个部分 包括声明部分 可执行部分和异常处理部分子程序的优点将代码分割成小模块带吗可重用和维护可以提供安全性过程 用于系统地完成特定任务的一组有序指令过程参数模式3种参数列表中的默认值赋给IN参数使用EXECUTE语

23、句和过程名 在PL/SQL程序中调用过程函数与过程类似 只是函数会返回值RETURN子句在定义函数时指示返回值的数据类型过程和函数的比较过程和函数都是子程序的类型过程和函数的执行方式是不同的过程是作为PL/SQL语句执行的 函数是作为表达式的一部分被调用的RETURN子句只在函数中指定 而且必须这样做过程不使用RETURN 语句返回值 但函数必须始终返回值 过程可以具有RETURN 语句 但是函数必须存在RETURN语句自动事务处理是由主事务处理启动的 启动自主事务处理之后 将暂停主事务处理自主事务处理SQL操作 最后通过恢复主事务处理来终止自主事务处理 PARGMA AUTONOMOUS_T

24、RANSACTION语句包括在子程序的声明部分中将该子程序标记为自主的自主事务处理不依赖于主事务处理的状态自主事务处理中的ROLLBACK和COMMIT 命令的使用不影响主事务处理的结果自主事物处理能够启动另一个自主事务处理包可以是过程 函数 变量和SQL的集合包规范:声明类型 变量 常量 异常 游标和子程序包主体:实现在包规范中声明的子程序和游标查询 这些实现对应用程序是隐藏的如果创建子程序或程序包出现错误 可以使用SHOW ERRORS命令查看详细信息程序包的优点封装相关对象模块化 将程序分解为明确定义的逻辑模块的过程可以轻松地对设计应用程序 允许用户只对规范 而不是对主体进行编码和编译程

25、序包主体中定义的私有子程序对其他程序包 隐藏了其定义首次调用打包的子程序时 整个程序包均将加载到内存中 后续的调用不需要磁盘I/O 因此性能不错程序包需要将游标规范和游标主体分开编写 这样允许在更改游标主体时 无需改变游标规范在编写游标主体时 应使用与游标规范相同的RETURN子句和SELECT语句在程序包主体中定义了游标主体 并在程序包的子程序中打开和使用了游标使用程序包中的REF的游标从Oracle存储过程返回记录集 Oracle的存储过程本来不能返回值但是可以利用IN OUT模式的REF游标变量参数返回结果集USER_OBJECTS数据字典视图 包含与当前用户创建的所有对象相关的数据US

26、ER_SOURCE数据字典视图 可以获得存储子过程的文本过程和函数的区别过程与函数类似 只是函数有个RETURN子句 该子句在函数规范中指示变量值的数据类型程序包中的公有项和私有项项是指子程序 类型 变量和常量声明为公有的项可以在程序包之外使用 声明为使用的项不能在程序包之外使用 公有项通常是在程序包规格说明中声明 在程序包主体中定义的而私有项只能在程序包主体中声明和定义 公有项可以用于全局目的 而私有项只能用于局部目的 只能在程序包内使用触发器在特定事件出现时自动执行的存储过程是在用户执行INSERT DELETE和UPDATE等更新表或视图的DML语句 或DDL语句时隐式运行的PL/SQL

27、过程触发器的功能自动生成数据强制复杂的完整性约束条件自定义复杂的安全权限提供审计和日志记录应用复杂的业务逻辑触发器组成部分触发器语句 触发器定时 BEFORE/AFTER 触发器事件INSERT UPDATE DELETE 表名 表/视图 触发器类型 行级/语句级触发器限制根据在WHEN字句中指定的条件限制触发器的执行 若为假 则不激活触发器触发器操作包含只有在激活触发器时才会执行的SQL语句和代码ALTER触发器在用户对表执行任意DML或DDL事件 并将更新的值存储在Oracle数据库之后激活BEFORE触发器与ALTER触发器则正好相反 它在将更新的值存储在Oracle数据库之前激活DML

28、触发器 当DML语句在表中发生时执行 语句级触发器 无论受触发语句影响的行数是多少 都只激活一次 即使没有行受影响 也会激活语句触发器 行级触发器 每当触发器语句影响表时就会激活行级触发器 在被修改的每一行上执行一次 若不存在影响行 则不激活行触发器 INSTRAD OF触发器 允许用户修改不能使用DML语句修改的视图 只可用于视图 不可用于表模式触发器 在数据库模式中执行DDL语句时激活数据库级触发器 在发生LOGOFF(退出) LOGON(登录) STARTUP(打开) SHUTDOWN(关闭)数据库和SERVERROR(生成错误消息)等系统事件时执行禁止用户执行特定的操作 则使用RAIS

29、E_APPLICATION_ERROR过程抛出异常 触发器会自动停止操作并回滚以前的更新不要在触发器中使用COMMIT和ROLLBACK语句 触发器与触发它的语句在同一事务中数据库级触发器 通常用于解决特殊问题 只有数据库管理员才需要创建此类触发器任何数据库用户的登录和注销信息保存在SYSTEM.SESSION_INFO表中用户可以使用ALTER命令来修改已创建的触发器使用DROP TRIGGER命令可删除触发器触发器与过程的区别在于 过程是由用户 应用程序甚至触发器显式运行的 触发器是有Oracle根据发生的事件而隐式激活的 它不能被直接调用执行内置程序包是Oracle数据库在安装时创建的P

30、L/SQL对象的集合它存储在Oracle数据库中 内置程序包扩展了PL/SQL语言的功能备份 是原始数据的副本 它可以防止数据意外丢失物理备份 维护物理数据库文件逻辑备份 维护逻辑数据 到出数据库对象 并将起存储为二进制文件故障类型语句故障 SQL语句执行发生了逻辑故障介质故障发生在写入或读取数据库操作所需的文件时 可能会出错 这些错误称为介质故障或磁盘故障发生介质故障以后 对控制文件和日志文件执行的数据库操作取决于这些文件是否已通过复用保护起来复用是一个文件存储在其他几个磁盘上的过程 如果其中一个磁盘发生介质故障 则可从其他的磁盘中获取该文件无归档日志方式可以防止数据库出现实例故障 但无法避免磁盘或介质故障 对于从实例故障恢复 只有最近对一组联机日志文件中的数据库所做的修改才有效对数据库采用无归档日志将禁用联机日志文件的归档 在此模式下 用户需定期进行数据库备份才可用于恢复在无归档日志方式下操作的数据库Oracle中有4种类型的故障语言故障 坏数据类型 - 当用户试图插入一些值 而没有足够的空间来执行操作时发生 权限不足 - 当用户试图执行一些XX 的操作时发生用户进程故障 - 当用户因为程序出错而无法访问Oracle数据库时发生 一个用户进程故障不会影响其他的用户进程 PMON后台进程自动