OCP认证082考试题库及答案

（新）OCP认证082考试题库及答案

一、单选题

1.ViewtheExhibitsandexaminethestructureofthecostsandPROMOTIONStabIes

YouwanttodispIayPRODIDSwhosepromotioncostisIessthanthehighestcostPROD

IDinapromotiontimeintervaI

13COSI8TMtFROMOTXXJS

NE

Name

NuW?TypePROMOJOI

PROMONAVENO1KUU.|vARCHAH2(30)|

KMfAm阳;Hm阳m1

TIMEJDNOTNULLDAYE

PROMOSUEiCAIfcGORYJDNOTNUU.NUVBtR

PROMOJDNUMBER一

NOTNULLPROMO.CATtGORYKOISULLVARCHAR2(30j

二NOTHULLNUMBER~~阳6V。一匕ATEGORY」。SOTNULLNUMBER

万PR0U6cdir

UMT_COSTNOTNULLNUMBER0O

kUI/BERHoilPROMOBEGINDAUNOTHULLlDATE

UNITPRICENOTNULLPROMODATt2TSULL|DATt

ExaminethisSQLstatement

SELECTprod_id

FROMcosts

WHEREproao_idIN

(SELECTpr8O_id

FROMpromotions

WHEREpromo-cost<ALL

(SELECTHAX(promo_COSt)

FROMpromotions

GROUPBY(oromoenddate-croaobegindate)));

WhatwiIIbetheresult?

A、ItexecutessuccessfuIIyandgivestherequiredresu11.

B、ItgivesanerrorbecausetheGROUPBYcIauseisnotvaIid

C、ItexecutessuccessfuIIybutdoesnotgivetherequiredresu11

D、ItgivesanerrorbecausetheALLkeywordisnotvaIid

答案：C

2.ExaminethedescriptionoftheSATESItabIe

SALES2isatabIewiththesamedescriptionassAIeS1SomesaIesdataiscontainede

rroneousIyinbothtabIes

YoumustdispIayrowsfromsAIEs1andSALESandwishtoseethedupIicatestooWhich

setoperatorgeneratestherequiredoutput?

NameNullType

SALESIDNOTNULLNUMBER

STOREIDNOTNULLHUMBER

ITEMS-IDNUMBER

QUANTITYNUMBER

SALESDATE

A、MINUS

B、UNIONALL

C、SUBTRACT

D、UNION

E、INTERSECT

答案：B

3.YourdatabaseinstanceisstartedwithaPFILE.

Examinetheseparameters:

NAMETYPEVALUE

memory_max_targetbiginteger0

memory_targetbiginteger0

sga_max_sizebiginteger2G

sga_targetbiginteger2G

Youwanttoincreasethesizeofthebuffercache.FreememoryisavaiIabIetoincre

asethesizeofthebuffercache.Youexecutethemand:SQL>ALTERSYSTEMSETDB_CAC

HE_SIZE=1024M;

Whatistheoute?

AvThevaIueischangedonIyinthePFILEandtakeseffectatthenextinstancestar

tup

B、ThevaIueischangedforthecurrentinstanceandinthePFILE

C、ItfaiIsbecausetheSCOPEcIauseismissing

D、ChangeisappIiedtothecurrentinstance,butdoesnotpersistafterinstance

restart

答案：D

4.WhichstatementistrueaboutdatabaseIinks?

A、AdatabaseIinkcreatedinadatabaseaIIowsaconnectionfromthatdatabase's

instancetothe

Targetdatabase'sinstance,butnotviceversa

B、PrivatedatabaseIinkcreationrequiresthesameusertoexistinboththeIoca

Iandtheremotedatabases

CxApubIicdatabaseIinkcanbeusedbyauserconnectedtotheIocaIdatabaseinst

ancetoconnecttoanyschemaintheremotedatabaseinstance

DxApubIicdatabaseIinkcanbecreatedonIybysys

E、AdatabaseIinkcanbecreatedonIybetweentwoOracIedatabases

答案：c

5.WhichstatementistrueabouttheINTERSECToperatorusedinpoundqueries?

AxMultiple!NTERSECToperatorsarenotpossibIeinthesameSQLstatement

B、ItprocessesNULLSintheseIectedcoIumns

C、INTERSECTisoflowerprecedencethanUNIONorUNIONALL

D、ItignoresNULLS

答案：B

6.YourdatabaseinstanceisstartedwithanSPFILEAPFILEisalsoavaiIable

Youexecutethismand

ALTERSYSTEMSETDBCACHESIZE=100K

WhereisthevaIuechanged?

A、intheSPFILEandPFTIE

BxintheSPFILE,PFILE,andmemory

C、onlyintheSPFILE

D、intheSPFILEandinmemory

Exonlyinmemory

答案：D

7.ExaminethedescriptionofthePRODUCTINEORMATTONtabIe

Naa«Null?Typ«

PROD_IDNOTNULLNUMBER(2)

PROD二NAMtVARCHAK2(10)

LIST二PRX3NUMBER2)

