国外公司的Oracle DBA 面试题
上一篇 / 下一篇 2007-02-02 15:39:59 / 精华(1)
查看( 7000 ) /
评论( 47 )
TAG:
-
wind_sky
发布于2006-08-02 08:33:08
-
看来我及格是没问题啦
-
David.Guo
发布于2006-08-02 08:34:48
-
我晕,我都看不懂,哈哈
老大厉害,小弟pf呀
-
wind_sky
发布于2006-08-02 09:25:05
-
QUOTE:
原帖由 David.Guo 于 2006-8-2 08:34 发表
偶纸上谈兵的说，兄弟才是应用派。偶汗
我晕,我都看不懂,哈哈
老大厉害,小弟pf呀
-
brotherxiao
发布于2006-08-02 09:42:09
-
部分都是how and what,基本没有yes or no,回答起来还是有难度吖
那位老大给解答下，偶也好学习下
-
mget
发布于2006-08-02 10:23:21
-
好东西，学习！
-
wind_sky
发布于2006-08-02 13:58:00
-
偶先抢答一个啊，谁给加分啊？
7. How do you tell what your machine name is and what is its IP address？
解答:hostname看主机名，ifconfig -a看ip地址.(windows下ipconfig)
-
brotherxiao
发布于2006-08-02 14:47:18
-
QUOTE:
原帖由 wind_sky 于 2006-8-2 13:58 发表
我感觉出题者的意思像是如何从oracle中获取主机名和ip地址吧hehe
偶先抢答一个啊，谁给加分啊？
7. How do you tell what your machine name is and what is its IP address？
解答:hostname看主机名，ifconfig -a看ip地址.(windows下ipconfig)
-
wind_sky
发布于2006-08-02 15:06:12
-
QUOTE:
原帖由 brotherxiao 于 2006-8-2 14:47 发表
hehehe,你就是不想让俺及格呗，难不倒俺，不就是主机名和ip地址吗，说吧，取客户端的还是服务器的:o
我感觉出题者的意思像是如何从oracle中获取主机名和ip地址吧hehe
-
brotherxiao
发布于2006-08-02 15:33:04
-
QUOTE:
原帖由 wind_sky 于 2006-8-2 15:06 发表
那敢不让老大及格呢
hehehe,你就是不想让俺及格呗，难不倒俺，不就是主机名和ip地址吗，说吧，取客户端的还是服务器的:o 一道题也难不倒你吖
只是觉得前面的题里面都是问的数据库相关的，我是从出题者的心思猜摸而已呵呵
-
Steven.Wang发布于2006-08-02 15:36:24
-
QUOTE:
原帖由 wind_sky 于 2006-8-2 15:06 发表
取服務器的主機名與IP
hehehe,你就是不想让俺及格呗，难不倒俺，不就是主机名和ip地址吗，说吧，取客户端的还是服务器的:o
-
blacktie发布于2006-08-02 15:40:23
-
楼上的斑斑们,不要互相恭维啦,解决问题才是硬道理啊.也让俺们学习学习.:lol
-
wind_sky
发布于2006-08-02 15:52:39
-
7题，如果是oracle中查询主机名方法倒是多，v$instance,v$session很多，ip吗还真不会直接从sql取,,,,
-
wind_sky
发布于2006-08-02 15:53:44
-
愿意答题的同志就答呗，答题接龙啦
-
dyb2000发布于2006-08-17 21:15:54
-
没答案啊.呵...
-
jametong发布于2006-08-18 00:24:22
-
1. How many memory layers are in the shared pool？
主要有以下几块组成, fixed tunnable variable (processes,sessions,transactions,dml_locks,buffer headers).
library cache (parsed sql /plsql representation, the main purpose of shared pool).
row cache /dictionary cache (cache of data dictionary rows).
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up？
I don't know , just backup archivelog all not backup 1 time.
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？
use proper log buffer .normally 512k/1m
use proper shared pool size normally 150m-200m.
if not use jave , set java_pool_size to 0
if not use rman/mts/parallel set large_pool_size to minimize , normally 8m
the other memory set to buffer cache, the method is below.
？ What is involved in tuning the SGA？
put different data into different buffer pools,small frequently used tables put into keep pool.
large seldom used tables put into recycle pool. others mainly put into default pool. if some table physical reads is enomous, then consider to put some segments(index/data) into keep pool.
if possible use 40%-60% of memory for buffer cache.
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？
cache hit ration = 1 - physical reads/(consistents gets + db block gets)
consistents gets refers to select query or none dml part of a dml statement.
db blocks gets refers to blocks that have be modified/created .
phisical reads refers to blocks that reads directly from disks.
logical reads are blocks that directly read from memory.
if this ratio is too small , may the system have too much disk reads, we should consider to tune the memory usage, and try to tune the sql statements with so much disk reads.
but now we prefer to use the wait events to tune the system. I prefer to use top 5/10 events togather with top 5/10 logical/disk reads to tune the system.
bad sql is the cause 90% system problems .
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？
1). tablespace usage. dba_free_space
2). invalidate objects/indexes. dba_objects/dba_indexes
3). top large data segments / top large index segment dba_segments
4). active transaction list. v$transaction
5) active session list v$session.active
6) active lock list v$lock
7) session distribution between machines/applications. v$session, group by machine,owner,idle_time
8). system resource usage v$resource_usage
7. How do you tell what your machine name is and what is its IP address？
hostname
ifconfig
nerstat -nr
uname -r
8. How would you go about verifying the network name that the local_listener is currently using？
tnsping network_name;
ping ip_address.
sqlplus user/pass@network_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？
ipcs
ps -ef
10. What view（s） do you use to associate a user's SQLPLUS session with his o/s process？
v$process.addr = v$session.paddr
-
jametong发布于2006-08-18 00:32:46
-
11. What is the recommended interval at which to run statspack snapshots， and why？
15-30 minutes at busy time. the statistics can show the spot of the system. and also will not be wipe out by useless information of the system.
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？
not clearly . must use show parameter optimize.
show parameter index
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.
conn scott
var job_no number
dbms_job.submit(:job_no,trunc(sysdate+3+9/24),'trunc(sysdate+1) + 2/24');
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM？
crontab -e
#add new job
0 14 * * * /test/test.sh >/tmp/test.log 2>&1
15. What do the 9i dbms_standard.sql_txt（） and dbms_standard.sql_text（） procedures do？
dont know.
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed？
dba/user_mviews
dba_jobs/user_jobs.(refer to the specified mview).
17. How would you best determine why your MVIEW couldn't FAST REFRESH？
1) is there any mview log.
2) is therer any primary key exists.
18. How does propagation differ between Advanced Replication and Snapshot Replication （readonly）？
dont know.
19. Which dictionary view（s） would you first look at to understand or get a high-level idea of a given Advanced Replication environment？
dont know
20. How would you begin to troubleshoot an ORA-3113 error？
1) is the network has some problem.
2) is the db has some problem.
3) has somebody killed the session.
-
jametong发布于2006-08-18 00:43:47
-
21. Which dictionary tables and/or views would you look at to diagnose a locking issue？
v$lock/v$locked_objects/v$enqueue_lock/dba_locks/dba_ddl_locks/dba_lock_internals.
x$kgllk,x$kglpn(library cache lock/pins).
22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed"， how do you approach troubleshooting this issue？
1) check the error no with alert_log. or jobq trace file.
2) check job parameters.
23. How would you extract DDL of a table without using a GUI tool？
exp rows=n
imp indexfile=aaa.sql
select dbms_metadata.get_ddl('table_name','owner','TABLE') from dual;
just generate it from dba_tables/dba_tab_columns/dba_tab_partitions/dba_part_tables/...
24. You're getting high "busy buffer waits" - how can you find what's causing it？
1) find the hot block. with the v$session_wait/trace file/v$bh
2) find the main sql statement .
3) tune it.
25. What query tells you how much space a tablespace named "test" is taking up， and how much space is remaining？
dba_free_spaces/dba_extents
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.
1) . first view active sessions. and v$session_wait .
2). see the uptime and top processes of the system.
3) analyze the main reason. and tune it.
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.
instance recovery
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses （LINUX and/or Solaris）。
cat /proc/cpuinfo
top
29. How do you increase the OS limitation for open files （LINUX and/or Solaris）？
umilit
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.
sqlplus /nolog <<_END_
connect / as sysdba
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select sysdate from dual;
set time on timing on
drop table table_name;
select sysdate from dual;
exit;
_END_
-
jametong发布于2006-08-18 00:50:38
-
31. Explain how you would restore a database using RMAN to Point in Time？
restore database until time '2006-08-18 00:00:00';
32. How does Oracle guarantee data integrity of data changes？
use constraints / isolation level
33. Which environment variables are absolutely critical in order to run the OUI？
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 = '<USERNAME>';
35. Why does Oracle not permit the use of PCTUSED with indexes？
dont know.
36. What would you use to improve performance on an insert statement that places millions of rows into that table？
1) at idle time.
2) direct insert /*+append*/
3) if possible nologging
4) if possible parallel
5) if possible disable constraints(foreign key/primary key)
6) if possible disable index.
37. What would you do with an "in-doubt" distributed transaction？
not clearly.
38. What are the commands you'd issue to show the explain plan for "select * from dual"？
explain plan for
select * from dual;
@?/rdbms/admin/utlxplp.sql
select * from (dbms_xplan.display)
39. In what script is "snap$" created？ In what script is the "scott/tiger" schema created？
sql.bsq.
demo
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/sqlplus/admin
-
jametong发布于2006-08-18 01:00:35
-
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.ica
42. You create a private database link and upon connection，
fails with： ORA-2085： connects to .
oerr ora 2085 to know details of this errors.
use google to search some answers.
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 target / catalog rman/rman <<_END_
rman script here.
_END_
shell_script &
44. Explain the concept of the DUAL table.
a table with only one row and one column.
to help show expressions internally in oracle.
45. What are the ways tablespaces can be managed and how do they differ？
dmt dictionary managed tablespace (permenent tablespace/temprary tablespace).
lmt local managed tablespaces.
uniform sized extents
system managed extents
freelist managed space allocation
bitmap managed space allocation
permenent tablespace
temporary tablespace datafile
temporary tablespace tempfile
undo tablespace
46. From the database level， how can you tell under which time zone a database is operating？
select * from nls_database_parameters;
select * from nls_instance_parameters;
47. What's the benefit of "dbms_stats" over "analyze"？
too much, not very clear.
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？
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.
alter database create datafile 'file_path' as 'file_path';
recover datafile;
-
David.Guo
发布于2006-08-18 09:05:44
-
老大出手,果然不凡,先学习学习
主要是题目看不懂丫
