oracle面试.doc

如何使用Oracle的游标？1. oracle中的游标分为显示游标和隐式游标2.显示游标是用cursor.is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理；隐式游标是在执行插入(insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。3.显式游标的操作：打开游标、操作游标、关闭游标；PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它Oracle中function和procedure的区别？可以理解函数是存储过程的一种函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值两者都可以通过out参数返回值,如果需要返回多个参数则建议使用存储过程在sql数据操纵语句中只能调用函数而不能调用存储过程Oracle的导入导出有几种方式，有何区别？1.使用oracle工具exp/imp2.使用plsql相关工具方法1.导入/导出的是二进制的数据，2.plsql导入/导出的是sql语句的文本文件Oracle中有哪几种文件？数据文件（一般后缀为.dbf或者.ora），日志文件(后缀名.log)，控制文件（后缀名为.ctl）怎样优化Oracle数据库，有几种方式？个人理解，数据库性能最关键的因素在于IO，因为操作内存是快速的，但是读写磁盘是速度很慢的，优化数据库最关键的问题在于减少磁盘的IO，就个人理解应该分为物理的和逻辑的优化，物理的是指oracle产品本身的一些优化，逻辑优化是指应用程序级别的优化物理优化的一些原则：Oracle的运行环境（网络，硬件等）使用合适的优化器合理配置oracle实例参数建立合适的索引（减少IO）将索引数据和表数据分开在不同的表空间上（降低IO冲突）建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO）逻辑上优化：可以对表进行逻辑分割，如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中如：select * from Emp where name=?这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时选择有效的表名对于多表连接查询，可能oracle的优化器并不会优化到这个程度，oracle中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sqlWhere字句规则Oracle中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾，另外!=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数），is null、is not null等优化器不会使用索引使用Exits Not Exits替代In Not in合理使用事务，合理设置事务隔离性数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数Oracle中字符串用什么符号链接？Oracle中使用|这个符号连接字符串如abc | dOracle分区是怎样优化数据库的?Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。1. 增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用；2. 减少关闭时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少；3. 维护轻松：如果需要得建表，独产管理每个公区比管理单个大表要轻松得多；4.均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；5.改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快6. 分区对用户透明，最终用户感觉不到分区的存在。Oracle是怎样分页的？Oracle中使用rownum来进行分页,这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的select * from ( select rownum r,a from tabName where rownum 10Oralce怎样存储文件，能够存储哪些文件？Oracle能存储clob、nclob、blob、bfileClob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型Nclob可变字符类型的数据，不过其存储的是Unicode字符集的字符数据Blob可变长度的二进制数据Bfile数据库外面存储的可变二进制数据Oracle中使用了索引的列，对该列进行where条件查询、分组、排序、使用聚集函数，哪些用到了索引？均会使用索引，值得注意的是复合索引（如在列A和列B上建立的索引）可能会有不同情况数据库怎样实现每隔30分钟备份一次？通过操作系统的定时任务调用脚本导出数据库Oracle中where条件查询和排序的性能比较？Order by使用索引的条件极为严格，只有满足如下情况才可以使用索引，1.order by中的列必须包含相同的索引并且索引顺序和排序顺序一直2.不能有null值的列所以排序的性能往往并不高，所以建议尽量避免order by解释冷备份和热备份的不同点以及各自的优点？冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法热备份是在数据库运行的情况下，采用归档方式备份数据的方法冷备的优缺点：1是非常快速的备份方法（只需拷贝文件）2容易归档（简单拷贝即可）3容易恢复到某个时间点上（只需将文件再拷贝回去）4能与归档方法相结合，作数据库“最新状态”的恢复。5低度维护，高度安全。冷备份不足：1单独使用时，只能提供到“某一时间点上”的恢复。2在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。3若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。4不能按表或按用户恢复。热备的优缺点1可在表空间或数据文件级备份，备份时间短。2备份时数据库仍可使用。3可达到秒级恢复（恢复到某一时间点上）。4可对几乎所有数据库实体作恢复。 5恢复是快速的，在大多数情况下在数据库仍工作时恢复。热备份的不足是： 1不能出错，否则后果严重。 2若热备份不成功，所得结果不可用于时间点的恢复。 3因难于维护，所以要特别仔细小心，不允许“以失败而告终”。解释data block , extent和segment的区别？data block数据块，是oracle最小的逻辑单位，通常oracle从磁盘读写的就是块extent区，是由若干个相邻的block组成segment段，是有一组区组成tablespace表空间，数据库中数据逻辑存储的地方，一个tablespace可以包含多个数据文件比较truncate和delete命令？Truncate和delete都可以讲数据实体删掉，truncate的操作并不记录到rollback日志，所以操作速度较快，但同时这个数据部能恢复Delete操作部腾出表空间的空间Truncate不能对视图等进行删除Truncate是数据定义语言（DDL），而delete是数据操纵语言(DML)解释什么是死锁，如何解决Oracle中的死锁？简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等待状态，如果是行级锁则不能操作锁定行解决办法：查找出被锁的表select b.owner,b.object_name,a.session_id,a.locked_modefrom v$locked_object a,dba_objects bwhere b.object_id = a.object_id;select b.username,b.sid,b.serial#,logon_timefrom v$locked_object a,v$session bwhere a.session_id = b.sid order by b.logon_time;杀进程中的会话alter system kill session sid,serial#;简述oracle中dml、ddl、dcl的使用Dml数据操纵语言，如select、update、delete，insertDdl数据定义语言，如create table、drop table等等Dcl数据控制语言，如commit、rollback、grant、invoke等说说oracle中的经常使用到得函数Length长度、lower小写、upper大写，to_date转化日期，to_char转化字符Ltrim去左边空格、rtrim去右边空格，substr取字串，add_month增加或者减掉月份、to_number转变为数字怎样创建一个一个索引,索引使用的原则,有什么优点和缺点创建标准索引：CREATE INDEX索引名ON表名(列名) TABLESPACE表空间名;创建唯一索引:CREATE unique INDEX索引名ON表名(列名) TABLESPACE表空间名;创建组合索引:CREATE INDEX索引名ON表名(列名1,列名2) TABLESPACE表空间名;创建反向键索引:CREATE INDEX索引名ON表名(列名) reverse TABLESPACE表空间名;索引使用原则：索引字段建议建立NOT NULL约束经常与其他表进行连接的表，在连接字段上应该建立索引；经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；可选择性高的关键字，应该建立索引；可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：A、正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上；B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引；C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；频繁DML的表，不要建立太多的索引；不要将那些频繁修改的列作为索引列；索引的优缺点：有点：创建唯一性索引，保证数据库表中每一行数据的唯一性大大加快数据的检索速度，这也是创建索引的最主要的原因加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。缺点：索引创建在表上，不能创建在视图上创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度怎样创建一个视图,视图的好处,视图可以控制权限吗?create view视图名as select列名别名 from表unio all select 好处：可以简单的将视图理解为sql查询语句，视图最大的好处是不占系统空间一些安全性很高的系统，不会公布系统的表结构，可能会使用视图将一些敏感信息过虑或者重命名后公布结构简化查询可以控制权限的，在使用的时候需要将视图的使用权限grant给用户主键有几种;字符型，整数型、复合型oracle的锁又几种,定义分别是什么;1.行共享锁(ROW SHARE)2.行排他锁(ROW EXCLUSIVE)3 .共享锁(SHARE)4.共享行排他锁(SHARE ROW EXCLUSIVE)5.排他锁(EXCLUSIVE)使用方法：SELECT * FROM order_master WHERE vencode=V002FOR UPDATE WAIT 5;LOCK TABLE order_master IN SHARE MODE;LOCK TABLE itemfile IN EXCLUSIVE MODE NOWAIT;ORACLE锁具体分为以下几类：1.按用户与系统划分，可以分为自动锁与显示锁自动锁：当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。显示锁：某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。2 .按锁级别划分，可分为共享锁与排它锁共享锁：共享锁使一个事务对特定数据库资源进行共享访问另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性，但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。排它锁：事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。3.按操作划分，可分为DML锁、DDL锁DML锁又可以分为，行锁、表锁、死锁行锁：当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。表级锁：当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参考相关文档)。死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。如事务1在表A行记录#3中有一排它锁，并等待事务2在表A中记录#4中排它锁的释放，而事务2在表A记录行#4中有一排它锁，并等待事务; 1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造成了死锁。死锁一般是因拙劣的事务设计而产生。死锁只能使用SQL下:alter system kill session sid,serial#；或者使用相关操作系统kill进程的命令，如UNIX下kill -9 sid,或者使用其它工具杀掉死锁进程。DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁排它DDL锁：创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。共享DDL锁：需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。分析锁：ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。4.内部闩锁内部闩锁：这是ORACLE中的一种特殊锁，用于顺序访问内部系统结构。当事务需向缓冲区写入信息时，为了使用此块内存区域，ORACLE首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。rowid,rownum的定义？rowid和rownum都是虚列rowid是物理地址，用于定位oracle中具体数据的物理存储位置rownum则是sql的输出结果排序，从下面的例子可以看出其中的区别。2.事务是什么？事务是作为一个逻辑单元执行的一系列操作，一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性局部索引与全局索引的区别1.局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。2.如果局部索引的索引列以分区键开头，则称为前缀局部索引。3.如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。4.前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。5.局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。6.局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。7.位图索引只能为局部分区索引。8.局部索引多应用于数据仓库环境中。全局索引globalindex1.全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。2.全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。3.全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。4.全局索引多应用于oltp系统中。5.全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。6.oracle9i以后对分区表做move或者truncate的时可以用updateglobalindexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。7.表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引表链接方式1)）嵌套循环(nest loop): 对于被连接的数据子集较小的情况，嵌套循环连接是较好的选择。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（大于10000不合适），要把返回子集较小的表作为外表（驱动表），而且在内表的连接字段上一定要有索引。2）哈希连接(hash join): 哈希连接是大数据集连接时常用的方式，优化器使用两个表中较小的表，利用连接键在内存中