WhichqueryretrievesthenumberofproductswithanuIllistprice?

A、SELECTCOUNT(Iistprice)FROMproductinformationWHEREIistpriceNULL

B、SELECTCOUNT(Iistprice)FROMproductinformationWHEREIistpriceIsNULL,

C、SELECTCOUNT(DISTINCTIistprice)PROMproductinformationWHEREIistprice

IsNULL:

D、SELECTCOUNT(NVL(Iistprice,0))FROMproductinformationWHEREIistpriceI

sNULL

答案：D

8.ExaminethedescriptionoftheEMPLOYEEStabIeNISDATEFORMATissettoDD-MON-

YY

WhichqueryrequiresexpIicitdatatypeconversion?

A、SELECTjoindateFROMempIoyeesWHEREjoindate>'10-02-2018';

B、SELECTjoindate||f'|IsalaryFROMemployees

GSELECTsaIary+'120.50'FROMempIoyees

D、SELECTSUBSTR(join_date,1,2)-1OFROMempIoyees

ExSELECTjoindate+'20'FROMempIoyees

答案：A

9.Whatistrueaboutnon-equiJoinstatementperformance?

A、Thejoinsyntaxusedmakesnodifferencetoperformance.

B、TheBETWEENconditionusedwithannon-equijoinsometimesperformsbetterth

anusingthe>=and<=conditions

CxTheBETWEENconditionusedwithannon-equijoinaIwaysperformsbetterthanw

henusingthe>=andUconditions

D、TheOracIejoinsyntaxperformsbetterthanthesoI:1999pIiantANSIjoinsynt

ax

ExTheOracIejoinsyntaxperformsIessweIIthanthesoI:1999pIiantANSIjoinsy

ntax

答案：A

10.Adatabaseisconfiguredtouseautomaticundomanagementwithtemporaryundo

enabIedAnUPDATEisexecutedonatemporarytabIe

WhereistheUNDOstored?

AxintheundotabIespace

B、intheSYSAUXtabIespace

CxintheSGA

D、inthePGA

E、inthetemporarytabIespace

答案：E

11.ExaminethedescriptionoftheCUSTOMERStabIe:

NameNull?Type

CUST-IDNOTNULLNUMBER

CUST_FIRST_NAMENOTNULLVARCHAR2(20)

CV3T_IAST_NAMENOTNULLVARCKAR2(30)

CU3T_INC<XE_LEVELVARCHAR2(30)

CUSTCREDITLIMITNUMBER

ForcustomerswhoseineIeveIhasavaIue,youwanttodispIaythefirstnameanddue

amountas5%

OftheircreditIimit.CustomerswhosedueamountisnuIIshouIdnotbedispIayed.

WhichqueryshouIdbeused?

A、SELECTcust_first_name,cust_credit_limit*.05ASDUE_AM0UNT

FROMcustomersWHEREcust_ins_levelISNOTNULLANDdue_amountISNOTNULL;

B、SELECTcust_first_name,cust_credit_limit*.05ASDUE_AM0UNT

FROMcustomersWHEREcust_ine_leveI!=NULLANDcust_credit_leveI!=NULL;

CxSELECTcust_first_name,cust_credit_limit*.05ASDUE_AM0UNT

FROMcustomersWHEREcust_ine_leveI<>NULLANDdue_amountONULL;

D、SELECTcust_first_name,cust_credit_limit*.05ASDUE_AM0UNT

FROMcustomersWHEREcust_ine_leveI!=NULLANDdue_amount!=NULL;

ExSELECTcust_first_name,cust_credit_limit*.05ASDUE_AM0UNTFR0Mcustome

rsWHEREcust_ine_IeveIISNOTNULLANDcust_credit_IimitISNOTNULL;

答案：E

