Oracle 10g OCP 042 题库 121-166题 共168题

121.Youwanttocreateanewoptimizeddatabaseforyourtransactionalproductionenvironmenttobeusedbyafinancialapplication.Whilecreatingthedatabase,youwanttheOraclesoftwaretotakecareofallbasicsettingstooptimizethedatabaseperformance.Whichmethodwouldyouusetoachievethisobjective?

A)UsetheCREATEDATABASE..commandtocreatethedatabasewithOracle-managedfiles.

B)UsetheDatabaseConfigurationAssistant(DBCA)tocreatethedatabasewithOracle-managedfiles.

C)UseEnterpriseManagertocreateanewdatabasewiththeOnlineTransactionProcessing(OLTP)option.

D)UseDatabaseConfigurationAssistant(DBCA)tocreatethedatabasewithTransactionProcessingtemplate.

E)UsetheCREATEDATABASE..commandtocreatethedatabasewithAutomaticStorageManagement(ASM)filesystem.

答案：D

122.YoureceivedcomplaintsaboutthedegradationofSQLqueryperformance.YouidentifiedtopSQLqueriesthatconsumetime.WhatwouldbeyournextsteptofindoutrecommendationsaboutstatisticscollectionandrestructuringoftheSQLstatementtoimprovequeryperformance?

A)runSegmentAdvisor

B)runSQLTuningAdvisorontopSQLstatements

C)runtheAutomaticWorkloadRepository(AWR)report

D)runtheAutomaticDatabaseDiagnosticMonitor(ADDM)ontopSQLstatements

答案：B

123.ViewtheExhibit.

Whichstatementregardingthedeptandemptablesistrue?

A)Whenyoudeletearowfromtheemptable,youwouldreceiveaconstraintviolationerror.

B)Whenyoudeletearowfromthedepttable,youwouldreceiveaconstraintviolationerror.

C)Whenyoudeletearowfromtheemptable,automaticallythecorrespondingrowsaredeletedfromthedepttable.

D)Whenyoudeletearowfromthedepttable,automaticallythecorrespondingrowsaredeletedfromtheemptable.

E)Whenyoudeletearowfromthedepttable,automaticallythecorrespondingrowsareupdatedwithnullvaluesintheemptable.

F)Whenyoudeletearowfromtheemptable,automaticallythecorrespondingrowsareupdatedwithnullvaluesinthedepttable.

答案：D

124.UserScotthasupdatedthesalaryofoneoftheemployeesintheEMPLOYEEStableandhasnotcommittedthetransaction.Whatarethetwotypesoflocksthatthisscenariowouldleadto?(Choosetwo.)

A)nulllockontherowbeingupdated

B)nulllockonthetablecontainingtherow

C)ROWSHARElockfortherowbeingupdated

D)ROWEXCLUSIVElockfortherowbeingupdated

E)sharedrow-exclusivelockfortherowbeingupdated

F)ashareabletablelockforthetablecontainingtherow

G)exclusivetable-levellockforthetablecontainingtherow

答案：DF

125.ViewtheExhibitandexaminetheoutput.

Whichstatementdescribestheconclusion?

A)Theusersshouldusebindvariablesinsteadofliteralsinthequery.

B)Thedictionarycacheisconsumingmorespacethanthelibrarycache.

C)ThesharedpoolsizeshouldbeincreasedtoaccommodatetheSQLstatements.

D)PreparingindexesonthetablesusedintheSQLstatementswouldimprovethelibrarycacheperformance.

答案：A

126.YourdatabaseisconfiguredinNOARCHIVELOGmode.Allthecontrolfileshavebeenlostduetoaharddiskfailurebutthedatafilesarenotlost.Youhavetheclosedwholedatabasebackupavailabletoyou.Whichtwostatementsaretrueinthisscenario?(Choosetwo.)

A)Theinstanceaborts.

B)Thedatabasecannotberecovered.

C)Thedatabasecanberecoveredbyrestoringthecontrolfilesfromthebackup.

D)ThedatabaseremainsopenedandyouhavetoshutitdownwiththeABORToption.

E)Thedatabasecanberestoredtillthepointofthelastclosedwholedatabasebackup.

答案：AE

