Oracle 10g OCP 042 题库 1-30 题 共168题
聲明：對于答案的相關的說明，是個人對Oracle的理解。
1.?Because?of?a?power?outage,instance?failure?has?occurred.?From?what?point?in?the?redo?log?does?recovery?begin?and?where?does?it?end?
A.?Current?redo?log?and?inactive?redo?log
B.?Checkpoint?position?to?end?of?redo?log
C.?Beginning?of?redo?log?to?end?of?redo?log
D.?All?redo?logs?before?the?point?of?last?commit
E.?Beginning?of?redo?log?to?checkpoint?position.
答案B.??Checkpoint?之前的數據已經寫入到數據文件。?所以用restore?就可以恢復。?而checkpoint之后的數據沒有寫入到數據文件，所以需要進行recovery。?Recovery時，對于已經commit的數據，前滾寫入到數據文件。?沒有commit的數據，進行回滾。
2.?Which?two?operations?can?be?flashed?back?using?the?flashback?technology??(choose?two)
A.?Drop?user?smith;
B.?Drop?table?employees;
C.?Drop?tablespace?users;
D.?Alter?table?sales_rep?drop?partition?p1;
E.?Alter?table?employees?drop?column?desig_id;
答案：AB。
Oracle?Flashback?技術?總結
http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4677378.aspx
?
3.?You?have?to?shut?down?the?database?instance?with?the?abort?option?because?of?a?hardware?failure.?Which?statement?is?true?about?the?subsequent?opening?of?the?database?
A.?The?database?would?open?normally.
B.?The?database?would?not?open,and?it?would?stop?at?mount?stage.
C.?The?database?would?open?alter?automatically?performing?instance?recovery.
D.?The?database?would?not?open,?and?have?to?perform?database?recovery?to?open?it.
答案：?C.?
如果實例異常關閉（宕機，shutdown?abort），并且數據文件，控制文件，聯機日志都沒有丟失。在下次啟動時，要利用聯機日志的內容進行恢復，這種恢復就是實例恢復（Instance?Recovery）。
Instance?Recovery?主要包括3個階段：
1）?根據聯機日志內容進行Rollover。
2）?打開數據庫，提供服務
3）?SMON?或者用戶進程進行Rollback。
具體參考?Oracle?備份與恢復概述?中的?3.2?節?恢復種類
http://blog.csdn.net/tianlesoftware/archive/2010/04/16/5490733.aspx
?
?
4.?You?backed?up?the?control?file?to?trace.?Which?statement?is?true?about?the?trace?file?generated?
A.?The?trace?file?is?in?binary?format.
B.?The?trace?file?has?a?SQL?scripts?to?re-create?the?control?file.
C.?The?trace?file?is?a?backup?set?created?during?the?backup?of?the?control?file.
D.?The?trace?file?contains?the?instructions?to?manually?re-create?the?control?file.
E.?The?trace?file?is?an?image?copy?of?the?control?file?created?during?the?backup?of?the?the?control?file.
答案：?B
聯機文檔：
An?alternative?to?the?CREATE?CONTROLFILE?statement?is?ALTER?DATABASE?BACKUP?CONTROLFILE?TO?TRACE,?which?generates?a?SQL?script?in?the?trace?file?to?re-create?the?controlfile
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5003.htm
?
?
5.?While?running?the?Oracle?Universal?Installer?on?a?Unix?platform?to?install?Oracle?Database?10g?software,you?are?prompted?to?run?orainstRoot.sh.?What?does?this?scripts?accomplish?
A.?It?creates?the?pointer?file.
B.?It?creates?the?base?directory.
C.?It?creates?the?inventory?pointer?file.
D.?It?creates?the?Oracle?user?for?installation.
E.?It?modifies?the?Unix?kernel?parmeters?to?match?Oracle's?requirement.
答案：?C
可以查看$ORACLE_BASE/oraInventory/orainstRoot.sh?腳本的內容。該腳本實際上完成了以下工作：
（1）創建software?inventory?location?pointer?file:?/etc/oraInst.loc，內容為
??? ? inventory_loc=$ORACLE_BASE/oraInventory
???? inst_group=oinstall
?? 修改該文件屬性:chmod?644?/etc/oraInst.loc?
（2）創建inventory?directory:?$ORACLE_BASE/oraInventory
?? 修改文件屬性：?chmod?-R?770?$ORACLE_BASE/oraInventory
???????? ???????chgrp?oinstall?$ORACLE_BASE/oraInventory
? oraInventory目錄是用來存儲oracle安裝的所有軟件組件的信息的，每個組件可能占用150k的空間.
?
6.?While?setting?up?an?Oracle?database?for?one?of?your?critical?applications,?you?want?to?ensure?that?the?database?is?backed?up?at?regular?intervals?without?your?intervention(介入).?What?should?you?do?to?achieve?the?objective?
A.?Configure?the?database?to?run?in?archivelog?mode.
B.?Configure?the?Flash?recovery?area?to?enable?automatic?database?backup.
C.?Schedule?the?database?backup?using?DBMS_JOB?package?after?creating?the?database.
D.?Schedule?the?database?backup?using?recovery?manager(RMAN)?commands?after?creating?the?database.
E.?Schedule?the?database?backup?using?Database?Configuration?Assistant(DBCA)?while?creating?the?database.
答案：E.
剛看到這個答案的時候，以為答案有問題。Google一下，真有。?我們注意看題目，在安裝數據庫的時候，確保數據庫在沒有干預的情況下規則的備份。?只有E中的DBCA工具合適了。?而且DBCA?還真有這個功能(玩了幾年Oracle，都沒有留意到，杯具中...)
?
?
7.?The?application?development?team?has?developed?PL/SQL?procedures?and?functions?for?different?purposes?and?calls?them?as?and?when?required.?The?loading?of?individual?procedures?or?functions?into?memory?degrades?performance?with?every?call.?Also,?it?causes?a?security?problem?for?individual?subprograms?and?loss?of?program?units?when?the?whole?system?is?transported?into?a?new?location.?Which?method?would?you?recommend?to?the?application?developers?to?solve?this?problem?
A.?Avoiding?the?use?of?cursors?in?the?subprograms.
B.?Using?anonymous（匿名）?PL/SQL?blocks?instead?of?subprograms.
C.?Referring?to?views?instead?of?tables?inside?the?subprograms.
D.?creating?PL/SQL?packages?to?include?interrelated(相關關聯)?subprograms.
答案：D.
?
8.?View?the?Exhibit.?
Your?Oracle?10g?database?has?6?tablespaces?in?which:?
-TEMP?is?the?default?temporary?tablespace?
-?UNDOTBS1?is?the?default?undo?tablespace?
-?USERS?is?the?default?permanent?tablespace?
In?this?database,?which?three?tablespaces?can?be?made?offline??(Choose?three.)?
?
A)?TEMP?
B)?PROD?
C)?USERS?
D)?SYSAUX?
E)?SYSTEM?
F)?UNDOTBS1?
答案:?BCD。?
可以參考在線文檔中?Overview?of?Tablespaces?小節的內容。
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/physical.htm#CNCPT1089
?
9.??The?junior?DBA?in?your?organization?has?accidentally?deleted?the?alert?log?file.?What?will?you?do?to?create?new?alert?log?file?
A.?Create?the?new?text?file?as?alert.log
B.?You?have?to?recover?the?alert?log?file?from?the?valid?backup.
C.?Change?the?value?for?the?BACKGROUND_DUMP_DEST?parameter.
D.?No?action?required.?The?file?would?be?created?automatically?by?the?instance.
答案：?D.
?
10.?While?planning?to?manage?more?than?one?database?in?your?system,you?perform?the?following?activities:
（1）?organize?different?categories?of?files?into?independent?subdirectories.
（2）?Use?consistent?naming?convention?for?database?files.
（3）?Separate?administrative?information?pertaining?to?each?database.
Which?option?corresponds(一致)?to?the?type?of?activities?performed?by?you?
A.?Oracle?Managed?Files.
B.?Oracle?Grid?Architecture.
C.?Optimal?Flexible?architecture.
D.?Oracle?database?architecture.
答案：C
Optimal?Flexible?Architecture
http://download.oracle.com/docs/cd/B28359_01/install.111/b32002/app_ofa.htm#i633068
?
11.?Data?files?of?which?three?tablespace?can?be?recovered?by?performing?an?open?recovery?(Choose?three)
A.?TEMP
B.?UNDO
C.?INDEX
D.?SYSAUX
E.?SYSTEM
答案：ACD
?
12.??Your?tnsnames.ora?file?has?the?following?entry?for?the?service?alias?ORCL:?
ORCL?=?
(DESCRIPTION?=?
(ADDRESS?=?(PROTOCOL?=?TCP)?(HOST?=?10.156.24.216)?(PORT?=?1521))?
(CONNECT_DATA?=?
(SERVER?=?DEDICATED)?
(SERVICE_NAME?=?orcl.oracle.com)?
)?
)?
The?TNSPING?command?executes?successfully?when?tested?with?ORCL?but?you?are?not?able?to?connect?to?the?
database?instance?with?the?following?command:?
SQL>?CONNECT?scott/tiger@orcl?
What?could?be?the?reason?for?this??
A)?The?listener?is?not?running?at?the?server.?
B)?The?TNS_ADMIN?environmental?variable?is?set?to?a?wrong?value.?
C)?The?orcl.oracle.com?database?service?is?not?registered?with?the?listener.?
D)?The?DEFAULT_DOMAIN?parameter?is?set?to?a?wrong?value?in?the?SQLNET.ORA?file.?
答案：C.
Oracle?Listener?動態注冊?與?靜態注冊
http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5543166.aspx
?
13.?View?the?Exhibit,?and?examine?the?alert?messages.?
You?added?space?to?the?TEST?tablespace?to?bring?the?space?usage?below?the?threshold?value.?
Which?statement?is?true?about?the?Tablespace?Full?alert??
?
A)?The?alert?is?purged?because?it?is?a?threshold?alert.?
B)?The?alert?is?cleared?and?transferred?to?the?alert?log?file.?
C)?The?alert?is?automatically?cleared?and?sent?to?the?alert?history.?
D)?The?alert?appears?in?Oracle?Enterprise?Manager?Database?Control?until?it?is?manually?cleared.?
答案：C
14.?Which?is?the?memory?area?that?is?created?when?a?dedicated?server?process?is?started,?and?contains?data?and?control?information?for?that?server?process?
A.?SGA
B.?PGS
C.?Shared?Pool
D.?Streams?Pool
答案：?B
Oracle?內存?架構?詳解
http://blog.csdn.net/tianlesoftware/archive/2010/05/16/5594080.aspx
15.?Which?two?statements?about?Recovery?Manager(RMAN)?backups?are?true?(Choose?two).
A.?Online?redo?log?files?can?be?backed?up.
B.?Archived?redo?log?files?are?backed?up.
C.?Only?used?data?blocks?can?be?backed?up?as?backup?sets.
D.?Only?consistent?database?backups?can?be?performed.
E.?RMAN?backup?can?be?taken?only?if?the?database?is?configured?in?ARCHIVELOG?mode.
答案：BC
如何?搭建?RMAN?備份平臺
http://blog.csdn.net/tianlesoftware/archive/2010/07/16/5740896.aspx
16.?Which?three?pieces?of?information?are?to?be?manatorily?provided?while?creating?a?new?listener?using?Enterprise?Manager?database?Control??(Choose?three).
A.?The?port?used?by?the?listener.
B.?The?protocol?used?by?the?listener.
C.?The?server?name?where?the?listener?runs.
D.?The?log?file?and?trace?file?destination?for?the?listener.
E.?The?database?services?to?be?registered?with?the?listener.
答案：ABC
17.?The?SCOTT?user?has?an?index?on?the?ITEM_DESC?column?of?the?ITEM?table.?As?part?of?the?year-ending?task,?SCOTT?updates?the?ITEM_DESC?column?for?most?of?the?rows?in?the?ITEM?table.?How?does?this?change?to?the?table?affect?the?index?
A.?An?update?in?a?leaf?row?takes?place.
B.?The?index?becomes?invalid?after?the?update.
C.?The?leaf?block?containing?the?row?to?be?updated?is?marked?as?invalid.
D.?A?row?in?the?leaf?block?of?the?index?for?the?key?value?is?deleted?and?inserted.
答案：D
18.?In?the?middle?of?a?transaction,a?user?session?was?abnormally?terminated?but?the?instance?is?still?up?and?the?database?is?open.?Which?two?statements?are?true?in?the?scenario（方案）??(Choose?two).
A.?Event?viewer?gives?more?details?on?the?failure.
B.?The?alert?log?file?gives?detailed?information?about?the?failure.
C.?PMON?rolls?back?the?transaction?and?releases?the?locks.
D.?SMON?rolls?back?the?transaction?and?releases?the?locks.
E.?The?transaction?is?rolled?backup?by?the?next?session?that?refers?to?any?of?the?blocks?updated?by?the?failed?transaction.
F.?Data?modified?by?the?transaction?up?to?the?last?commit?before?the?abnormal?termination?is?retained?in?the?database.
答案：C?F
19.?The?application?workload?on?your?database?is?same?bwtween?10?a.m.?And?11?a.m.?On?weekdays.?Suddenly?you?observe?poor?performance?between?10?a.m.?And?11?a.m.?In?the?middle?of?the?week.?How?would?you?identify?the?changes?in?configuration?settings,?workload?profile,?and?statistics?to?diagnose?the?possible?causes?of?the?performance?degradation?
A.?By?using?the?SQL?access?advisor
B.?By?using?the?automatic?workload?repository?report.
C.?By?running?the?automatic?database?diagnostic?monitor(ADDM)
D.?By?using?the?automatic?workload?repository?(AWR)?compare?period?report.
E.?By?analyzing?the?output?of?the?v$ACTIVE_SESSION_HISTORY?view.
答案：D
Oracle?AWR?介紹
http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx
20.?Your?database?is?in?NOARCHIVELOG?mode.?After?which?two?operations?you?should?take?the?backup?of?the?control?file?
A.?Adding?a?new?user?to?the?database.
B.?Dropping?a?user?from?the?database.
C.?Dropping?a?table?from?the?database.
D.?Dropping?a?data?file?from?a?tablespace.
E.?Adding?a?new?tablespace?to?the?database.
答案：?D?E.?控制文件里保存了數據文件的信息，所以添加刪除都會修改控制文件里的內容，所以需要修改。
Oracle?控制文件
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4974440.aspx
21.?Which?three?statements?are?true?regarding?the?logical?structure?of?the?Oracle?database??(Choose?three).
A.?Each?segment?contains?one?or?more?extents.
B.?Multiple?tablespace?can?share?single?data?file.
C.?A?data?block?is?the?smallest?unit?of?I/O?for?data?files.
D.?It?is?possible?to?have?tablespace?of?different?block?sizes?in?a?database.
E.?Each?data?block?in?the?database?always?corresponds?to?one?OS?block.
答案：ACD
表空間(tableSpace)?段(segment)?盤區(extent)?塊(block)?關系
http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4962476.aspx
22.??You?executed?the?following?command?to?start?the?database:?
SQL>?STARTUP?
ORACLE?instance?started.?
Total?System?Global?Area?281018368?bytes?
Fixed?Size?789000?bytes?
Variable?Size?229635576?bytes?
Database?Buffers?50331648?bytes?
Redo?Buffers?262144?bytes?
ORA-00205:?error?in?identifying?controlfile,?check?alert?log?for?more?info?
Which?view?would?you?query?at?this?stage?to?investigate?this?missing?control?file??
A)?V$INSTANCE?
B)?V$CONTROLFILE?
C)?DBA_CONTROL_FILES?
D)?V$DATABASE_PROPERTIES?
E)?V$CONTROLFILE_RECORD_SECTION?
答案:?B
23.?Which?two?are?valid?locking?levels?that?are?used?by?transactions?in?an?Oracle?database??(Choose?two)
A.?Row?level
B.?Block?level
C.?Object?level
D.?Schema?level
E.?Database?level
答案：?AC
在這個地方研究了半天，也不是十分明白。?
ORACLE?鎖機制
http://blog.csdn.net/tianlesoftware/archive/2009/10/20/4696896.aspx
http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/consist.htm#CNCPT1341
24.??Your?database?is?started?by?using?the?server?parameter?file?(SPFILE).?You?issued?this?command?to?change?
the?value?of?the?LOG_BUFFER?initialization?parameter:?
ALTER?SYSTEM?SET?LOG_BUFFER=24M?SCOPE=BOTH;?
What?would?be?the?outcome?of?this?command??
A)?The?command?would?return?an?error?because?LOG_BUFFER?is?a?static?parameter.?
B)?The?parameter?value?would?be?changed?and?it?would?come?into?effect?immediately.?
C)?You?need?to?restart?the?database?so?that?parameter?changes?can?come?into?effect.?
D)?The?command?would?succeed?only?if?initialization?parameter?LOG_ARCHIVE_MAX_PROCESS?is?set?to?value?2.?
答案：?A.
LOG_BUFFER?specifies?the?amount?of?memory?(in?bytes)?that?Oracle?uses?when?buffering?redo?entries?to?a?redo?log?file.?Redo?log?entries?contain?a?record?of?the?changes?that?have?been?made?to?the?database?block?buffers.?The?LGWR?process?writes?redo?log?entries?from?the?log?buffer?to?a?redo?log?file.
The?log?buffer?size?depends?on?the?number?of?redo?strands?in?the?system.?One?redo?strand?is?allocated?for?every?16?CPUs?and?has?a?default?size?of?2?MB.?Oracle?allocates?a?minimum?of?2?redo?strands?per?instance.?When?the?log?buffer?size?is?not?specified,?any?remaining?memory?in?the?redo?granules?is?given?to?the?log?buffer.
LOG_BUFFER
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams127.htm#REFRN10094
SQL>?select?name,issys_modifiable?from?v$parameter?where?name='log_buffer';
NAME???????????ISSYS_MOD
--------------------? ---------
log_buffer?????????FALSE
通過這個查詢，我們知道，修改這個參數必須重啟數據庫。
?
25.?Which?two?statements?are?true?about?the?roles?in?the?Oracle?database??(Choose?two)
A.?A?role?can?be?granted?to?itself.
B.?Roles?are?owned?by?the?sys?user.
C.?Roles?can?be?granted?to?other?roles.
D.?A?role?can?not?be?assigned?external?authentication.
E.?A?role?can?contain?both?system?and?object?privileges.
答案：?C?E
Oracle?用戶及角色?介紹
http://blog.csdn.net/tianlesoftware/archive/2009/11/09/4786956.aspx
26.?You?have?been?recently?hired?as?a?database?administrator.?Your?senior?manager?asks?you?to?study?the?
production?database?server?and?submit?a?report?on?the?settings?done?by?the?previous?DBA.?While?observing?
the?server?settings,?you?find?that?the?following?parameter?has?been?set?in?the?parameter?file?of?the?database:?
REMOTE_OS_AUTHENT?=?TRUE?
What?could?have?been?the?reason?to?set?this?parameter?as?TRUE??
A)?to?enable?operating?system?authentication?for?a?remote?client?
B)?to?restrict?the?scope?of?administration?to?identical?operating?systems?
C)?to?allow?the?start?up?and?shut?down?of?the?database?from?a?remote?client?
D)?to?enable?the?administration?of?the?operating?system?from?a?remote?client?
E)?to?disable?the?administration?of?the?operating?system?from?a?remote?client?
答案：?A
參考：http://download.oracle.com/docs/cd/E11882_01/network.112/e10746/asoauth.htm#ASOAG9769
27.??View?this?parameter?setting?in?your?database:?
DB_CREATE_FILE_DEST='D:?/oracle/product/10.2.0/oradata/oracle'?
You?created?a?tablespace?by?using?this?command:?
CREATE?TABLESPACE?USERS;?
Which?two?statements?are?true?about?the?USERS?tablespace??(Choose?two.)?
A)?The?tablespace?has?two?data?files.?
B)?An?error?is?reported?and?tablespace?creation?fails.?
C)?Data?files?are?created?with?names?generated?by?the?instance.?
D)?The?tablespace?can?be?extended?without?specifying?the?data?file.?
E)?Data?files?belonging?to?the?USERS?tablespace?cannot?be?renamed.
答案：C?D
28.?View?the?Exhibit?to?see?the?source?and?target?databases.?
You?have?created?a?database?link,?devdb.us.oracle.com,?between?the?databases?PRODDB?and?DEVDB.?You?
want?to?import?schema?objects?of?the?HR?user?using?Oracle?Data?Pump?from?the?development?database,?
DEVDB,?to?the?production?database,?PRODDB.?You?execute?the?following?command?on?the?target?database?
server:?
$impdp?system/manager?directory?=?DB_DATA?
dumpfile?=?schemas.dat?
schemas?=?hr?
flashback_time?=?2004-02-03?09:?00?
The?command?fails,?displaying?the?following?error:?
ORA-39001:?invalid?argument?value?
ORA-39000:?bad?dump?file?specification?
ORA-31640:?unable?to?open?dump?file?"/home/oracle/schema/schemas.dat"?for?read?
ORA-27037:?unable?to?obtain?file?status?
What?would?you?do?to?overcome?the?error??
A)?remove?the?dumpfile?option?in?the?command?
B)?remove?the?flashback_time?option?in?the?command?
C)?add?the?user,?SYSTEM,?to?the?schemas?option?in?the?command?
D)?add?network_link?=?devdb.us.oracle.com?option?in?the?command?
E)?remove?the?schemas?option?and?add?the?network_link?=?devdb.us.oracle.com?option?in?the?command?
F)?remove?the?dumpfile?option?and?add?the?network_link?=?devdb.us.oracle.com?option?in?the?command?
答案：F
29.?What?is?the?implication?of?setting?the?initialization?parameter?FAST_START_MTTR_TARGET?to?0?in?your?
database??
A)?MTTR?Advisor?would?be?disabled.?
B)?Redo?Log?Advisor?would?be?disabled.?
C)?Automatic?tuning?of?checkpoint?would?be?disabled.?
D)?Checkpoint?information?would?not?be?written?to?the?alert?log?file.?
答案：?C
FAST_START_MTTR_TARGET?enables?you?to?specify?the?number?of?seconds?the?database?takes?to?perform?crash?recovery?of?a?single?instance.?When?specified,?FAST_START_MTTR_TARGET?is?overridden?by?LOG_CHECKPOINT_INTERVAL.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams084.htm#REFRN10058
30.?Users?of?HR?schema?complain?about?slower-than-normal?performance.?On?investigation?the?DBA?found?that?maintenance?was?recently?performed?on?some?of?the?tables.?The?DBA?traced?the?query?that?takes?longer?than?normal?to?execute.?
View?the?Exhibit?exhibit_before.?
?
After?the?DBA?resolves?the?problem,?the?query?performs?normally.?
View?the?Exhibit?exhibit_after.?
What?action?would?the?DBA?have?taken?to?resolve?the?performance?problem??
A)?analyzed?the?EMPLOYEES?table?to?collect?the?current?statistics?
B)?moved?the?EMPLOYEES?table?in?to?a?locally?managed?tablespace?
C)?moved?the?EMPLOYEES?table?to?another?location?in?the?same?tablespace?
D)?reorganized?the?associated?indexes?for?the?EMPLOYEES?table?that?were?in?an?unusable?state?
E)?moved?the?indexes?associated?with?the?EMPLOYEES?table?to?the?same?tablespace?where?the?EMPLOYEES?table?exists?
答案：?D
?
------------------------------------------------------------------------------?
Blog：?http://blog.csdn.net/tianlesoftware?
網上資源：?http://tianlesoftware.download.csdn.net?
相關視頻：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx?
DBA1?群：62697716(滿);?DBA2?群：62697977
DBA3?群：63306533;?????聊天?群：40132017
轉載于:https://www.cnblogs.com/Hiberniane/archive/2010/07/26/2488631.html
總結
以上是生活随笔為你收集整理的Oracle 10g OCP 042 题库 1-30 题 共168题的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： shell 脚本 自动发送邮件
- 下一篇： 第 2 章 常量、变量和表达式