12.YouwanttowriteaquerythatpromptsfortwocoIumnnamesandtheWHEREconditi

oneachtimeitIs

ExecutedinasessionbutonIypromptsforthetabIenamethefirsttimeitisexecut

edThevariabIesusedinyourqueryareneverundefinedinyoursession

Whichquerycanbeused?

A、SELECT&&co11&&co2FR0M&tabIeWHERE&&condition=&&cond

B、SELECT&coI1&C012FR0M&&tabIeWHERE&condition

C、SELECT&coI1&C012FR0M&tableWHERE&condition

D、ELECT&&co11&&coFR0M&tabIeWHERE&&condition=&cond

E、SELECT&&co11&&C0L2FR0M&tableWHERE&&condition

答案：B

13.WhichpressionmethodisremendedforDirect-PathInsertoperations?

A、COLUMNSTOREPRESSADVANCED

B、ROWSTOREPRESSADVANCED

C、COLUMNSTOREPRESSBASIC

D、ROWSTOREPRESSBASIC

答案：D

14.YouwanttousetabIepressionsuitabIeforOLTPthatwiII:1-pressrowsforaII

DMLstatementsonthattabIe

2-MinimizetheoverheadsassociatedwithpressionWhichpressionoptionisbest

suitedforthis?

A、COLUMNSTOREPRESSFORQUERYLOW

B、ROWSTOREPRESSBASIC

C、COLUMNSTOREPRESSFORARCHIVELOW

D、COLUMNSTOREPRESSFORARCHIVEHIGH

E、ROWSTOREPRESSADVANCED

答案：E

15.ExaminethedescriptionoftheCUSTOMERStabIe

NameIWType

CUSTJDNOTNULLVARCHAR2(6)

nRST_NAMEVARCHAR2(50)

LAST-NAMENOTNULLVARCHAR2(50)

ADDRESSVARCHAR2(50)

CITYVARCHAR2(25)

YouwanttodispIaydetaiIsofaIIcustomerswhoresideincitiesstartingwiththe

letterDfollowedbyat

LeasttwocharactersWhichquerycanbeused?

A、SELECT"FROMcustomersWHEREcityLIKED

B、SELECT*FROMcustomersWHERE

C、SELECT"FROMcustomersWHEREcityLIKED

D、SELECT"FROMcustomersWHEREcity=D

答案：A

16.WhichisthedefauItooIumnorcoIumnsforsortingoutputfrompoundqueriesus

ingSEToperatorssuchasINTERSECTinaSQLstatement?

AxthefirstNUMBERorVARCHAR2coIumnintheIastseIectofthepoundquery

BxthefirstNUMBERcoIumninthefirstseIectofthepoundquery

C、thefirstvARCHAR2coIumninthefirstseIectofthepoundquery

D、thefirstcolumninthefirstseIectofthepoundquery

E、thefirstcoIumnintheIastseIectsofthepoundquery

答案：D

17.YouhavebeentaskedtocreateatabIeforabankingappIicationOneofthecoIum

nsmustmeetthreerequirements:

Bestoredinaformatsupportingdatearithmeticwithoutusingconversionfuncti

onsStoreaIoanperiodofuptolOyears

BeusedforcaIculatinginterestforthenumberofdaystheIoanremainsunpaidWhi

chdatatypeshouIdyouuse?

A、INTERVALYEARTOMONTH

B、INTERVALDAYTOSECOND

GTIMESTAMPWITHLOCALTIMEZONE

D、TIMESTAMP

E、TIMESTAMPWITHTIMEZONE

答案：B

18.InthespfiIeofasingleinstancedatabase,LOCALLISTENERissettoLISTENER1.

TheTNSNAMESORAfileinSORACLEHOME/network/admininthedatabasehomecontain

s:LISTENER仁

(ADDRESS二(PROTOCOL=TCP)

(H0ST=host1abc.)(P0RT=1521)

)

Whichstatementistrue?

AxTherearetwoIistenersnamedLISTENERandLISTENER1runningsimuItaneousIy

usingportl521onthesamehostasthedatabaseinstances

B、ThedefinitionforLISTENER1requiresaCONNECTDATAsectiontoenabIedynami

cserviceregistration

GLISTENER1mustaIsobedefinedintheLISTENER.ORAfiIetoenabIedynamicserv

iceregistration

DxTheLREGprocessregistersservicesdynamicallywiththeLISTENER_1Iistene

r