127.WhilerunningtheOracleUniversalInstalleronaUnixplatformtoinstallOracleDatabase10gsoftware,youarepromptedtorunorainstRoot.shscript.Whatdoesthisscriptaccomplish?

A)Itcreatesthepointerfile.

B)Itcreatesthebasedirectory.

C)ItcreatestheInventorypointerfile.

D)ItcreatestheOracleuserforinstallation.

E)ItmodifiestheUnixkernelparameterstomatchOracle'srequirement.

答案：C

128.YourdatabaseisstartedwithSPFILE.Youwantthedatabaseinstancetobedynamicallyregisteredwitha

listenerL2withthefollowingdetails:

Protocol:TCP

Host:indl151e

Port:1525

Whichisthecorrectorderofthestepsthatyouwouldfollowtoachievethis?

1.SettheLOCAL_LISTENERparametertoL2dynamically.

2.MakeanentryforL2intnsnames.oraonthedatabaseserver.

3.RestartL2.

4.Modifythelistener.orafiletoaddtheinstancenameinSID_LISTofL2.

A)1,2,4,3

B)1,2,3;4isnotrequired.

C)2,1;3and4arenotrequired.

D)1,2;3and4arenotrequired.

答案：C

129.ThejuniorDBAinyourorganizationhasaccidentallydeletedthealertlogfile.Whatwillyoudotocreatenewalertlogfile?

A)CreatethenewtextfilefileasALERT.LOG.

B)Youhavetorecoverthealertlogfilefromthevalidbackup.

C)ChangethevaluefortheBACKGROUND_DUMP_DESTparameter.

D)Noactionrequired.Thefilewouldbecreatedautomaticallybytheinstance.

答案：D

130.ViewtheExhibit.

YourOracle10gdatabasehas6tablespacesinwhich)

-TEMPisthedefaulttemporarytablespace

-UNDOTBS1isthedefaultundotablespace

-USERSisthedefaultpermanenttablespace

Inthisdatabase,whichtwotablespacescanbemadereadonly?(Choosetwo.)

A)TEMP

B)PROD

C)USERS

D)SYSAUX

E)SYSTEM

F)UNDOTBS1

答案：BC

131.YouareusingthebackupschedulerinEnterpriseManager(EM)toscheduleabackupofyourdatabase.Whichtypeofscriptdoesthebackupschedulergenerate?

A)SQLscript

B)PL/SQLscript

C)OperatingSystem(OS)script

D)EnterpriseManager(EM)script

E)RecoveryManager(RMAN)script

答案：E

132.Youareinthemiddleofatransactionandverycrucialdatahasbeenmodified.Becauseofahardwarefailure,theinstancehasshutdownbeforesynchronizingallthedatabasefiles.Whichtwostatementsaretrue?(Choosetwo.)

A)Onstartup,SMONcoordinatesinstancerecovery.

B)Onstartup,CKPTcoordinatesinstancerecovery.

C)Onstartup,useRMANtoperforminstancerecovery.

D)Uncommittedchangeswillberolledbackafterthedatabaseisopened.

E)Onstartup,performmediarecoveryandtheninstancerecovery.

F)Onstartup,allthefileswillbesynchronizedandyougetbothcommittedanduncommitteddata.

答案：AD

133.Afterbeinghiredasadatabaseadministrator,youfindthatthereisonlyonedatabasethatisfunctionalandthatisbeingaccessedbytheapplications.Youwanttocreateareplicaofthedatabase,tobeusedfortestingpurposes.Whatisthebestmethodtocreatethereplica?

A)createadatabasebyusingCREATEDATABASE..commandandmanuallycopythedata

B)useDatabaseConfigurationAssistant(DBCA)tocreateatemplatefromtheexistingdatabasetocontainthedatabasestructure

C)useDBCAtocreateatemplatefromtheexistingdatabasetocontainthedatabasestructureandthenmanuallycopythedatausingOracleDataPump

D)useDBCAtocreateatemplatefromtheexistingdatabasetocontainthedatabasestructurewithdatafilesandthenusethesametemplatetocreatethedatabaseinthenewlocation

答案：D

134.Youareworkingonanonlinetransactionprocessing(OLTP)system.YounoticethataPL/SQLproceduregotexecutedtwiceat2:00p.m.ThishasincorrectlyupdatedtheEMP_SALtable.Howwouldyourevertthetabletoitsstateat2:00p.m.?

