国外公司的Oracle DBA试题,你都会了吗?
涉及oracle,还有几个关于unix操作方面的
[@more@]国外公司的Oracle DBA试题
Oracle DBA Interview Questions By B G
1. How many memory layers are in the shared pool?
##library cache,row (dict ) cache,some fixed variable
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
##list backup of archivelog like
3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?
df -k
du -k
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?
cachehitratio=(1-(physical_reads/(consistent_gets+block_gets) )*100%
select 1-(physical_reads/(consistent_gets+block_gets)) from v$sess_io
where block_gets>0 or consistent_gets>0;
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?
hostname,ifconfig -a
netstat -nr,uname -n
8. How would you go about verifying the network name that the local_listener is currently using?
tnsping net_service_name
ping ip
sqlplus "conn user/name@net_service_name"
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?
ps -ef |grep ora_
10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?
from v$process p,v$session s
where p.addr=s.paddr
11. What is the recommended interval at which to run statspack snapshots, and why?
half an hour
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?
show parameter optimizer_mode,当然模糊匹配也可以
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.
exec dbms_job.submit();哈哈,参数自己记不得了,反正有desc可以查看结构,真爽
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
contrab -e 参数编辑文件
0 14 * * * /test/test.sh >/tmp/test.log 2>&1
mi :hh24 *** command >
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 couldn't 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?
没碰到这个错误号,查error参考手册先
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
v$lock,v$latch,v$db_object_cache
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?
看alert.log
23. How would you extract DDL of a table without using a GUI tool?
当然查看其他object也可以这样用,注意对象类型
select dbms_metadata.get_ddl('table_name','owner','table') from dual;
24. You're getting high "busy buffer waits" - how can you find what's causing it?
25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining?
dba_free_space; dba_source,
冒失就这两个dba_视图是单数形式,其他的都是复数的
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.
第一感觉shutdown abort,哈哈,结果当然不能这样
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.
of course instance recovery ,oracle will do it auto,nothing should done by dba.
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
top
cat /proc/cpuinfo
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?
restore database util time '时间点';
32. How does Oracle guarantee data integrity of data changes?
pk,fk,consistent read
33. Which environment variables are absolutely critical in order to run the OUI?
DISPLAY ,图形截面的程序运行,当然需要在那些纯shell下设置
export display 环境变量,才能执行成功
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
select count(*) from v$session where username='&name';
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?
insert into /*+APPEND*/ -- hint提示
nologging --不记录日志
parallel 并行执行等
37. What would you do with an "in-doubt" distributed transaction?
38. What are the commands you'd issue to show the explain plan for "select * from dual"?
set autotrace on;
set timed_statistics=true;
explain plan set statement_id='name' for
select * from dual;
utlxplan.sql 创建plan_table表
执行utlxplp或者utlxpsp.sql脚本查看成本要素之类
39. In what script is "snap$" created? In what script is the "scott/tiger" schema created?
sql.bsq
demo.sql--创建scott schema
40. If you're unsure in which script a sys or system-owned object is created, but you know it's in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
$oracle_home/rdbms/admin
$oracle_home/sysman/admin
$oracle_home/sqlplus/admin
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
DSS.ICALLINCE.COM=(描述信息,注意connect_data sid或者service_name取值)
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?
rman catalog username/password@rman_catalog_db target user/pass@target_database cmdfile=命令文件
44. Explain the concept of the DUAL table.
dual 表,真是好东西啊,哪天认真分析下
45. What are the ways tablespaces can be managed and how do they differ?
data dict management --freelist,HWM等
extent management --bitmap 管理剩余空间
46. From the database level, how can you tell under which time zone a database is operating?
select * from v$nls_parameters;
47. What's the benefit of "dbms_stats" over "analyze"?
more particular collected statistic data use dbms_stats
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
$oracle_home/bin
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?
exp,drop ,imp
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted.
alter database create datafile '文件' as '原文件';
recover datafile;--记得要恢复,然后online
到网上其他地方看了答案,发觉自觉会做的只有70%,
然后作对的有只有80%左右,
哈哈,不及格的DBA啊,哈哈
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9194732/viewspace-912669/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9194732/viewspace-912669/