ExDynamicserviceregistrationcannotbeusedforthisdatabaseinstance

答案：A

19.InthespfiIeofasingleinstancedatabase,LOCALLISTENERissettoLISTENER.

1

TheTNSNAMES.ORAfiIeinSORACLEHOME/network/admininthedatabasehomecontai

ns

LISTENER」=

(ADDRESS>

(PROTOCOL«TCP)

(HOST=hoMlabccom)

(PORT»1521)

)

Whichstatementistrue?

AxDynamicserviceregistrationcannotbeusedforthisdatabaseinstance

B、TheLREGprocessregistersservicesdynamicallywiththeLISTENERIIistener

C、LISTENER_1mustaIsobedefinedintheLISTENER.ORAfiIetoenabIedynamicser

viceregistration

D、TherearetwoIistenersnamedLISTENERandLISTENER1runningsimuItaneousIy

usingport1521onthesamehostasthedatabaseinstances

ThedefinitionforLISTENER1requiresaCONNECTDATAsectiontoenabIedynamicse

rvice

答案：D

20.TheCUSTOMERStablehasaCUST_LASTNAMEcoIumnofdatatypeVARCHAR2ThetabIe

hastworowswhoseCUST_LASTNAMEvaIuesareAndersonandAusson

WhichqueryproducesoutputforCUST_LASTNAMEcontainingoderforthefirstrowa

ndAusforthe

Second?