A)Performpoint-in-timerecoveryto2:00p.m.

B)UseFlashbackTablefeaturetorevertthechanges.

C)Restoretheentiredatabasefromtherecentbackupandopenit.

D)Issuetherollbackstatementwithsystemchangenumber(SCN).

答案：B

135.ViewtheExhibit.

WhatwouldhappenifyouchangethevalueofDesiredMeanTimeToRecoverto30?

A)TheMTTRAdvisorwouldbeenabled.

B)TheRedoLogAdvisorwouldbedisabled.

C)Automaticcheckpointtuningwouldbedisabled.

D)Theredologfromlogbufferswouldbewrittentoredologfilesataslowerrate.

答案：A

136.ViewtheExhibittoobservetheprivilegesgrantedtotheMGR_ROLErole.

TheSCOTTuserhasbeengrantedtheCONNECTandRESOURCErolesonly.Thedatabaseadministrator(DBA)grantsMGR_ROLEtotheSCOTTuserbyusingthiscommand:

SQL>GRANTMGR_ROLETOSCOTTWITHADMINOPTION;

WhichstatementistrueabouttheSCOTTuserafterheisgrantedthisrole?

A)TheSCOTTusercangranttheMGR_ROLEroleandtheprivilegesinittootherusers.

B)TheSCOTTusercangranttheprivilegesintheMGR_ROLEroletootherusersbutnotwithADMIN_OPTION.

C)TheSCOTTusercangrantonlytheMGR_ROLEroletootherusersbutnottheprivilegesinit.

D)TheSCOTTusercangranttheprivilegesintheMGR_ROLEroletootherusersbutcannotrevokeprivilegesfromthem.

答案：C

137.Whichtwostatementsaretrueaboutsimpleviews?(Choosetwo.)

A)Viewscanbecreatedasreadonly.

B)Viewsaredatasegmentsliketables.

C)Viewscanbecreatedonthebasisofmorethanonetable.

D)Datamanipulationlanguage(DML)operationscannotbeperformedonviews.

答案：AC

138.Whichnamingmethodusesthetnsnames.orafiletostoretheconnectdescriptorusedbytheclientwhileconnectingtothedatabaseinstancefromaremotemachine?

A)hostnamingmethod

B)localnamingmethod

C)externalnamingmethod

D)directorynamingmethod

答案：B

139.YourdatabaseisrunningintheARCHIVELOGmode.Youplacedatablespace,tbs_1,offlinewiththeimmediateoption.Whichstatementiscorrectinthisscenario?

A)Theoperationwouldfailiftbs_1werearead-onlytablespace.

B)Theoperationwouldfailiftbs_1werearead/writetablespace.

C)Theoperationwouldfailiftbs_1werethedefaulttablespaceforthedatabase.

D)Iftheaboveoperationweresuccessful,mediarecoverywouldberequiredtobringthetablespaceonline.

E)Iftheaboveoperationweresuccessful,instancerecoverywouldberequiredtobringthetablespaceonline.

答案：D

140.Youworkinadatawarehouseenvironmentthatinvolvestheexecutionofcomplexqueries.ThecurrentcontentoftheSQLcacheholdstheidealworkloadforanalysis.Youwanttoanalyzeonlyfewmostresource-intensivestatements.Whatwouldbeyoursuggestiontoreceiverecommendationsontheefficientuseofindexesandmaterializedviewstoimprovequeryperformance?

A)RuntheSQLAccessAdvisor.

B)RuntheSQLTuningAdvisor(STA).

C)RuntheAutomaticWorkloadRepository(AWR)report.

D)RuntheAutomaticDatabaseDiagnosticMonitor(ADDM).

答案：A

141.Youhavealargeamountofhistoricaldatainanoperatingsystemfile.Someanalystsinyourorganizationneedtoquerythisdata.Thefileistoolargetoloadintoyourcurrentdatabase.Whichisthemosteffectivemethodtoaccomplishthetask?

A)Upgradethehardware/memorytoaccommodatethedata.

B)LoadthedataintoyourdatabasebyusingthePARALLELclause.

