oracle笔试.doc

1. 对字符串操作的函数？ 答：ASCII() -函数返回字符表达式最左端字符的ASCII 码值CHR() -函数用于将ASCII 码转换为字符 -如果没有输入0 255 之间的ASCII 码值CHR 函数会返回一个NULL 值-应该是必须给chr()赋数字值 concat(str,str)连接两个字符串LOWER() -函数把字符串全部转换为小写UPPER() -函数把字符串全部转换为大写 LTRIM() -函数把字符串头部的空格去掉RTRIM() -函数把字符串尾部的空格去掉TRIM() -同时去掉所有两端的空格实际上LTRIM(),RTRIM(),TRIM()是将指定的字符从字符串中裁减掉其中LTRIM(),RTRIM()的格式为xxxx(被截字符串，要截掉的字符串)，但是TRIM()的格式为TRIM(要截掉的一个字符 from 被截的字符串)SUBSTR() -函数返回部分字符串INSTR(String,substring) -函数返回字符串中某个指定的子串出现的开始位置，如果不存在则返回0REPLACE(原来的字符串，要被替换掉的字符串，要替换成的字符串)SOUNDEX() -函数返回一个四位字符码 -SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回NULL 值2、 事务概念答案：事务是这样一种机制，它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用： * 一致性：同时进行的查询和更新彼此不会发生冲突，其他用户不会看到发生了变化但尚未提交的数据。 * 可恢复性：一旦系统故障，数据库会自动地完全恢复未完成的事务。 3、oracle中查询系统时间答：select sysdate from dual4、 触发器的作用，什么时候用触发器,创建触发器的步骤，触发器里是否可以有commit, 为什么？答案：触发器是可以由事件来启动运行的，存在于数据库服务器中的一个过程。他的作用：可以实现一般的约束无法完成的复杂约束，从而实现更为复杂的完整性要求。使用触发器并不存在严格的限定，只要用户想在无人工参与的情况下完成一般的定义约束不可以完成的约束，来保证数据库完整性，那么就可以使用触发器。由于触发器主要是用来保证数据库的完整性的，所以要创建一个触发器，首先要明确该触发器应该属于那一种（DML，INSTEAD OF，SYSTEM）因为他们各有个的用途；其次就是要确定触发器被触发以后所设计到的数据。出发器中不可以使用COMMIT。5数字函数 abs()绝对值 exp(value)e的value次幂 ceil()大于等于该值的最小整数 floor()小于等于该值的最大整数 trunc(value,precision)保留precision个小数截取value round(value,precision)保留precision个小数对value进行四舍五入 sign()根据值为正负零返回1，-1，0 mod()取模操作 power(value,exponent)value的exponent次幂 sqrt()求平方根4、 关系数据库系统与文件数据库系统的区别在那里?关系数据库系统一般适用那些方面? 答案： 关系数据库系统文件系统的区别在于：首先，关系性数据库的整体数据是结构化的，采用关系数据模型来描述，这是它与文件系统的根本区别。（数据模型包括：数据结构，数据操作以及完整性约束条件）其次，关系数据库系统的共享性高，冗余低可以面向整个系统，而文件系统则具有应用范围的局限性，不易扩展。第 三，关系数据库系统采用两级映射机制保证了数据的高独立性，从而使得程序的编写和数据都存在很高的独立性。这方面是文件系统无法达到的，它只能针对于某一 个具体的应用。（两级映射：保证逻辑独立性的外模式/模式映射和保证物理独立性的内模式/模式映射。外模式：用户模式，是数据库用户的局部数据的逻辑结构 特征的描述。模式：数据库全体数据的逻辑结构特征的描述。内模式：也就是数据最终的物理存储结构的描述。）第四，就是关系性数据库系统由统一的DBMS进行管理，从而为数据提供了如安全性保护，并发控制，完整性检查和数据库恢复服务。5、 触发器的概念,存储过程的概念. 答案： 触发器： 是存储在数据库中的过程，当表被修改（增、删、改）时它隐式地被激发。存储过程：是数据库语言SQL的集合，同样也存储在数据库中，但是他是由其他应用程序来启动运行或者也可以直接运行。6、 基本SQL语句有哪些. 答案： select、insert、update、delete、create、drop、truncate1. 中级8、什么是事务一致性 ? 选择熟悉的数据库实现一个事务处理,如信用卡提款. 答案： 事务的一致性：是事务原子性的体现，事务所对应的数据库操作要么成功要么失败没有第三种情况。事务不管是提交成功与否都不能影响数据库数据的一致性状态。 事务：用户定义的一个数据库操作序列，这些操作要么全部成功完成要么全部不做，是一个不可分割的整体。定义事务的SQL语句有：BEGIN TRANSACTION，COMMIT，ROLLBACK。事务的原子性：就是事务所包含的数据库操作要么都做,要么都不做.事务的隔离性：事务对数据的操作不能够受到其他事务的影响。事务的持续性：也就是说事务对数据的影响是永久的。对信用卡提款这一事务而言就是要保证提取到现金和卡帐号余额的修改要同时成功或失败.BEGIN TRANSACTION读取A的帐户余额BALANCE；BALANCE=BALANCE-AMOUNT转帐金额；IF（BALANCE0） THENROLLBACK；ELSEBEGIN将A的新余额写回；读取B的帐户余额BALANCEB；BALANCEB=BALANCEB+AMOUNT转帐金额；将B的新余额写回；COMMIT；END IF；END；9、 实际编写和调试存储过程或触发器. 答案： 10、 实现索引的方式? 索引的原理? 索引的代价? 索引的类型？ 答案： 实现索引的方式有两种：针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名)；在创建表时为字段建立主键约束或者唯一约束，系统将自动为其建立索引。 索引的原理：根据建立索引的字段建立索引表，存放字段值以及对应记录的物理地址，从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析.索引的类型有：B树索引，位图索引，函数索引等。北京亿阳信通一、选择题1.当你执行以下查询语句:SELECT empno,enameFROM empWHERE empno =7782 OR empno =7876;在WHERE语句中，以下哪个操作符可以取代OR？A. INB. BETWEEN C. LIKED. =2. 哪个实现 IF.THENELSE 逻辑?A. INITCAP()B. REPLACE()C. DECODE()D. IFELSE()3. 以下哪行有错?1 SELECT deptno2 FROM emp3 GROUP BY deptno4 HAVING COUNT(deptno)=5 (SELECT max(count(deptno)6 FROM emp7 GROUP BY deptno);A. Line 3B. Line 4C. Line 5D. Line 7E. 都没错.4. 当一个程序执行了 SELECT.FOR UPDATE 语句, 以下哪个步骤必须需要完成?A. 执行 COMMIT or ROLLBACK 结束 transaction, 即使并没有数据改变。B. 如果有数据改变,则需要执行commit or rollback 来结束 transaction。C. 由于只有数据确实改变后，一个transaction 才会开始，所以现在不需要COMMIT or ROLLBACK 被执行。5. 以下哪个命令可以被用来从表 STATE中 drop 列 UPDATE_DT ?A. ALTER TABLE STATE DROP COLUMN UPDATE_DT;B. ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;C. DROP COLUMN UPDATE_DT FROM STATE;D. 你不能从这个表中DROP该列.6. 哪个命令用来创建一个primary key constraintpk_books 在表 BOOKS, 列 ISBN上? 请选择一个。A. create primary key on books(ISBN);B. create constraint pk_books primary key on books(ISBN);C. alter table books add constraint pk_books primary key (ISBN);D. alter table books add primary key (ISBN);7. 以下哪行有错r?1 X :=Y +200;2 IF X 10 THEN3 Y :=30;4 ELSEIF X connect /as sysdbasqlshutdown normal；（2） 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件sqlcp （3） 重启Oracle数据库sqlstartup三、 热备份热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成：1. 数据文件一个表空间一个表空间的备份。（1） 设置表空间为备份状态（2） 备份表空间的数据文件（3） 回复表空间为正常状态2. 备份归档log文件（1） 临时停止归档进程（2） log下那些在archive rede log目标目录中的文件（3） 重新启动archive进程（4） 备份归档的redo log文件3. 用alter database bachup controlfile命令来备份控制文件热备份的优点是：1. 可在表空间或数据库文件级备份，备份的时间短。2. 备份时数据库仍可使用。3. 可达到秒级恢复（恢复到某一时间点上）。4. 可对几乎所有数据库实体做恢复5. 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。热备份的不足之处：1. 不能出错，否则后果不堪设想。2. 假如热备份不成功，所得结果则不能用于时间点的恢复。3. 因为难于维护，所以需要特别仔细小心，不允许在操作时半途而废。1.给出在star schema中的两种表及它们分别含有的数据？fact tables和dimension tables。fact table主要包含大量的主要信息而dimension tables中存放着对fact table的某些属性描述的信息。2.fact table上需要建立何种索引？位图索引（bitmap index）3.给出两种相关约束？主键和外键4.如何在不影响子表的前提下，重建母表？使子表的外键强制失效，重建母表，激活外键5.解释$Oracle_home和$oracle_base的区别？$oracle_home是Oracle的产品目录$Oracle_base是Oracle的根目录6.如何判断数据库的时区？select dbtimezone from dual；7.解释function、procedure和package的区别？function和procedure都是PL/SQL的代码集合，都是为了完成一个任务。procedure不需要返回值而function将返回一个值；package是一个为了完成一组商业功能的function和procedure的集合。8.解释table function的用途？table function是通过PL/SQL逻辑返回一组记录，它用于表或者视图。也可以用于pipeline或者etl过程。9.当用户进程出错，哪个后台进程负责清理它？pmon10.描述什么是redo logs？redo logs是用于存放数据库数据改动的物理和逻辑结构。用于恢复数据库。11.如何强制log swith？alter System switch logfile；12.temporary tablespace和permanent tablespace的区别？temporary tablespace用于临时对象，例如排序结果；permanent tablespace用于存储“真实”的对象，例如表、回滚段。13.创建数据库时，自动建立的tablespace的名称？System tablespace14.创建用户时，需要赋予用户什么权限才能使它连接上数据库？connect15.如何在tablespace里增加数据文件？alter tablespace add datafilesize;16.如何变动数据文件的大小？alter database datafile resize;17.哪些View用来检查数据文件的大小？dba_data_files18.哪些View用来判断tablespace的剩余空间？dba_free_space19.如何重构索引？alter index rebuild;20.解释什么是partition以及它的优点？partition是将大表和者索引分割成更小的、易于管理的分区。21. IMPORT和SQL*LOADER 这2个工具的不同点？这两个ORACLE工具都是用来将数据导入数据库的。区别是：IMPORT工具只能处理由另一个ORACLE工具EXPORT生成的数据。而SQL*LOADER可以导入不同的ASCII格式的数据源。1.解释冷备份与热备份的不同及各自的优缺点？热备份是针对归档模式的数据库，数据库仍处于工作状态时进行备份，而冷备份是在数据库关闭后，进行备份，适合所有模式的数据库。热备份时，数据库仍然可以 被使用并且可以恢复到任意一个时间点。冷备份时，它的备份和恢复操作相当简单，由于冷备份的数据库可以工作在非归档模式下，数据库的性能要比归档模式稍 好。（因为不用将archive log写入磁盘）2.你必须利用备份恢复数据库，但是没有控制文件，如何解决问题？重建控制文件，用带backup control file 子句的recover命令恢复数据库。3.如何生成explain plan？运行utlxplan.sql建立plan表针对特定的sql语句。使用 explain plan set statement_id=sts1into plan_table 运行utlxplp.sql和utlxpls.sql观察explain plan。4.解释global_name设置为TRUE的用途？global_name是指明数据库的连接方式。如果这个参数设置为TRUE，那么在建立数据库连接时就必须使用相同名连接远程数据库。5.如何加密PL/SQL程序？WRAP6.举出三种可以收集three advisory statistics？buffer cache advice segment statistics timed statistics7.audit trace 存放在哪个Oracle的目录中？Unix$oracle_home/rdbms/audit windows the event viewer8.解释materialized views的作用？materialized View用于减少那些汇总、集合和分组的信息的集合数量。它们适合数据仓库和dds系统。9.哪个后台进程刷新materialized Views?the job queue processes10.如何判断哪个session正在连接以及它们正在等待的资源？V$session v$session_wait11.举出两个判断DDL改动的方法？logminer 和streams12.coalescing做了什么？coalescing针对于字典管理的tablespace进行碎片整理。将临近的小的extents合并成单个大的extent。13.如何判断往表里增加了一条数据？auditing14.你刚刚编译了一个PL/SQL package但是有错误报道，如何显示出错误？show errors15.如何搜索表的各种状态？analyzethe analyze command16.如何启动session级别的trace？dbms_session.set_SQL_tracealter session set SQL_trace=TRUE；17.用于网络连接的两个文件？tnsnames.ora和SQLnet.ora1关于优化器的优化配置介绍oracle数据库优化的基本方式，基于规则的优化，基于规则包括全表扫描，扫描第一行(需要上网查看一下)基于成本的优化，基于选择的优化也就是oracle的3种优化器，关于优化器的配置可以通过oracle数据库启动的时候通过初始化数据库配置文件来配置 通过配置init.ora文件的optimizer_mode也就是优化模式的参数来配置这个配置是一个全局的共享配置。优化模式可以根据具体的生产环境在用户会话级别和sql级别进行在配置 ,来覆盖全局的优化模式配置使用基于成本的优化器要运行一个命令 analyze(分析)让oracle分析表 统计统计数据对象达到更好的优化效果.(如果针对表执行了analyze 将自动使用基于成本的优化器)2oracle访问表的方式优化a 全表扫描模式 全表扫面oracle采用读取数据块的方式对全部表进行扫面块的大小可以在init.ora中配置 数据块的大小应该是系统的数据块的整数倍。适当调节数据块的大小可以起到性能优化的作用b 通过rowid访问数据表 rowid表记录的物理位置信息标记，oracle通过索引与rowid建立联系通过适当的在字段上建立索引可以高速高效的访问数据表的记录，从而达到数据库优化查询的目的。3 共享sql语句为了不重复解析SQL语句oracle采用共享SQL的方式提高SQL语句执行效率。oracle把执行过的语句放入到一个叫做SGA内存区域的share buffer pool 中 也就是系统全局区域的缓存共享池如果客户端执行的SQL语句完全相同oracle首先查找share buffer pool 如果存在这个语句直接执行这个语句。不过oracel对SQL语句的匹配要求很严格是在字符级别进行匹配的，包括执行语句的字符匹配。所以在实际的项目中尽量要求编码人员统一规范编写SQL语句尽量做到统一以提高数据库的性能和执行效率。还有就是oracle基于对象的共享匹配，包括用户对象，变量的绑定在权限对象分配中和dba沟通做好系统优化的准备。我们可以适当的提高SGA中share buffer pool 的大小 来优化数据库的执行效率4 根据oracle的SQL语句解析器进行优化首先解释一下oracle解释SQL的语句的顺序，oracle在解释SQL语句的时候是按照从右到左的顺序进行解析。所有要把检索效率最高的表，记录最少的表写在FROM的最右表也就是基础表的选择5 交叉表优先选择在多对多的表关联中会遇到这样的情况。在sql语句的书写方面尽量把引用表放在FROM的右边6 基于wher条件书写顺序的解释优化首先介绍一下oracle以上两个数据库优化的基本原则就是根据oracle解析sql语句和oracle自身对在扫描表的时候做的优化基础上尽量优先选择出记录少的表放在from的最右边7尽量减少与数据库的交互减少系统的IO访问量8 不要用*9 使用decode函数减少处理时间10 整合简单的无关联的数据库访问 较少数据库服务器的IO操作11删除重复记录 尽量使用RID12 用TRUNCATE替代DELETE13 在编写pl/sql时候尽量多使用cimmit 以便释放系统资源只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:COMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d.ORACLE为管理上述3种资源中的内部花费14. 用Where子句替换HAVING子句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.例如:低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != SYDNEYAND REGION != PERTH高效SELECT REGION，AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等.除此而外,一般的条件应该写在WHERE子句中)15 尽量少用子查询 合并可以合并的子查询例如:低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)ANDDB_VER= ( SELECT DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)Update 多个Column 例子:低效:UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效:UPDATE EMPSET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;15. 使用表的别名(Alias)当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.(译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)1、long i