DBA面试宝典.doc

一份ORACLE DBA面试题一：SQL tuning 类1:列举几种表连接方式答:merge join,hash join,nested loop2:不借助第三方工具，怎样查看sql的执行计划答:sqlplusset autotrace .utlxplan.sql创建plan_table表3:如何使用CBO,CBO与RULE的区别答:在初始化参数里面设置optimizer_mode=choose/all_rows/first_row等可以使用cbo.rbo会选择不合适的索引,cbo需要统计信息。4:如何定位重要(消耗资源多)的SQL答:根据v$sqlarea 中的逻辑读/disk_read。以及寻找CPU使用过量的session,查出当前session的当前SQL语句，或者：监控WIN平台Oracle的运行5:如何跟踪某个session的SQL答:先找出对应的sid,serial,然后调用system_system.set_sql_trace_in_session(sid,serial,true);参考：跟踪某个会话6:SQL调整最关注的是什么答:逻辑读。IO量7:说说你对索引的认识（索引的结构、对dml影响、对查询影响、为什么提高查询性能答:默认的索引是b-tree.对insert的影响.(分裂，要保证tree的平衡)对delete的影响.(删除行的时候要标记改节点为删除).对update的影响，如果更新表中的索引字段，则要相应的更新索引中的键值。查询中包含索引字段的键值和行的物理地址。8:使用索引查询一定能提高查询的性能吗？为什么答:不能。如果返回的行数目较大，使用全表扫描的性能较好。9:绑定变量是什么？绑定变量有什么优缺点答:通俗的说，绑定变量就是变量的一个占位符，使用绑定变量可以减少只有变量值不同的语句的解析。10:如何稳定(固定)执行计划答:使用stored outline.11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么答i：使用sort_area_size，hash_area_size,每个session分配相同的值，不管有无使用。9i: 使用pga_aggregate来统一管理。临时表空间的作用:在sort_area_size中不能完成的部分在临时表空间完成，临时表空间在重建索引，创建临时表等都要用到。还有hash join不能完成的也在临时表空间中做。12:存在表T(a,b,c,d),要根据字段c排序后取第2130条记录显示，请给出sqlselect a,b,c,d from (select a,b,c,d from T order by c) where rownum=30minusselect a,b,c,d from (select a,b,c,d from T order by c) where rownum 1 otherwise 1 the order of this algorithm is .a. log (n) b. n c. n2 d. nn8. The number of 1s in the binary representation of 3*4096+ 15*256+5*16+ 3 are .a. 8 b. 9 c. 10 d. 12百威啤酒(武汉公司)1,为什么申请来百威?2,将来有什么打算?3,有没有社会活动经历?4,有没有当众演讲的经历?5,经常使用那些软件?6,喜欢哪些课程?7,你认为工作中的什么因素对你来说最重要?8,什么时候可以来上班?可以在这里工作多久?9,八点上班,要加班和出差,能不能做到?星巴克1、 您是一家咖啡店的店经理，你发现店内同时出现下列状况：1）许多张桌子桌面上有客人离去后留下的空杯未清理，桌面不干净待整理。2）有客人正在询问店内卖哪些品种，他不知如何点咖啡菜单。3）已有客人点完成咖啡，正在收银机旁等待结帐。4）有厂商正准备要进货，需要店经理签收。请问，针对上述同时发生的情况，你要如何排定处理之先后顺序，为什么2、 有一位甲员工脾气不好以致在前三家店因为与店内其他同事相处不佳而屡屡调动，现在甲被调到你的店里面来，请问身为店经理的你，将如何应对？3、 你是店经理，本周五结帐后，发现门市总销售额较上周五减少30%，请问可能原因 会是哪几种，各原因如何应对？凹凸电子软件笔试题1. Select ONE of the following projects to discuss:a. Signal Filtering: You are given a sampled real time waveform consisting of a sensor reading mixed with highly periodic impulses and high frequency noise. The desired output is the real time filtered sensor signal with the impulses and noise removed, and a readout of the impulse period. The FFT may not be used.b. Interrupt Processing. A headware register consisting of eight independent edge triggered latches is used to record external asynchronous interrupt requests. When any of the request bits are latched, a software interrupt is generated. The software may read the latch to see which interrupt(s) occurred. Writing a one to any latch bit will clear the latch. How does that software assure that no interrupt request is ever missed?c. User Interface: a prototype MP3 player interface consisting of a playlist display and a few control buttons is given to you. How would you make the interface “skinnable”, with user selected graphics, options, and control button placement?Each project description is incomplete. What questions would you ask to completely specify the project? What development tools would you prefer to use?What algorithm /data structures/design would you use?2. What program(s) have you coded for you own enjoyment (not part of a school project, not for pay). What type of software project would you most enjoy working on?3. Have you participated in a team programming project? What is the hardest part of programming as a team, as opposed to programming alone?友立资讯笔试题目1一堆鸡蛋，3个3个数剩余2个，5个5个数剩余1个，7个7个数剩余3个，问这堆鸡蛋 最少有多少个？并给出通解。2列举五岳，及其所在省份。3何为四书。4按顺序默写24节气。5默写于谦的吟石灰。6英语翻译约300字。7作文一篇：求职有感。普华永道PWC笔试题目（作文）1最近10年来中国媒体的变化。2你认为发展汽车产业和公共交通哪个更重要？3如何理解风险投资？4如何理解广告的消极作用和积极作用？Avant! 微电子EE笔试题1名词解释：VLSI，CMOS，EDA，VHDL，Verilog，HDL，ROM，RAM，DRC，LVS。2简述CMOS工艺流程。3画出CMOS与非门的电路，并画出波形图简述其功能。4画出N沟道增强型MOSFET的剖面图。5简述ESD和latch-up的含义。6简述三极管与MOS管的区别。7简述MOORE模型和MEALY模型。8简述堆栈与队列的区别。奇码数字信息有限公司笔试题1画出NMOS的特性曲线（指明饱和区，截至区，线性区，击穿区和C-V曲线）22.2um工艺下，Kn3Kp，设计一个反相器，说出器件尺寸。3说出制作N-well的工艺流程。4雪崩击穿和齐纳击穿的机理和区别。5用CMOS画一个D触发器（clk，d，q，q-）Oracle DBA 笔试题（答题时间60分钟）Unix/Linux题目1、如何查看主机cpu，内存、IP和磁盘空间?2、你常用的进程管理、主机性能察看命令有哪些？3、建立组dba和该组下用户oracle，默认shell为bash。4、如何查看用户oracle的登陆历史和命令操作历史？5、写crontab，让脚本/opt/test.sh在每周日晚上8:00执行。6、如何查找当前目录及其所有子目录下含有“ORA-”或“warning”字符内容的所有带log后缀的文件。7、写一个shell，完成自动登陆数据库（用户名密码是test/testpwd, 网络连接串是db_wending）并获取数据库当前SCN的功能。Oracle Database题目1、如何快速了解一个已经运行很久的数据库系统？2、如何获得SQL语句的执行计划和统计信息？3、列出你常用的数据字典视图和动态性能视图。4、陈述使用过的数据库备份和恢复技术，及其优缺点。5、描述 table/segment/tablespace/datafile/extent/block 之间的关系。6、前台系统反映非常慢，需要你去分析诊断。请详细陈述诊断流程，包括写下可能用到的操作系统命令、数据库视图等。7、公司准备把Oracle9i升级为Oracle，但批准的停库时间仅为10分钟，你打算如何应对？8、详细陈述RAC、Dataguard、Streams Replication、Advanced Replication等Oracle HA技术的原理和优缺点。9、写存储过程，把当前用户下数据库对象个数信息按对象类型分组输出来。10、请写下你常关注的oracle初始化参数。-End-国外公司的Oracle DBA 面试题2007-11-20 10:20Oracle DBA Interview Questions By B G1. How many memory layers are in the shared pool？2. How do you find out from the RMAN catalog if a particular archive log has been backed-up？3. How can you tell how much space is left on a given file system and how much space each of the file systems subdirectories take-up？4. Define the SGA and：？ How you would configure SGA for a mid-sized OLTP environment？ What is involved in tuning the SGA？5. What is the cache hit ratio， what impact does it have on performance of an Oracle database and what is involved in tuning it？6. Other than making use of the statspack utility， what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database？7. How do you tell what your machine name is and what is its IP address？8. How would you go about verifying the network name that the local_listener is currently using？9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance？10. What view（s） do you use to associate a users SQLPLUS session with his o/s process？11. What is the recommended interval at which to run statspack snapshots， and why？12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index， even if the index scan may appear to be calculated as more costly？13. Assuming today is Monday， how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM？15. What do the 9i dbms_standard.sql_txt（） and dbms_standard.sql_text（） procedures do？16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed？17. How would you best determine why your MVIEW couldnt FAST REFRESH？18. How does propagation differ between Advanced Replication and Snapshot Replication （readonly）？19. Which dictionary view（s） would you first look at to understand or get a high-level idea of a given Advanced Replication environment？20. How would you begin to troubleshoot an ORA-3113 error？21. Which dictionary tables and/or views would you look at to diagnose a locking issue？22. An automatic job running via DBMS_JOB has failed. Knowing only that its failed， how do you approach troubleshooting this issue？23. How would you extract DDL of a table without using a GUI tool？24. Youre getting high busy buffer waits - how can you find whats causing it？25. What query tells you how much space a tablespace named test is taking up， and how much space is remaining？26. Database is hung. Old and new user connections alike hang on impact. What do you do？ Your SYS SQLPLUS session IS able to connect.27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracles. What database recovery options are available？ Database is in archive log mode.28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses （LINUX and/or Solaris）。29. How do you increase the OS limitation for open files （LINUX and/or Solaris）？30. Provide an example of a shell script which logs into SQLPLUS as SYS， determines the current date， changes the date format to include minutes & seconds， issues a drop table command， displays the date again， and finally exits.31. Explain how you would restore a database using RMAN to Point in Time？32. How does Oracle guarantee data integrity of data changes？33. Which environment variables are absolutely critical in order to run the OUI？34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account？35. Why does Oracle not permit the use of PCTUSED with indexes？36. What would you use to improve performance on an insert statement that places millions of rows into that table？37. What would you do with an in-doubt distributed transaction？38. What are the commands youd issue to show the explain plan for select * from dual？39. In what script is snap$ created？ In what script is the scott/tiger schema created？40. If youre unsure in which script a sys or system-owned object is created， but you know its in a script from a specific directory， what UNIX command from that directory structure can you run to find your answer？41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain ？42. You create a private database link and upon connection，fails with： ORA-2085： connects to .What is the problem？ How would you go about resolving this error？43. I have my backup RMAN script called backup_rman.sh. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process？44. Explain the concept of the DUAL table.45. What are the ways tablespaces can be managed and how do they differ？46. From the database level， how can you tell under which time zone a database is operating？47. Whats the benefit of dbms_stats over analyze？48. Typically， where is the conventional directory structure chosen for Oracle binaries to reside？49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces？50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.如何获得Oracle DBA工作？不过48个小时，就有120封Oracle 数据库管理员的求职信堆在了Debbie Reames的桌子上，Reames是位于洛杉矶的商业编程公司的高级技术招聘人员。然后Reames做了许多招聘人员都做的事情。她通过三个步骤的过程剔除了其中的一般简历。需要H-1B visa信用卡?走吧。需要重新安置?不适合这个工作。只在小公司工作过?这个职位对你来说太大了。根据多项调查，除了IT历史上最低迷的时期之外，Oracle总是为那些经过认证的专业人士和数据库管理员提过了大量的工作机会。当然，具有资格的候选人的列表就更长了。当在市场上寻找一份工作的时候，即使是最资深的IT专家都需要记住一些他们在很久以前就学到的规则，并且同时采用一些新的策略。数据库管理员市场可以按照以多种方式分为两个部分初级和高级。Don Burleson，位于美国北卡罗来纳州Kittrell的BEI Oracle顾问公司的所有者，说，那些工作经验少于3年的初级数据库管理员，经常会面临失业和低等的工作市场。Burleson曾写过与Oracle技术和职业生涯有关的17本书，其中包括Oracle工作面试指导(Conducting the Oracle Job Interview)，这本书是为那些需要评估Oracle周围候选人的IT经理们准备的指南。“过去，许多公司都不会雇佣初学者，” Burleson说。然而，Burleson说，那些初级数据库管理员仍然是有希望的。那些不愿意