C)GiveanalystsDBAprivilege,sothattheycanqueryDBA_EXTERNAL_TABLES.

D)Useanexternaltablesoyoucanhavethemetadataavailableinyourdatabase,butleavethedataintheoperatingsystemfiles.

答案：D

142.Whichtwostatementsaretrueaboutabitmapindex?(Choosetwo.)

A)Itisrecommendedforthecolumnsthathaveuniquevalues.

B)ItcanbeconvertedtoaB-treeindexbyusingtheALTERINDEXcommand.

C)Ithasabitmapsegmentforeachdistinctvalueinthekeycolumn,containingastringofbitsinwhicheachbitrepresentsthepresenceorabsenceofakeycolumnvalue.

D)Updatingthekeycolumnlocksthewholebitmapsegmentthatcontainsthebitforthekeyvaluetobeupdated.

答案：CD

143.UserSCOTTwantstoexporthisobjectsusingOracleDataPumpandexecutesthefollowingcommand:

$expdpscott/tiger

directory=EXPORT_DIR

dumpfile=scott.dmp

include=table

include=view："like'%DEPARTMENTS%'"

content=DATA_ONLY

Whichtaskwouldthecommandaccomplish?

A)OracleDataPumpwouldexportonlythedataofallofthetablesandviews.

B)OracleDataPumpwouldexportallofthetablestructuresalongwithdataandalltheviews.

C)OracleDataPumpwouldexportthetabledataandtheviewdefinitionswheretheviewnamecontainsastringnamedDEPARTMENTS.

D)OracleDataPumpwouldexportthetabledataandtheviewdefinitionswithdatawhereviewnamecontainsastringnamedDEPARTMENTS.

E)OracleDataPumpwouldexportallofthetablestructuresandtheviewdefinitionswithdatawhereviewnamecontainsastringnamedDEPARTMENTS.

答案：C

144.WhichtwostatementsregardingtheLOGGINGclauseoftheCREATETABLESPACE...statementarecorrect?(Choosetwo.)

A)Thisclauseisnotvalidforatemporaryorundotablespace.

B)IfthetablespaceisintheNOLOGGINGmode,nooperationonthetablespacewillgenerateredo.

C)ThetablespacewillbeintheNOLOGGINGmodebydefault,ifnotspecifiedwhilecreatingatablespace.

D)Thetablespace-levelloggingattributecanbeoverriddenbyloggingspecificationsatthetable,index,materializedview,materializedviewlog,andpartitionlevels.

答案：AD

145.Youwanttomonitorandcontroltheresourceusagebysessions.Youwanttobewarnedautomaticallywhenmorethan100sessionsareopenedwithyourdatabase.Whatactionwouldyoutaketoachievethis?

A)UsetheDatabaseResourceManager.

B)Setthelimitsintheprofilesusedbyusers.

C)ModifytheSESSIONSinitializationparameter.

D)SetthewarningthresholdfortheCurrentLogonsCountmetric.

答案：D

146.Inyourdatabase,theSTATISTICS_LEVELinitializationparameterissettoBASIC.Whatistheimpactofthissetting?

A)Theoptimizerstatisticsarecollectedautomatically.

B)Onlythetimedoperatingsystem(OS)statisticsandplanexecutionstatisticsarecollected.

C)TheOracleserverdynamicallygeneratesthenecessarystatisticsontablesaspartofqueryoptimization.

D)ThesnapshotsfortheAutomaticWorkloadRepository(AWR)arenotgeneratedautomatically.

E)SnapshotscannotbecollectedmanuallybyusingDBMS_WORKLOAD_REPOSITORYPACKAGE.

答案：D

147.Youhavealargeamountofhistoricaldatainanoperatingsystemfile.Someanalystsinyourorganizationneedtoquerythisdata.Thefileistoolargetoloadintoyourcurrentdatabase.Whichisthemosteffectivemethodtoaccomplishthetask?

A)Upgradethehardware/memorytoaccommodatethedata.

B)LoadthedataintoyourdatabasebyusingthePARALLELclause.

C)GiveanalystsDBAprivilege,sothattheycanqueryDBA_EXTERNAL_TABLES.

D)Useanexternaltablesoyoucanhavethemetadataavailableinyourdatabase,butleavethedataintheoperatingsystemfiles.