A、SELECTREPLACE(SUBSTR(cust_Iast_name,-3),'AN',"0')FROMcustomers;

B、SELECTINITCAPREPLACE(TRIM('S0NFR0Mcust_Iast_namE.,1AN,'0'))FROM

Customers;

C、SELECTREPLACE(TRIM(TRAILINGHS0N'FR0McustIastnamE.,*AN,'O')FROM

Customers

DxSELECTREPLACE(REPLACE(oustlastname,'son','),'AN','O')FROMcustomers;

答案：D

21.ExaminethedescriptionoftheproductstabIe

NameNull?

PRODNAMEVARCHAR2(40)

COSTNUMBER(H•Z)

A、SELECTprodid,AVG(MAX(cost))FROMproductsGROUPBYprod.

BvSELECTprodid,MAX(AVG(cost))FROMproductsGROUPBYprodid:

GSeIectprodid,reIeasedate,SUM(cost)FROMproductsGROUPBYprodid

D、SELECTprodid,reIeasedate,SUM(cost)FROMproductsGROUPBYprodid,reIeas

edate

答案：D

多选题

1.WhichthreestatementsaretrueaboutsequencesinasingleinstanceOracIedat

abase?

AvAsequencenumberthatwasaIIocatedcanberoIIedbackifatransactionfaiIs

BvAsequencecanonIybedroppedbyaDBA

C、SequencescanaIwayshavegaps

DvAsequencecanissuedupIicatevaIues.

E、Asequence'sunaIIocatedcachedvaIuesareIostiftheinstanceshutsdown

F、TwoormoretabIescannothavekeysgeneratedfromthesamesequence

答案：CDE

2.WhichthreestatementsaretrueaboutusingSQL*PIus?

A、ItcanrunscriptspassedtoitbyasheIIscript.

B、ItcanrunscriptsenteredattheSQLprompt

C、Ithasbothmand-lineandgraphicaIuserinterfaces(Gun)

D、IthasitsownmandsthatareseparatefromanySQLstatements

E、ItcanrunRecoveryManager(RMAN)mands

F、ItmustbedownIoadedfromtheOracIeTechnoIogyNetwork(OTN)

答案：BCD

3.Examinethesemands

[oracleghostOlsqlplusul/oracle

SQL>SELECT*FROMemp;

ENOENAMEDN

1Alan2

23en2

SQL>exit

[oracleghostOlcatemp.dat

.t2

3,Curl,4

4,Bob,4

[oracleShostOl*•]Ssqlldrul/oracle

WhichtwostatementsaretrueaboutthesqIIdrexecution?

A、ItoverwritesthedataforAIanandaddsdataforCurIandBob

B、ItgeneratesasqIscriptthatitusestoIoaddatafromEMP.DATtoEMP

C\ItoverwritesaIIdatainEMPwithdatafromEMP.DAT.

D、ItgeneratesaIogthatcontainscontroIfiIeentries,whichcanbeusedwithno

rmaISQL*Loaderoperations

ExItappendsdatafromEMP.DATtoEMP.

答案：DE

4.Whichtwostatementsaretrueaboutundosegmentsandtheuseofundobytransact

ionsinanOracle

Databaseinstance

AxAsingIetransactionmayusemuItipleundosegmentssimuItaneousIy.

BxUndosegmentscanbestoredintheSYSAUXtabIespace

C、UndosegmentscanextendwhenatransactionfiIIstheIastextentoftheundose

gment

DxUndosegmentscanwraparoundtothefirstextentwhenatransactionfiIIstheI

astextendoftheundosegment

E、UndosegmentscanbestoredintheSYSTEMtabIespace

答案：DE

5.WhichtwostatementsaretrueaboutsubstitutionvariabIes?

A、AsubstitutionvariabIeusedtopromptforacoIumnnamemustbeencIosedindou

bIequotationmarks.

B、AsubstitutionvariabIeprefixedwithsaIwayspromptsonIyonceforavaIuein

asession

C、AsubstitutionvariabIeusedtopromptforacoIumnnamemustbeencIosedinsin

gIequotationmarks

D、AsubstitutionvariabIeprefixedwith&&promptsonIyonceforavaIueinasess

ionunIessitissettoundefinedinthesession

E、AsubstitutionvariabIecanbeusedonIyinaSELECRstatement

F、AsubstitutionvariabIecanbeusedwithanycIauseinaseIectstatement

答案：DF

6.TheORDERStabIehasacoIumnORDERDATEofdatatypeDATE.ThedefauItdispIayfo

rmatforadateisDD-MON-RR

WhichtwoWHEREconditionsdemonstratethecorrectusageofconversionfunction

s?

A、WHEREorderdateTODATE(ADDMONTHS(SYSDATE,6),'MONDDYYYY')

B、WHERETOCHAR(orderdate,'MONDDYYYY')="JAN202019,

C、WHEREorderdate>TODATE(JUL102018','NONDDYYYY')

D、WHEREorderdateTOCHAR(ADDMONTHS(SYSDATE,6),'MONDDYYYY)

E、WHEREorderdateIN(ToDATE('oct212018','monDDYYYY'),roCHAR('Nov212018

'MonDDYYYY'))

答案：BC

7.Inoneofyourdatabases,youcreateauser,HR,andthenexecutethismandGRANTC

REATESESSIONTOhrWITHADMINOPTION:

WhichthreeactionscanHRperform?

AxGranttheCREATESESSIONpriviIegewithADMTNOPTIONtootherusers

B、Logintothedatabaseinstance.

CxRevoketheCREATEsessionpriviIegefromotherusers

D、ExecuteDDLstatementsintheHRschema

E、ExecuteDMLstatementsintheHRschema

F、RevoketheCREATESESSIONprivilegefromuserHR

答案：ACF

8.WhichthreestatementsaretrueaboutsingIe-rowfunctions?(Choosethree.)

AxTheycanbeusedonIyintheWHEREcIauseofaSELECTstatement

B、TheargumentcanbeacoIumnname,variabIe,IiteraIoranexpression

CxThedatatypereturnedcanbedifferentfromthedatatypeoftheargument

D、TheycanbenestedtoanyIeveI

E、TheycanacceptonIyoneargument

TheyreturnasingleresuItrowpertabIe

答案：BCD

9.WhichthreestatementsaretrueabouttheAutomaticDiagnosticRepository(AD

R)?

Ax11isafiIe-basedrepositoryheIdoutsideanydatabase

B、TheADRbaseisspecifiedintheDIAGNOSTICDESTdatabaseparamete

C、11isonIyusedforOracIeDatabasediagnosticinformation

D、11isheIdinsideanOracIedatabaseschema

E、ItcanbeusedforprobIemdiagnosisofadatabasewhenthatdatabase'sinstanc

eisdow

答案：ABE

10.WhichthreestatementsaretrueaboutaseIfjoin?

AxItcanbeanouterjoin

BxTheONcIausecanbeused

CxThequerymustusetwodifferentaIiasesforthetabIe

DxItmustbeanequijoin

E、Itmustbeaninnerjoin

F、TheONcIausemustbeused

答案：ABC

11.1nthePROMOTIONstabIe,thePROMOBEGINDATEcoIumnisofdatatypeDATEandthe

defauItdateformatisDD-MON-RR

WhichtwostatementsaretrueaboutexpressionsusingPROMOBEGINDATEcontained

inaquery?

A、PROMOBEGINDATE-SYSDATEwiIIreturnanumber

B、T0DATE(PR0M0BEGINDATE*5)wiIIreturnadate

C、TONUMBER(PROMOBEGINDATE.-5wiIIreturnanumber

D、PROMOBEGINDATE-SYSDATEwiIIreturnanerror

E、PROMOBEGINDATE-5wiIIreturnadate

答案：AE

12.WhichthreestatementsaretrueaboutGLOBALTEMPORARYTABLES?

A、AGLOBALTEMPORARYTABLEcanhaveonIyoneindex

B、AtriggercanbecreatedonaGLOBALTEMPORRYTABLE

C、DataManipuIationLanguage(DML)onGLOBALTEMPORARYTABLESgeneratesnoRED

0

D、AGLOBALTEMPORARYTABLEcannothaveaPUBLICSYNONYM

ExAGLOBALTEMPORARYTABLEcanbereferencedinthedefiningqueryofaview.

F、AGLOBALTEMPORARYTABLEcanhavemu11ipIeindexes.

答案：BEF

13.YoumustcreateatabIespaceofnon-standardbIocksizeinanewfiIesystemand

pIantousethismand

CREATETABLESPACEns_tb3

DATAFILE'/u02/oraclc/data/n3tbs_f01.dbf*

SIZE100G

BLOCKSIZE32K;

ThestandardbIocksizeis8kbutothernon-standardbIocksizeswiIIaIsobeusedW

hichtwoarerequirementsforthismandtosucceed?

AxDB32KCACHESIZEmustbesettoavaIuethatcanbeacmodatedintheSGA

B、DB32KCACHESIZEshouIdbesettoavaIuegreaterthanDBCACHESIZE

C、DB32KCACHESIZEmustbeIessthanDBCACHESIZE

D、Theoperatingsystemmustusea32kbIocksize

ExDBCACHESIZEmustbesettoasizethatissmaIIerthanDB32KCACHESIZE

F、The/uo2fiIesystemmusthaveatIeastlOOgspaceforthedatafiIe

答案：AF

14.WhichtwoarebenefitsofexternaItabIes?

AxTheysupportUPDATESwhichtransparentIyupdatesrecordsinthefiIesystema

siftheyweretable

B、TheycanbequeriedwhiIethedatabaseisintheMOUNTstateIikedynamicperfor

manceviews

C、TheysupportDELETEswhichtransparentIydeIetesrecordsinthefiIesystema

siftheyweretabIerows

D、TheresuItsofapIexjoinoraggregatingfunctionorbothcanbeunIoadedtoafi

Iefortransportationtoanotherdatabase

E、Theycanbequeried,transformed,andjoinedwithothertabIeswithouthaving

toIoadthedatafirst

答案：DE

15.WhichtwostatementsaretrueconcerninglogicalandphysicaIdatabasestruc

tures?

A、Asegment'sbIockscanbeofdifferentsizes.

B、AsegmentmighthaveonIyoneextent

CxAlItablespacesmayhaveoneormoredatafiles

D、Segmentscanspanmu11ipIetabIespaces

E、AsegmentcanspanmuItipledatafilesinsometabIespaces

答案：BE

16.WhichthreestatementsaretrueregardingsingIerowsubqueries?

A、TheymustbepIacedontheIeftsideoftheparisonoperatororcondition

B、TheymustreturnarowtopreventerrorsintheSQLstatement

C、TheycanbeusedintheHAVINGclause.

D、ASQLstatementmayhavemuItiplesinglerowsubquerybIocks

E、TheycanbeusedintheWHEREcIause

F、TheymustbepIacedontherightsideoftheparisonoperatororcondition

答案：CDE

17.YoucurrentIyhaveanactivetransactioninyoursessionandhavebeengranted

seIectaccesstoVSTRANSACTION

InwhichthreesituationswiIIre-executingthisquerystiIIreturnarowbutwith

adifferentXIDindicatinganewtransactionhasstarted?

Executing:

SELECTxid,statusFROMv$transaction;

inyoursessionreturns:

XIDSTATUS

0A0007000A07000DACTIVE

A、aftersuccessfuIIyexecutingaTRUNCATEstatementfoIIowedbyaDMLstatemen

t

B、aftersuccessfuIIyexecutingaCREATETABLEASSeIectstatementfoIIowedbya

SELECTFORUPDATEstatement

C、aftersuccessfulIyexecutingaCREATETABLEstatementfoIIowedbyaCREATEIN

DEXstatement

DxaftersuccessfuIIyexecutingamitorROLLBACKfoIIowedbyaDMLstatement

E、aftersuccessfuIIyexecutingaDMLstatementfoIlowingafaiIedDMLstatemen

t

F、aftersuccessfuIIyexecutingamitorROLLBACKfoIIowedbyaSeIectstatement

答案:ABD

18.orcI.dmpcontainsafuIIexportoftheORCIdatabase

(oraclefthostOl*]impdpsystem/oracle\

>SCHEMAS=sh,o«\

>REMAP_SCHEMA«Bh：hx\

>DUMPrTLE-orcl.dmp\

>EXCLUDE>index\

>TABLE_EXISTS_ACTIONax*pl*C«\

>LOGFILETmpdp.log

ThismandIsexecutedtoIoaddatafromorcI.dmpintotheRESRDBdatabase

Whichtwostatementsaretrue?

A、ItskipsonIytabIesthatexistInbothORCL:SHandTESTDB:HR

B、Itdropsandre-createstabIesthatexistinbothORCL:SHandTESTDB:HR.

C、ItskipsalIindexesofORCL:SHandORCI:OE

D、Itdropsandre-createsindexesthatexistInbothORCI:SHandTESTDB:HR

E、ItskipsonIyindexesthatexistinbothORCL:SHandTESTDB:HR

答案：BC

19.ExaminetheseSOLstatementswhichexecutesuccessfuIIy

CREATETABLEQmp

(ompnoNUMBER(2>CONSTRAINTPRIMARYKEY.

on^imoVARCHAR2(15>r

salaryNVMBER(8,2),

ALTERTABLEcmpADDCOHSTRADn1enip_niar_fk

FOREIGNKEYjno)

REFERENCESemp(cxp-no)

ONDELETESETML：

ALTERTABLEQ»p

DISABLECOKSTBAIJrrep_emp_no_pk

CASCADE；

ALTERTABLEgp

EKABLEC0：JS7RA：NTfcxp_Gxp__no__Fk；

Whichtwostatementsaretrueafterexecution?

AvTheprimarykeyconstraintwiIIbeenabIedandIMMEDIATE

BvTheforeignkeyconstraintwiIIbeenabledandDEFERRED

C、TheforeignkeyconstraintwiIIbedisabIed

D\TheforeignkeyconstraintwiIIbeenabIedandIMMEDIATE

ExTheprimarykeyconstraintwiIIbeenabledandDEFERRED

答案：AC

20.WhichthreestatementsaretrueaboutDeferredSegmentCreationinOracIedat

abases?

A、11isthedefauItbehaviorfortabIesandindexes.

B、Itissupportedforsys-ownedtabIescontainedinIocaIIymanagedtabIespace

s

C、SessionsmaydynamicaIIyswitchbackandforthfromDEFERREDtoIMMEDIATEseg

mentcreation.

D、IndexesinherittheDEFERREDorIMMEDIATEsegmentcreationattributefromth

eirparenttable

Ex11issupportedforIndexOrganizedTabIes(lOTs)containedinIocaIIymanage

dtabIespaces.

答案：ACD

21.Whichtwostatementsaretrueaboutviews?

AxAviewmustonIyrefertotablesinitsdefiningquery.

B、TheWITHCHECKcIausepreventseertainrowsfrombeingdispIayedwhenqueryin

gtheview

C、Viewscanbeupdatedwithouttheneedtore-grantpriviIegesontheview

D、TheWITHCHECKcIausepreventseertainrowsfrombeingupdatedorinsertedint

heunderlying

TabIethroughtheview.

ExViewscanbeindexed

答案：CD

22.WhichthreeactivitiesarerecordedinthedatabaseaIertIog?

A、bIockcorruptionerrors

B、DataDefinitionLanguage(DDL)statements

C、Non-defauItdatabaseparameters

D、deadIockerrors

E、sessionIoginsandIogouts

答案：ACD

23.WhichtwoaretrueaboutaSQLstatementusingSEToperatorssuchaslINION?

A、ThedatatypeofeachcoIumnreturnedbythesecondquerymustexactIymatchthe

datatypeofthecorrespondingcoIumnreturnedbythefirstquery

B、Thenumber,butnotnames,ofcoIumnsmustbeidenticaIforaIISELECTstatemen

tsinthequery

C、ThenamesandnumberofcoIumnsmustbeidenticaIforaIISELECTstatementsint

hequery

D、ThedatatypeofeachcoIumnreturnedbythesecondqueryisautomaticaIIyconv

ertedtothedatatypeofthecorrespondingcoIumnreturnedbythefirstquery

ExThedatatypegroupofeachcoIumnreturnedbythesecondquerymustmatchtheda

tatypegroupofthecorrespondingcoIumnreturnedbythefirstquery

答案：BE

24.WhichtwostatementsaretrueregardingaSAVEPOINT?(Choosetwo.

A、RollingbacktoaSAVEPOINTcanundoaCREATEINDEXstatement

B、RoIIingbacktoaSAVEPOINTcanundoaTRUNCATEstatement

CxOnIyoneSAVEPOINTmaybeissuedinatransaction

DxASAVEPOINTdoesnotissueaMIT

E、RoIIingbacktoaSAVEPOINTcanundoaDELETEstatement

答案：DE

25.hichthreefunctionsareperformedbydispatchersinasharedserverconfigur

ation?(Choosethree

A、writinginboundrequesttothemonrequestqueuefromaIIsharedserverconnec

tions

B、checkingforoutboundsharedserverresponsesonthemonoutboundresponsequ

eue

C、receivinginboundrequestsfromprocessesusingsharedserverconnections

D、sendingeachconnectioninputrequesttotheappropriatesharedserverinput

queue

ExbroadcastingsharedserversessionresponsesbacktorequestersonaIIconne

ctions

F、sendingsharedserversessionresponsesbacktorequestersontheappropriat

econnection

答案:ACF

26.ExaminethedescriptionoftheBOORSTRANSACTIONStabIe:

WhichtwoWHEREconditionsgivethesameresu11?

NameNull?Type

TRANSACTIOH-IDHOTNULLVARCHAR2(6)

TRANSACT工ON_TYPEVARCHAR2(3)

BORROWED-DATEDATE

BOOK_IDVARCHAR2⑹

MEMBERIDVARCHAR2(6)

ExaminethispartialSQLstatement:

SELECT•FROMbooks_tcansactions

A、WHEREborroweddate=SYSDATEAND(transaction_type='RM'AND(member_id'A1

01'ORmemberid=1A1021))

B、WHEREborroweddate=SYSDATEAND(transactiontype='RM'ORmemberidINCA10

IJA102D)

C、WHEREborroweddate=SYSDATEAND(transactiontype='RM'ANDmember_id'A101

'ORmemberid='A102')

D、WHEREborroweddate=SYSDATEANDtransactiontype='RM'ORmemberidINCA101

,,'A102f)

E、WHERE(borroweddate=SYSDATEANDtransactiontype='RM')ORmemberidINA101

,,'A1021)

答案：DE

27.WhichthreetasksareperformedbybackgroundprocessesinanOracIedatabase

instance?

AxregisteringserviceswithOracIeNetIisteners

B、writingdirtydatabasebIockimagesfromthebuffercache

CxwritingredotoIogfiIes

D、readingdatabasebIocksintothebuffercache

Excreatingdedicatedserverconnections

答案：ABC

28.Whichfourstatementsaretrueregardingprimaryandforeignkeyconstraints

andtheeffecttheycanhaveontabIedata?

Ax11ispossibIeforchiIdrowsthathaveaforeignkeytoremaininthechiIdtabIe

atthetimetheparentrowisdeIeted

B、PrimarykeyandforeignkeyconstraintscanbedefinedatboththecoIumnandta

bIeIeveI

C、AtabIecanhaveonIyoneprimarykeyandoneforeignkey.

D、AtabIecanhaveonIyoneprimarykeybutmuItipleforeignkeys

ExOnIytheprimarykeycanbedefinedatthecoIumnandtabIeIeveI

F、TheforeignkeycoIumnsandparenttabIeprimarykeycoIumnsmusthavethesame

names.

G、11ispossibIeforchiIdrowsthathaveaforeignkeytobedeIetedautomaticaII

yfromthechiIdtabIeatthetimetheparentrowisdeIeted

答案：ABDG

29.WhichtwostatementsaretrueabouttheOracIejoinandANSIjoinsyntax?

A、TheOracIejoinsyntaxperformsbetterthantheSQL:1999pIiantANSIjoinsynt

ax.

B、TheOra