答案：D

148.TheHRusercreatesaviewwiththiscommand:

SQL>CREATEVIEWemp_vASSELECT*FROMscott.emp;

NowHRwantstogranttheSELECTprivilegeontheEMP_VviewtotheJIMuser.Whichstatementistrueinthisscenario?

A)HRcangranttheprivilegetoJIMbutwithoutGRANTOPTION.

B)HRcangranttheprivilegetoJIMbecauseHRistheowneroftheview.

C)SCOTThastogranttheSELECTprivilegeontheEMPtabletoJIMbeforethisoperation.

D)HRneedstheSELECTprivilegeontheEMPtablewithGRANTOPTIONfromSCOTTforthisoperation.

答案：D

149.Youexecutethefollowingsetofcommandstocreateadatabaseuserandtograntthesystemprivilegesinyourproductionenvironment.

SQL>CREATEUSERuser01

IDENTIFIEDBYoracle

DEFAULTTABLESPACEtbs1

TEMPORARYTABLESPACEtemp

PROFILEdefault

/

SQL>GRANTcreatesession,createtableTOuser01;

Whileexecutingthecommandtocreateatable,theusergetsthefollowingerrormessageandtheCREATE

TABLE..commandfails.

ERRORatline1:

ORA-01950:noprivilegesontablespace

Whatcouldbethepossiblereasonforthiserrormessage?

A)ThetablespaceTBS1isfull.

B)TheuserisnottheowneroftheSYSTEMtablespace.

C)TheuserdoesnothavequotaontheTBS1tablespace.

D)TheuserdoesnothavesufficientsystemprivilegestocreatetableintheTBS1tablespace.

E)Theuserdoesnothavesufficientprivilegestocreatetableonthedefaultpermanenttablespace.

答案：C

150.WhichtwostatementsaboutFlashbackQueryaretrue?(Choosetwo.)

A)Itisgeneratedbyusingtheredologfiles.

B)Ithelpsinrow-levelrecoveryfromusererrors.

C)ItcanbeperformedtorecoverALTERTABLEstatements

D)Itfailswhenundodatapertainingtothetransactionisoverwritten.

E)ThedatabasehastobeopenedwiththeresetlogsoptionafterperformingFlashbackQuery.

答案：BD

151.Youralertlogfilehasthefollowinginformation:

TueMay2517:43:382004

ORA-00060:Deadlockdetected.Moreinfoinfile

/u01/app/oracle/admin/ORCL/udump/orcl_ora_3173.trc.

Whatwouldyoudotoensurethatdatabaseisstillrunningcorrectly?

A)examinethetracefileandkillthesessionthatcausedthedeadlock

B)examinethetracefileandkillboththesessionsresponsibleforthedeadlock

C)examinethetracefilefordetailsandasktheuserwhocausedthedeadlocktorollbackthetransaction

D)examinethetracefilefordetailsandrememberthatdeadlocksareresolvedautomaticallybyOracledatabase

E)examinethetracefilefordetailsandusetheUndoAdvisortorollbackthetransactionthatcausedthedeadlock

答案：D

152.TheST_INFOmastertablehasmillionsofrowsthatareupdatedveryrarely.IthasaSTATE_CODEcolumnthatcontainsthevaluefor25states.ThetableisfrequentlyqueriedontheSTATE_CODEcolumn.Whichtypeofindexwouldyousuggesttoimprovethequeryperformance?

A)B-tree

B)bitmap

C)reversekey

D)functionbased

答案：B

153.YourdatabaseisinNOARCHIVELOGmodeandalogswitchhappensevery20minutes.OnThursdaymorning,mediafailureresultedincorruptionofadatafilebelongingtotheTOOLStablespace.BackupsaretakenoneverySunday.Whatwouldyoudointhissituation?

A)recoverusingOracleFlashbackDatabasetechnology

B)restorethedatafilesfrombackupandperformacompleterecovery

C)restoreonlythecorrupteddatafileandperformtablespacerecovery

D)restorethedatafilesfrombackupandperformcancel-basedrecovery

E)restoretheentiredatabasefromthemostrecentbackupandstarttheinstanceandopenthedatabase

答案：E

154.WhichtwooperationscanbeflashedbackusingtheFlashbacktechnology?(Choosetwo.)

A)DROPUSERSMITH;

B)DROPTABLEEMPLOYEES;

C)DROPTABLESPACEUSERS;

D)ALTERTABLESALES_REPDROPPARTITIONP1;

E)ALTERTABLEEMPLOYEESDROPCOLUMNDESIG_ID;

答案：AB

155.WhilesettingupanOracledatabaseforoneofyourcriticalapplications,youwanttoensurethatthedatabaseisbackedupatregularintervalswithoutyourintervention.Whatshouldyoudotoachievetheobjective?

A)configurethedatabasetoruninARCHIVELOGmode

B)configuretheFlashRecoveryAreatoenableautomaticdatabasebackup

C)schedulethedatabasebackupusingDBMS_JOBpackageaftercreatingthedatabase

D)schedulethedatabasebackupusingRecoveryManager(RMAN)commandsaftercreatingthedatabase

E)schedulethedatabasebackupusingDatabaseConfigurationAssistant(DBCA)whilecreatingthedatabase

答案：E

156.Youcreatedaresponsefileandwanttocheckitbeforestartinginstallationsinsilentmode.YoustartedinstallationofOraclesoftwareininteractivemodebyprovidingtheresponsefile.Inthemiddleoftheinstallation,yourealizethatthebehavioroftheOracleUniversalInstaller(OUI)isnotconsistentwiththeresponsefile.Whatactionwouldyoutaketodetectthecauseofthisbehavior?

A)Comparethecontentsoftheinstall.logfilewiththeresponsefile.

B)RefertothecontentsoftheoraInst.locfiletoverifythestepsperformedbyOUI.

C)ComparethecontentsoftheinstallActions.logfilewiththatoftheresponsefile.

D)RefertothealertlogfileforinformationregardingtheactionsperformedbyOUIduringinstallation.

答案：C

157.WhichstepdoyouneedtoperformtoenableauserwiththeSYSDBAprivilegetologinasSYSDBAiniSQL*Plus?

A)Theusermustbegrantedthedatabaseadministrator(DBA)privilege.

B)Theusermustbelistedinthepasswordfilefortheauthentication.

C)NospecialsetupisneededfortheusertoconnectasSYSDBAiniSQL*Plus.

D)SetupauserintheOracleApplicationServerContainersforJ2EE(OC4J)usermanager,andgrantthewebDbaroletotheuser.

答案：D

158.Youwanttocreateatablespacewiththefollowingspecifications:

1.Thetablespaceextendsautomatically.

2.Usedandfreeextentsshouldbemanagedbybitmaps.

3.DefaultPCTUSEDattributeissetto60.

4.Alltheextentswouldbeofsize1MB.

Whichthreeoptionswouldyouchoosetocreatethetablespace?(Choosethree.)

A)tablespacewithAUTOEXTENDenabled

B)tablespacewithdictionary-managedextents

C)tablespacewithauniformextentallocationof1MB

D)tablespacewithsegmentspacemanagementasmanual

E)tablespacewithsegmentspacemanagementasautomatic

答案：ACD

159.WhichtwodatabaseoperationscanbeperformedattheMOUNTstageofdatabasestartup?(Choosetwo.)

A)renamingofdatafiles

B)droppingthedatabaseuser

C)renamingofcontrolfiles

D)multiplexingofcontrolfiles

E)configuringthedatabaseinARCHIVELOGmode

答案：AE

160.Youareusingflatfilesasthedatasourceforoneofyourdatawarehousingapplications.YouplantomovethedatafromtheflatfilestructurestoanOracledatabasetooptimizetheapplicationperformance.Inyourdatabaseyouhaveclusteredtables.Whilemigratingthedata,youwanttohaveminimalimpactonthedatabaseperformanceandoptimizethedataloadoperation.WhichmethodwouldyouusetoloaddataintoOracledatabase?

A)usetheexternaltablepopulation

B)usetheOracleDataPumpexportandimportutility

C)usetheconventionalpathdataloadofSQL*Loaderutility

D)usethedirectpathdataloadofOracleexportandimportutility

答案：C

上一篇： Oracle 10g OCP 042 题库 121-166题 共168题 下一篇： ORA-16019 和 ORA-16018 错误的处理方法