WhichtwoWHEREconditionsgivethesameresultA)WHEREborrowed_date=SYSDATEAND(transaction_type='RM'AND(member_id='A101'ORmember_id="A102'));B)WHEREborrowed_date=SYSDATEANDtransaction_type='RM'ORmember_idIN('A101','A102');C)WHEREborrowed_date=SYSDATEAND(transaction_type='RM'ORmember_idIN('A101'，'A102'));D)WHEREborrowed_date=SYSDATEAND(transaction_type='RM'ANDmember_id='A101'ORmember_id='A102');E)WHERE(borrowed_date=SYSDATEANDtransactiontype='RM')ORmember_idIN('A101'，'A102');Answer:BEQ5.WhichtwostatementsaretrueabouttheOraclejoinandANSIjoinsyntaxA)TheOraclejoinsyntaxlackstheabilitytodoouterjoins.B)TheOraclejoinsyntaxperformslesswellthantheSQL:1999compliantANSIjoinsyntax.C)TheSQL:1999compliantANSIjoinsyntaxsupportscreationofaCartesianproductoftwotables.D)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntax.E)TheOraclejoinsyntaxsupportscreationofaCartesianproductoftwotables.Answer:CEQ6.
WhichtwostatementsaretrueA)PRODUCT_PRICEcanbeusedinanarithmeticexpressionevenifithasnovaluestoredinit.B)EXPIRY_DATEcontainstheSYSDATEbydefaultifnodateisassignedtoit.C)PRODUCT_IDcanbeassignedthePRIMARYKEYconstraint.D)PRODUCT_PRICEcontainsthevaluezerobydefaultifnovalueisassignedtoit.E)EXPIRY_DATEcannotbeusedinarithmeticexpressions.F)PRODUCT_NAMEcannotcontainduplicatevalues.Answer:AC
Q7.
WhichthreestatementsaretrueA)Thereisnorowcontainingfountainpen.B)Thereisnorowcontainingpenci1.C)Thereisnorowcontainingpen.D)Thecodeforpenis1.E)Thecodeforpenis10.F)Thecodeforfountainpenis3.Answer:BEFQ8.WhichtwostatementsaretrueaboutOraclesynonymsA)AnyusercancreateaPUBLICsynonym.B)Asynonymhasanobjectnumber.C)Asynonymcanhaveasynonym.D)AlIprivatesynonymnamesmustbeuniqueinthedatabase.E)Asynonymcanbecreatedonanobjectinapackage.Answer:BCQ9.Whatistrueaboutnon-equijoinstatementperformanceA)TheOraclejoinsyntaxperformslesswellthantheSQI:1999compliantANSIjoinsyntax.B)TheBETWEENconditionusedwithannon-equijoinalwaysperformsbetterthanwhenusingthe>=and<=conditions.C)TheBETWEENconditionusedwithannon-equijoinsometimesperformsbetterthanusingthe>=and<=conditions.D)Thejoinsyntaxusedmakesnodifferencetoperformance.E)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntax.Answer:DQ10.TheSALEStablehascolumnsPRODIDandQUANTITY_SOLDofdatatypeNUMBER.
WhichtwoqueriesexecutesuccessfullyA)SELECTCOUNT(prod_id)FROMsalesGROUPBYprodidWHEREquantity_sold>55000;B)SELECTCOUNT(prod_id)FROMsalesWHEREquantitysold>55000GROUPBYprod_id;C)SELECTprod_idFROMSalesWHEREquantitysold>55000ANDCOUNT(*)>10GROUPBYCOUNT(*)>10;D)SELECTprodidFROMsalesWHEREquantity_sold>SS000ANDCOUNT(*)>10GROUPBYProd_idHAVINGCOUNT(*)>10;E)SELECTprod_idFROMsalesWHEREquantity_sold>55000GROURBYprod_idHAVINGCOUNT(*)>10;Answer:BEQ11.
E、CEIL：可用于正数和负数FLOOR是向下取整，正负整数都支持floor(9.999999)=9.0CEIL是先上取整正负整数都支持ceil(9.999999)=10WhichthreeQ20.
WhichthreestatementsaretrueA)Acustomercanexistinmanycountries.B)ThestatementwillfailbecauseasubquerymaynotbecontainedinaVALUESclause.C)ThestatementwillexecutesuccessfullyandanewrowwillbeinsertedintotheSALEStable.D)ThestatementwillfailifarowalreadyexistsintheSALEStableforproduct23.E)TheSALEStablehasfiveforeignkeys.F)Aproductcanhaveadifferentunitpriceatdifferenttimes.Answer:CEFQ26.
Whichqueryisvalid
E)ThenumberofcolumnsselectedbythefirstSELECTstatementcanbegreaterthanthenumberselectedinsubsequentSELECTstatements.F)DuplicatescanoptionallybeeliminatedbytheUNIONoperator.G)ThenamesofcolumnsselectedineachSELECTStatementcanbeidentical.Answer:CDG变种题和33题相似：WhichtwostatementsaretrueregardingtheUNIONandUNIONALLoperatorsA)NULLSarenotignoredduringduplicatecheckingB)TheoutputissortedbytheUNIONALLoperator.C)ThenumberofcolumnsselectedineachSELECTstatementmustbeidenticalD)DuplicatesareeliminatedautomatiallybytheUNIONALLoperator.E)E)ThenamesofcolumnsselectedineachSELECTStatementcanbeidentical.Answer:AC解析：有关null的处理方式在本次题库中多次出现，比如在:intersect和union的操作经过使用证明，null值的行是不会被汇率的，会被计算在内。
Q34.ExaminethedescriptionofthePRODUCTSTATUStable:
TheSTATUScolumncontainsthevalues'INSTOCK'or'OUTOFSTOCK'foreachrow.WhichtwoquerieswillexecutesuccessfullyA)SELECTprod_id||q'('snotavailable)'FROMproduct_statusWHEREstatus='OUTOFSTOCK1';B)SELECTprod_id||q'(s'notavailable)'"CURRENTAVAILABILITY"FROMproduct_statusWHEREstatus='OUTOFSTOCK';C)SELECTprod_id||q'(s'notavailable)''CURRENTAVAILABILITTFROMproduct_statusWHEREstatusOUTOFSTOCK';D)SELECTprod_id||q”'snotavailable”ROMproduct_statusWHEREstatus=‘OUTOFSTOCK';E)SELECTprod_idq’snotavailable"FROMproduct_statusWHEREstatus=‘OUTOFSTOCK';F)SELECTprod_id“CURRENTAVAILABILITY”||q’(‘snotavailable)’FROMproduct_statusWHEREstatue=‘OUTOFSTOCK';Answer:ABQ35.ExaminethisdescriptionoftheBOOKStablecontaining100rows:
Q38.Whileoneofyourdatabaseswasinmountstate,thedatafileswererenamedbecausetheyhadbeenmovedtoanewfilesystem.Thedatabasewasthenopened.WhichtwostatementsaretrueA)DBA_DATA_FILESdisplaystheoriginalnameforthedatafiles.B)V$DATAFILEdisplaysthenewnamesforthedatafiles.C)DBA_DATA_FILESdisplaysboththenewnameandtheoldnameforthedatafiles.D)DBA_DATA_FILESdisplaysthenewnameforthedatafiles.
E)DBA_DATA_FILESmustberesynchronizedmanuallywiththecontrolfileanordertohaveitdisplaythenewfilenames.Answer:BD解析：数据文件改名字，数据字典和动态性能视图会自动更新
Q39.Youexecutethiscommand:
G.SegmentscanspanmultipletablespsacesAnswer:CDF（解析：这是相同的提问，不同的答案的另外一种考法。Smallfile表空间不是说表空间尺寸一定小于bigfile表空间。A答案跟bigfile表空间有冲突。）Q46.WhichtwostatementsaretrueregardingaSAVEPOINT(Choosetwo.)A.RollingbacktoaSAVEPOINTcanundoaCREATEINDEXstatementB.RollingbacktoaSAVEPOINTcanundoaTRUNCATEstatementC.OnlyoneSAVEPOINTmaybeissuedinatransactionD.ASAVEPOINTdoesnotissueaCOMMITE.RollingbacktoaSAVEPOINTcanundoaDELETEstatementAnswer:DE（解析：SAVEPOINT是一个很重要的考点，不涉及到版本问题，以前还出现过不同的考试方法。）
（解析：题目的意思是使用一个变量的值第一次输入以后，后面引用的话就不用输入了，那么可以使用&&符号来使用原来变量的值。）Q51.ExaminethedescriptionoftheCUSTOMERStable:
YouwanttodisplaydetailsofallcustomerswhoresideincitiesstartingwiththeletterDfollowedbyatleasttwocharacters.WhichquerycanbeusedA.SELECT*FROMcustomersWHEREcityLIKE‘D_%’;B.SELECT*FROMcustomersWHEREcity=‘%D_’;C.SELECT*FROMcustomersWHEREcityLIKE‘D_’;D.SELECT*FROMcustomersWHEREcity=‘D_%’;Answer:A（解析：此题关键的地方是首字母D打头，必须至少两个字符，那么就必须要D_%，而不能用D%，这个可能导致城市的名字只有一个D的行也会被查出。该题型容易变题。）Q52.Examinethiscommand:
Whichtwostatementsaretrue(Choosetwo.)A.DMLmaybeperformedontableswithoneormoreextentsinthisdatafileduringtheexecutionofthiscommand.B.ThetablespacecontainingSALES1.DBFmustbealteredREADONLYbeforeexecutingthecommand.C.ThetablespacecontainingSALES1.DBFmustbealteredOFFLINEbeforeexecutingthecommand.D.IfOracleManagedFiles(OMF)isused,thenthefileisrenamedbutmovedtoDB_CREATE_FILE_DEST.E.ThefileisrenamedandstoredinthesamelocationAnswer:AE（解析：12c支持在线移动数据文件，所以不需要把数据文件变成只读状态，该命令用到了reuse选项，而且路径没有变化，所以只是改了名字。）Q53.WhichthreestatementsaretrueaboutdroppingandunusedcolumnsinanOracledatabase(Choosethree.)A.AprimarykeycolumnreferencedbyanothercolumnasaforeignkeycanbedroppedifusingtheCASCADEoption.B.AnUNUSEDcolumn’sspaceisreclaimedautomaticallywhentheblockcontainingthatcolumnisnextqueried.C.AnUNUSEDcolumn’sspaceisreclaimedautomaticallywhentherowcontainingthatcolumnisnextqueried.
D.Partitionkeycolumnscannotbedropped.E.ADROPCOLUMNcommandcanberolledbackF.AcolumnthatissettoUNUSEDstillcountstowardsthelimitof1000columnspertableAnswer:ADF解析：被设置为unused列应该还算在1000个列的限制里面。Q54.Examinethefollowingcommand:CREATETABLEPRODUCTS
(
Q62.WhichthreearetypesofsegmentsinanOracleDatabase(Choosethree.)A)undoB)indexC)storedproceduresD)sequencesE)tablesF)clustersAnswer:BEF解析：答案BEF都是不同段的类型，其实undo也是属于段，我们这道题不选择它，是因为它跟其它的段类型不属于同一类
Q63.WhichtwostatementsaretrueabouttheresultsofusingtheINTERSECToperatorincompoundqueries(Choosetwo.)A)ColumnnamesineachSELECTinthecompoundquerycanbedifferentB)ThenumberofcolumnsineachSELECTinthecompoundquerycanbedifferentC)ReversingtheorderoftheintersectedtablescansometimesaffecttheoutputD)INTERSECTreturnsrowscommontobothsidesofthecompoundqueryE)INTERSECTignoresNULLsAnswer:AD解析：复合查询的列名可以不一样，但是个数和数据类型必须一样；INTERSECT获得是交集，
Q67.Inoneofyourdatabases,theuserHRhasthepasswordHRMGR.Youwanttoconnecttoadatabaseinstancewhoselistenerlistensonport1531byusingthisstatement:CONNECTHR/HRMGR@orclNonameserverisused.WhichstatementistrueaboutORCLA)ItmustbethevalueoftheSERVICE_NAMESparameterontheclientsideB)Itmustresolvetoavalidconnectdescriptorintheserver’stnsnames.orafileC)Itmustresolvetoavalidconnectdescriptorintheclient’stnsnames.orafileD)ItmustbethenameofthedatabasetowhoseinstanceHRwishestoconnectE)ItmustbethenameoftheserverrunningthedatabasetowhoseinstanceHRwishestoconnectAnswer:C解析：使用连接别名，那么这个别名必须是在客户端定义的，指定了连接的数据库信息。
Q68.Whichtwostatementsaretrueaboutviewsusedforviewingtablespaceanddatafileinformation(Choosetwo.)A)TablespacefreespacecanbeviewedinV$TABLESPACEB)V$TABLESPACEdisplaysinformationthatiscontainedinthecontrolfileabouttablespacesC)V$TABLESPACEdisplaysinformationabouttablespacescontainedinthedatadictionaryD)TablespacefreespacecanbeviewedinDBA_TABLESPACESE)AdatafilecanberenamedwhenthedatabaseisinMOUNTstateandthenewfilenameisdisplayedwhenqueryingDBA_DATA_FILESafterthedatabaseisopenedAnswer:BE解析：答案E很有深度，前面半句话容易理解，后面半句话意义很深，数据字典必须要在数据库打开的情况下才能够查询
Q69.ExaminethedescriptionofthePROMOTIONStable:
Youwanttodisplaytheuniquepromotioncostsineachpromotioncategory.Whichtwoqueriescanbeused(Choosetwo.)A)SELECTpromo_cost,promo_categoryFROMpromotionsORDERBY1;B)SELECTDISTINCTpromo_cost||’in‘||DISTINCTpromo_categoryFROMpromotionsORDERBY1;C)SELECTDISTINCTpromo_category||‘has‘||promo_costASCOSTSFROMpromotionsORDERBY1;D)SELECTpromo_category,DISTINCTpromo_costFROMpromotionsORDERBY2;E)SELECTDISTINCTpromo_category,promo_costFROMpromotionsORDERBY1;Answer:CE解析：CE两个都能够查询出正确的结果，其它的错误在语法上，distinct只能在select子
句第一个位置使用，不能有两个distinct语句。Q70.ExaminethedescriptionofthePROMOTIONStable:
Managementrequiresareportofuniquepromotioncostsineachpromotioncategory.WhichquerywouldsatisfythisrequirementA)SELECTDISTINCTpromo_category,promo_costFROMpromotionsORDERBY1B)SELECTpromo_category,DISTINCTpromo_costFROMpromotionsC)SELECTDISTINCTpromo_cost,promo_categoryFROMpromotionsD)SELECTDISTINCTpromo_cost,DISTINCTpromo_categoryFROMpromotions;Answer:A解析：AC两个都能够查询出正确的结果，但是题目要的是最佳答案，基于题目的要求，A答案可以提供更加具有可读性的结果
expressionE)MultipleparenthesescanbeusedtooverridethedefaultprecedenceofoperatorsinanexpressionAnswer:DE解析：本题的难点在于英文的理解A、连接运算符||始终在表达式中的加减运算之前求值B、空值影响表达式中运算符的优先级C、在SQL语句的表达式中，+二进制运算符的优先级最高D、具有相等优先级的算术运算符在表达式中从左到右求值写表达式中运算符的默认优先级
Q73.Inoneofyourdatabases,userKINGis:1.NotaDBAuser2.Anoperatingsystem(OS)userExaminethiscommandanditsoutput:
）
Q74.ViewtheExhibitsandexaminethestructureoftheCOSTSandPROMOTIONStables.YouwanttodisplayPROD_IDSwhosepromotioncostislessthanthehighestcostPROD_IDinapromotiontimeinterval.ExaminethisSQLstatement:
WhatwillbetheresultA)ItgivesanerrorbecausetheALLkeywordisnotvalidB)ItgivesanerrorbecausetheGROUPBYclauseisnotvalidC)ItexecutessuccessfullybutdoesnotgivetherequiredresultD)ItexecutessuccessfullyandgivestherequiredresultAnswer:D
Q77.WhichtwostatementsaretrueabouttheDUALtable(Choosetwo.)A)ItcanbeaccessedonlybytheSYSuserB)ItconsistsofasinglerowandsinglecolumnofVARCHAR2datatypeC)ItcandisplaymultiplerowsbutonlyasinglecolumnD)ItcanbeusedtodisplayonlyconstantsorpseudocolumnsE)ItcanbeaccessedbyanyuserwhohastheSELECTprivilegeinanyschemaF)ItcandisplaymultiplerowsandcolumnsAnswer:EF（解析：dual只是为了满足语法上的需要而设计的一个虚拟表。）SQL>selectuser,sysdate,sum(900)fromdual;USERSYSDATESUM(900)---------------------------------------
Q79.ExaminethedescriptionoftheEMPLOYEEStable:
WhichqueryisvalidA)SELECTdept_id,AVG(MAX(salary))FROMemployeesGROUPBYdept_id;B)SELECTdept_id,join_date,SUM(salary)FROMemployeesGROUPBYdept_id;C)SELECTdept_id,join_date,SUM(salary)FROMemployeesGROUPBYdept_id,join_date;D)SELECTdept_id,MAX(AVG(salary))FROMemployeesGROUPBYdept_id;Answer:C解析：本道题考的是分组函数的语法规则，没有出现在分组函数中的列必须要出现在groupby子句中，但是AD答案不能执行，因为这里用到了嵌套函数。
Q80.Whatistrueaboutnon-equijoinstatementperformance(Choosetwo.)A)TablealiasescanimproveperformanceB)TheBETWEENconditionalwaysperformsbetterthanusingthe>=and<=conditionsC)ThejoinsyntaxusedmakesnodifferencetoperformanceD)TheBETWEENconditionalwaysperformslesswellthanusingthe>=and<=conditionsE)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntaxAnswer:AE解析：多表连接使用别名可以提高查询性能，使用oracle的join语法更适合oracle的运行环境
Q81.Inyourdatacenter,OracleManagedFiles(OMF)isusedforalldatabases.Alltablespacesaresmallfiletablespaces.SALES_Q1isapermanentuser-definedtablespaceintheSALESdatabase.ExaminethiscommandwhichisabouttobeissuedbyaDBAloggedintotheSALESdatabase:ALTERTABLESPACEsales_q1ADDDATAFILE;Whicharetwoactions,eitheroneofwhichyoucouldtaketoensurethatthecommandexecutessuccessfully(Choosetwo.)A)EnsurethatDB_RECOVERY_FILE_DESTandDB_CREATE_FILE_DESTeachspecifywithatleast50Mbofavailablespace.B)SpecifyapathintheDATAFILEclauseofthecommandspecifyingalocationwithatleast100Mofavailablespace.C)EnsurethatDB_CREATE_FILE_DESTspecifiesalocationwithatleast100Mbofavailablespace.D)AddtheAUTOEXTENDONclausewithNEXTsetto100M.E)EnsurethatDB_RECOVERY_FILE_DESTandDB_CREATE_FILE_DESTeachspecifylocationswithatleast50Mbofavailablespace.Answer:CD解析：经过实验，OMF管理的数据文件，它的名字由oracle自动起名；而且默认是100M，自动扩展
Q82.Choosetwo.Examinethisparameter:NAMETYPEVALUE-----------------------------------------------------------------------------------db_create_file_deststring/u01/app/oracle/oradata/prod/Nowexaminethisstatement:SQL>CREATETABLESPACEdata_olap;Tablespaceiscreated.WhichtwoaretrueaboutthistablespaceanditsattributesA)ThedatafileiscreatedwithanamegeneratedbytheOracleInstance.B)Thedatafilesizeis100M.C)Thetablespaceiscreatedwithmanualsegmentspacemanagement.
D)Thedatafilethatiscreatedisnotautoextensible.E)Thetablespaceisadictionarymanagedtablespace.Answer:AB解析：OMF管理的数据文件，它的名字由oracle自动起名；而且默认是100M，自动扩展。Q83.WhichtwostatementsaretrueaboutOracleManagedFiles(OMF)A)OMFcannotbeusedinadatabasethatalreadyhasdatafilescreatedwithuserspecifieddirections.B)ThefilesystemdirectionsthatarespecifiedbyOMFparametersarecreatedautomatically.C)OMFcanbeusedwithASMdiskgroups,aswellaswithrawdevices,forbetterfilemanagement.D)OMFautomaticallycreatesuniquefilenamesfortablespacesandcontrolfiles.E)OMFmayaffectthelocationoftheredologfilesandarchivedlogfiles.Answer:CD解析：OMF管理的数据文件，它的名字由oracle自动起名；而且默认是100M，自动扩展。Q84.Examinethiscommandandsomepartialoutput:
Answer:DEF解析：OracleNetManager可以用来配置监听和tns，但是不提供集中管理功能，同时emcc也可以用来配置监听
Q91.WhichthreestatementsaretrueaboutundosegmentsandtheuseofundobytransactionsinanOracledatabaseinstance(Choosethree.)A)AnundosegmentmaybeusedbymultipletransactionssimultaneouslyB)Undosegmentscanwraparoundtothefirstextentwhenatransactionfillsthelastextendof
Whichtwostatementsaretrue(Choosetwo.)A)Employee100willhaveSALARYsettothesamevalueastheSALARYofemployee200B)Employee200willhaveJOB_IDsettothesamevalueastheJOB_IDofemployee100C)Employee200willhaveSALARYsettothesamevalueastheSALARYofemployee100D)Employee100willhaveJOB_IDsettothesamevalueastheJOB_IDofemployee200E)Employees100and200willhavethesameJOB_IDasbeforetheupdatecommandF)Employees100and200willhavethesameSALARYasbeforetheupdatecommandAnswer:AD解析：因为是以id=100为条件，所以描述的时候应该说100的客户将跟200的客户有相同的salary和job_id。Q96.WhichtwoaretrueaboutaSQLstatementusingSEToperatorssuchasUNION(Choosetwo.)A)Thedatatypegroupofeachcolumnreturnedbythesecondquerymustmatchthedatatypegroupofthecorrespondingcolumnreturnedbythefirstquery.B)Thenumber,butnotnames,ofcolumnsmustbeidenticalforallSELECTstatementsinthe
解析：DBTIMEZONE：是数据库服务器所在的时区。SESSIONTIMEZONE：是你的会话的时区。例如：数据库服务器是放在英国(+00:00时区)，而你在中国(+08:00)访问数据库，则SESSIONTIMEZONE就是+08:00,DBTIMEZONE就是+00:00db是数据库的，session是针对当前会话的，因为时区在会话级可以改变以下是测试结果：SQL>altersessionsettime_zone='+00:00'SQL>selectSYSTIMESTAMPfromdual;SYSTIMESTAMP----------------------------------------27-JUL-2012.53.54.730359AM-04:00SQL>selectCURRENT_TIMESTAMPfromdual;CURRENT_TIMESTAMP------------------------------------------------------------------------------27-JUL-2004.54.15.696145AM+00:00SQL>selectCURRENT_DATEfromdual;CURRENT_D-----------------------27-JUL-20
Q100.Whichthreestatementsaretrueaboutinnerandouterjoins(Choosethree.)A)AfullouterjoinreturnsmatchedandunmatchedrowsB)AninnerjoinreturnsmatchedrowsC)OuterjoinscanonlybeusedbetweentwotablesperqueryD)AfullouterjoinmustuseOraclesyntaxE)OuterjoinscanbeusedwhentherearemultiplejoinconditionsontwotablesF)AleftorrightouterjoinreturnsonlyunmatchedrowsAnswer:ABE解析：fullouterjoin只能用ANSI的语法，ORACLE语法不支持，同时返回匹配的和不匹配的行；外部连接可以在多张表上执行，所以B答案错；同时外部连接可以有多个限制条件。Q101.ExaminethisdescriptionoftheTRANSACTIONStable:
WhichtwoSQLstatementsexecutesuccessfully(Choosetwo.)A)SELECTcustomer_idAS“CUSTOMER-ID”,transaction_dateASDATE,amount+100“DUES”FROMtransactions;B)SELECTcustomer_idAS“CUSTOMER-ID”,transaction_dateAS“DATE”,amount+100DUES
Q103.ViewtheExhibitandexaminethestructureoftheCUSTOMERStable.
UsingtheCUSTOMERStable,youmustgenerateareportthatdisplaysacreditlimitincreaseof15%forallcustomers.Customerswithnocreditlimitshouldhave“NotAvailable”displayed.WhichSQLstatementwouldproducetherequiredresultA)SELECTNVL(TO_CHAR(cust_credit_limit*.15),‘NotAvailable’)“NEWCREDIT”FROMcustomers
B)SELECTTO_CHAR(NVL(cust_credit_limit*.15),‘NotAvailable’))“NEWCREDIT”FROMcustomersC)SELECTNVL(cust_credit_limit*.15,‘NotAvailable’)“NEWCREDIT”FROMcustomersD)SELECTNVL(cust_credit_limit,‘NotAvailable’)*.15“NEWCREDIT”FROMcustomersAnswer:A解析：注意nvl函数返回的数据类型，如果是字符型，则第一个表达式的数据类型也要为字符型
Q104.WhichstatementistrueaboutaggregatefunctionsA)AggregatefunctionscanbenestedtoanynumberoflevelsB)TheAVGfunctionimplicitlyconvertsNULLStozeroC)AggregatefunctionscanbeusedinanyclauseofaSELECTstatementD)TheMAXandMINfunctionscanbeusedoncolumnswithcharacterdatatypesAnswer:D解析：max和min可以用在数字、字符、和日期型的数据类型上。）分组函数不能嵌套太多层
Answer:C解析：如果在释放表碎片的释放不影响当前的操作，那么应该在shrink的时候加上compact选项，等业务空闲的时候再DEALLOCATEUNUSE，所以shrinkspace可以分为两部分进行操作。
Q110.WhichfouractionsarepossibleduringanOnlineDatafileMoveoperationA)CreatinganddroppingtablesinthedatafilebeingmovedB)PerformingfileshrinkofthedatafilebeingmovedC)QueryingtablesinthedatafilebeingmovedD)PerformingBlockMediaRecoveryforadatablockinthedatafilebeingmovedE)FlashingbackthedatabaseF)ExecutingDMLstatementsonobjectsstoredinthedatafilebeingmovedAnswer:ACDF解析：实验证明答案E不行；D答案可以；在线对数据文件进行迁移，意思是迁移过程中包含的对象还可以做ddl和dml操作，但是对数据文件进行恢复操作和收缩大小的操作肯定是不行的，报错：ORA-63000:不允许该操作:正在移动数据文件
Q111.Examinethesecommands:[oracle@host01~]$sqlplusu1/oracleSQL>SELECT*FROMemp;ENOENAMEDN------------------------------------------------------------1Alan22Ben2SQL>exit[oracle@host01~]$catemp.dat1,Alan,23,Cur1,44,Bob,4[oracle@host01~]$sqlldru1/oracleTABLE=empWhichtwostatementsaretrueaboutthesqlldrexecution(Choosetwo.)A)ItoverwritesdatainEMPwithdatainEMP.DATB)ItusesthedatabasebuffercachetoloaddataC)Itgeneratesalogthatcontainscontrolfileentries,whichcanbeusedwithnormalSQL*LoaderoperationsD)ItgeneratesasqlscriptthatitusestoloaddatafromEMP.DATtoEMPE)ItappendsdatafromEMP.DATtoEMPAnswer:CE解析：实验证明，在当前路径下产生一个日志，里面有保护sqlldr控制文件的内容，看来是oracle自动产生控制文件，方便我们使用，而不是省略控制文件。
Q114.WhichthreestatementsaretrueaboutOraclesynonyms(Choosethree.)A)AsynonymcannotbecreatedforaPL/SQLpackageB)AsynonymcanbeavailabletoallusersC)ASEQUENCEcanhaveasynonymD)AsynonymcreatedbyoneusercanrefertoanobjectbelongingtoanotheruserE)AnyusercandropaPUBLICsynonymAnswer:BCD解析：同义词可以在表、存储过程、序列上创建。如果是public同义词，则对所以的用户都可以访问。创建不同的同义词都需要授予不同的权限，只有属主和有droppublicsynonym权限的用户才能够删除public同义词Q115.WhichtwotaskscanbeperformedonanexternaltableA)partitioningthetableB)creatinganinvisibleindexC)updatingthetablebyusinganupdatestatementD)creatingapublicsynonymE)creatingaviewAnswer:DE
解析：外部表就只能创建视图和同义词，其它的更新操作都不允许
Q116.Choosethebestanswer.Examinethesecommands:CONNECT/ASSYSDBASQL>CREATEDIRECTORYdumpdirAS‘/u01/app’;Directorycreated.SQL>GRANTREAD,WRITEONDIRECTORYdumpdirTOPUBLIC;Grantsucceeded.SQL>GRANTUSERTESTIDENTIFIEDBYtest;Usercreated.SQL>GRANTCREATESESSION,RESOURCE,UNLIMITEDTABLESPACETOtest;Grantsucceeded.SQL>CONNtest/test.Connected.SQL>CREATETABLEtest_employees(idNUMBER(3),nameVARCHAR2(20),salaryNUMBER(7));Tablecreated.SQL>CREATESYNONYMempFORtest_employees;Synonymcreate.Nowexaminethiscommand:$expdptest/testDIRECTORY=dumpdirDUMPFILE=test_emp.dmpLOGFILE=test.logTABLES=empWhichistrueA)Itperformstheexportsuccessfully.B)Itthrowsanerrorasthetabletest_employeesisemptyandCONTENT=data_onlyisspecified.C)ItthrowsanerrorastheQUERYparameterisnotsupportedonanemptytable.D)ItthrowsanerrorasDataPumpcannotfindatablecalledTEST.EMP.Answer:D解析：之前以为可以通过同义词导出表，结果实验发现不能，为什么oracle要这么设计呢，这道题说了半天就是证明不能通过同义词来导表。
Q117.YouissuethefollowingcommandtodropthePRODUCTStable:SQL>DROPTABLEproducts;WhichthreestatementsaretrueabouttheimplicationofthiscommandA)Alldataalongwiththetablestructureisdeleted.B)Apendingtransactioninthesessioniscommitted.C)Allindexesonthetableremainbuttheyareinvalidated.D)Allviewsandsynonymsonthetableremainbuttheyareinvalidated.E)Alldatainthetableisdeletedbutthetablestructureremains.Answer:ABD解析：由于题目没有说回收站的事情，所以按照表被真正删除来处理。注意答案B，它说的是跟drop操作同一个会话里面的事务会被提交Q118.ViewtheExhibitandexaminethestructureofthePRODUCTStable.Whichtwotasksrequiresubqueries(Choosetwo.)
A)DisplaythenumberofproductswhosePROD_LIST_PRICEismorethantheaveragePROD_LIST_PRICEB)DisplaysupplierswhosePROD_LIST_PRICEislessthan1000C)DisplayproductswhosePROD_MIN_PRICEismorethantheaveragePROD_LIST_PRICEofallproducts,andwhosestatusisorderableD)Displaythetotalnumberofproductssuppliedbysupplier102whichhaveaproductstatusofobsoleteE)DisplaytheminimumPROD_LIST_PRICEforeachproductstatusAnswer:AC解析：什么时候用子查询，注意条件语句中进行比较的值如果涉及分组函数的值，则需要子查询。
Q119.TheSALES_Q1andUSERStablespacesexistinoneofyourdatabasesandTEMPisatemporarytablespace.Segmentcreationisnotdeferred.Youexecutethiscommand:
WhichthreestatementsmustbetruesothattheSALESusercancreatetablesinSALES_Q1(Choosethree.)A)ThesalesusermusthaveaquotaontheTEMPtablespaceB)ThesalesusermusthaveaquotaontheSALES_Q1tablespacetoholdtheinitialextendsofalltablestheyplantocreateintheirschemaC)ThesalesusermusthavebeengrantedtheCREATESESSIONprivilegeD)ThesalesusermusthavetheirquotaontheuserstablespaceremovedE)ThesalesusermusthaveaquotaontheSALES_Q1tablespacetoholdalltherowstobeinsertedintoanytableintheirschema
F)ThesalesusermusthavebeengrantedtheCREATETABLEprivilegeAnswer:BCF解析：答案C与F比较简单，这里为什么选择B而不选择E，因为题目是只要建表成功就行，所以分配的quata只要够分配初始化区就可以了，这个答案有点深度。Q120.WhichthreestatementsaretrueabouttabledatastorageinanOracleDatabase(Choosethree.)A)DatablockheaderscontaintheirownDataBlockAddress(DBA)B)AtablerowpiececanbechainedacrossseveraldatabaseblocksC)MultiplerowpiecesfromthesamerowmaybestoredindifferentdatabaseblocksD)MultiplerowpiecesfromthesamerowmaybestoredinthesameblockE)DatablockfreespaceisalwayscontiguousinthemiddleoftheblockF)IndexblockfreespaceisalwayscontiguousinthemiddleoftheblockAnswer:ABD解析：该题难度比较大，块头都记录了块的id号；一行能够链接到其它块（行迁移）；同一行的多个片段可以存放在同一个块，因为update造成的；由于有行迁移现象，所以答案C是错误的Q121.ExaminethedescriptionoftheBOOKStable:
Thetablehas100rows.Examinethissequenceofstatementsissuedinanewsession:INSERTINTObooksVALUES(‘ADV112’,‘AdventuresofTomSawyer’,NULL,NULL);SAVEPOINTa;DELETEFROMbooks;ROLLBACKTOSAVEPOINTa;ROLLBACK;Whichtwostatementsaretrue(Choosetwo.)A)ThesecondROLLBACKcommanddoesnothingB)ThesecondROLLBACKcommandreplaysthedeleteC)ThefirstROLLBACKcommandrestoresthe101rowsthatweredeleted,leavingtheinsertedrowstilltobecommittedD)ThesecondROLLBACKcommandundoestheinsertE)ThefirstROLLBACKcommandrestoresthe101rowsthatweredeletedandcommitstheinsertedrowAnswer:CD解析：有关savepoint与rollback组合的考题很多，这个只能根据实际情况去判断。第一个rollback是回滚了delete操作，第二rollback是把所有的操作都回滚，剩下的就只有insert操作被回滚了。
Q122.WhichthreestatementsaretrueaboutexternaltablesinOracle18candlaterreleases(Choosethree.)A)ExternaltablefilescanbeusedforotherexternaltablesinadifferentdatabaseB)TheORACLE_LOADERaccessdrivercanbeusedtounloaddatafromadatabaseintoanexternaltableC)TheORACLE_DATAPUMPaccessdrivercanbeusedtounloaddatafromadatabaseintoanexternaltableD)TheycannotbepartitionedE)TheORACLE_DATAPUMPaccessdrivercanbeusedtoloaddataintoadatabasefromanexternaltableF)TheysupportUPDATESbutnotINSERTSandDELETESAnswer:ACD解析：C答案的意思是把数据库里面的数据取出（unload这个词有多种意思，这里理解为取出）然后放到外部表文件中。
Q124.WhichtwotaskscanyouperformusingDBCAfordatabases(Choosetwo.)A)ConfigureanonstandardblocksizeforanewdatabaseB)RegisteranewdatabasewithanavailableEnterpriseManagerManagementserverC)ChangethestandardblocksizeofanexistingdatabaseD)ConfigureincrementalbackupsforanewdatabaseE)EnableflashbackdatabaseforanexistingdatabaseAnswer:BD解析：DBCA能够为已存在的数据库所做的操作有限，只能在创建新数据库的时候做一些设置。但是使用dbca无法做上面的操作。
Q125.YouplantocreateadatabasebyusingtheDatabaseConfigurationAssistant(DBCA),withthefollowingspecifications:Applicationswillconnecttothedatabaseviaamiddletier.Thenumberofconcurrentuserconnectionswillbehigh.Thedatabasewillhavemixedworkload,withtheexecutionofcomplexBIqueriesscheduledatnight.WhichDBCAoptionmustyouchoosetocreatethedatabaseA)aGeneralPurposedatabasetemplatewithdefaultmemoryallocationB)aDataWarehousedatabasetemplate,withthededicatedservermodeoptionandAMMenabledC)aGeneralPurposedatabasetemplate,withthesharedservermodeoptionandAutomaticMemoryManagement(AMM)enabledD)adefaultdatabaseconfigurationAnswer:C解析：根据题目要求配置共享服务器连接的数据库模板。
Q126.WhichtwooptionscanbeconfiguredforanexistingdatabasebyusingtheDatabaseConfigurationAssistant(DBCA)A)DatabaseResidentConnectionPoolingB)OracleSuggestedBackupStrategyC)DatabaseVaultinORACLE_HOMED)NondefaultblocksizetablespacesE)ConfigureLabelSecurityAnswer:CE解析：这道题比较偏门，没有什么技术含量，使用dbca对已经存在的数据库进行修改时可以修改的内容就一目了然。
Q127.Youexecutethisquery:SELECTTO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),‘MON’),‘dd“Mondayfor”fmMonthrrrr’)WhatistheresultA)ItexecutessuccessfullybutdoesnotreturnanyresultB)ItreturnsthedateforthefirstMondayofthenextmonthC)ItgeneratesanerrorD)ItreturnsthedateforthelastMondayofthecurrentmonth
Answer:B解析：函数里面求当前月的最后一天的下一个周一，所以结果为下一个月的第一个周一。
Q130.IntheSALESdatabase,DEFERRED_SEGMENT_CREATIONisTRUE.Examinethiscommand:SQL>CREATETABLET1(c1INTPRIMARYKEY,c2CLOB);Whichsegmentorsegments,ifany,arecreatedasaresultofexecutingthecommandA)T1,anindexsegmentfortheprimarykey,aLOBsegment,andalobindexsegmentB)nosegmentsarecreatedC)T1onlyD)T1andanindexsegmentcreatedfortheprimarykeyonlyE)T1,anindexsegmentfortheprimarykey,andaLOBsegmentonlyAnswer:B解析：经过实验，因为段创建延迟设置为true，建表时只要没有数据，段是不会创建的。Q131.Choosethebestanswer.WhichstatementistrueaboutdatabaselinksA)Apublicdatabaselinkcanbecreatedonlybysys.
Q131.Choosethree.WhichthreestatementsaretrueaboutviewsinanOracleDatabaseA)Rowsinsertedintoatableusingaviewareretainedinthetableiftheviewisdropped.B)Aviewcanbecreatedthatreferstoanon-existenttableinitsdefiningquery.C)Viewshavenoobjectnumber.D)Viewshavenosegment.E)ASELECTstatementcannotcontainaWHEREclausewhenqueryingaviewcontainingaWHEREclauseinitsdefiningquery.F)Viewscanjointablesonlyiftheybelongtothesameschema.Answer:ABD解析：有个视图的考点很多，这是最新出现的，不难。
Q133.Choosetwo.WhichtwotaskscanbeperformedintheNOMOUNTstateA)fulldatabaserecoveryB)re-creatingcontrolfilesC)renamingdatafiles.D)creatingadatabase
E)enablingonlineredologarchivingAnswer:BD解析：考点简单，概念题
Q135.Choosetwo.WhichtwoarebenefitsofexternaltablesA)Theresultsofacomplexjoinoraggregatingfunctionorbothcanbeunloadedtoafilefortransportationtoanotherdatabase.B)TheycanbequeriedwhilethedatabaseisintheMOUNTstatelikedynamicperformanceviews.C)TheysupportDELETESwhichtransparentlydeletesrecordsinthefilesystemasiftheyweretablerows.D)TheysupportUPDATESwhichtransparentlyupdatesrecordsinthefilesystemasiftheyweretablerows.E)Theycanbequeried,transformed,andjoinedwithothertableswithouthavingtoloadthedatafirst.Answer:AE解析：外部表可以做任何的查询操作，好处是不需要加载到数据库中，但是不支持任何的dml操作。Q136.Choosetwo.DATADIR1andDATADIR2aredatabasedirectoryobjects.Examinethiscommand:[orac1e@host01~]expdpsystem/oracle\>FULL=Y\>DUMPFILE=datadir1:full%U.dmp,datadir2:full%U.dmp\>FILESIZE=2G\>PARALLEL=4\>LOGFILE=datadir1:/expfull.1og\>JOB_NAME=expfullWhichtwostatementsaretrueabouttheexpdpoperationA)Itstartsonlywhenfourworkerprocessesareavailable.
F)Updatestatementscanresultinoneormorechainedrows.Answer:AEF解析：update会造成行迁移和行链接，insert会造成行链接不会造成行迁移；行链接的原因是一行的数据太大导致一个数据块都存放不下整行的数据。
Q149.choosethreewhichthreestatementsaretrueaboutGLOBALTEMPORARYTABLESA)ADELETEcomandonGLOBALTEMPORARYTABLEcannotberolledbackB)GLOBALTEMPORARYTABLErowsinsertedbyasessionareavailabletoanyothersessionwhose
Q151.Choosetwo.WhichtwostatementsaretrueabouttheOracleDataDictionaryA)Alldatadictionaryviewjoinbasetablestodynamicperformanceviews.B)Datadictionarybasetablescanbequerieddirectly.C)ItisownedbytheSYSuser.D)ItisownedbytheSYSTEMuser.E)Datadictionaryviewsarealwayscreatedwithqueriesthatjointwoormorebasetables.Answer:BC解析：数据字典基表可以直接查询，并且属于基表，只能够在数据库open的时候看到。
Q152.Choosetwo.Youexecutethiscommand:CREATESMALLPILETABLESPACEsalesDATAFILE'/u01/app/oracle/sales01.dbf'SIZE5GSEGMENTSPACEMANAGEMENTAUTO;WhichtwostatementsaretrueabouttheSALEStablespaceA)Itisalocallymanagedtablespace.B)ItmustbesmallerthanthesmallestBIGPILEtablespace.C)Freespaceismanagedusingfreelists.D)Itusesthedatabasedefaultblocksize.E)Anydatafilesaddedtothetablespacemusthaveasizeof5gigabytes.Answer:AD解析：虽然该题是新出现，但是概念比较简单
F)TheycanbeusedintheHAVINGclause.Answer:AEF解析：单行子查询概念比较简单，注意答案BD，单行子查询可以放在比较操作符的两边，以前有过类似的题目。答案F比较出人意外，居然可以使用。Q158.Choosethree.Whichthreestatementsaretrueaboutsingle-rowfunctionsA)Theycanacceptonlyoneargument.B)Theycanbenestedtoanylevel.C)Theyreturnasingleresultrowpertable.D)Thedatatypereturnedcanbedifferentfromthedatatypeoftheargument.E)Theargumentcanbeacolumnname,variable,literaloranexpression.F)TheycanbeusedonlyintheWHEREclauseofaSELECTstatement.Answer:BDE解析：从单行函数的定义和语法可以判断答案BDE是对的。答案B只要符合规律，可以嵌套任何层次。)单行函数的定义如下：操作数据项接收参数并返回一个值在返回的每一行上进行处理每一行都有一个处理结果可能要修改数据类型可以进行嵌套语法：function_name(column|expression,[arg1,arg2,...])可以有多个参数：CONCAT(Good,String)
解析：ADR的基本目录是可以共享的，能够都不设置，默认会用到ORACLE_BASE目录，而这个变量对应所以的实例都是一样的。ADR提供诊断信息，并不依赖数据库启动。AWR快照是存放在sysaux表空间中。Q161.Choosetwo.WhicharetwooftheaccountmanagementcapabilitiesthatcanbeconfiguredusingOracleprofilesA)themaximumamountofCPUtimeallowedforauser'ssessionsbeforetheiraccountislockedB)thenumberofdaysforwhichanaccountmaybeinactivebeforeitislockedC)themaximumnumberofsessionspermittedforauserbeforethepasswordmustbechanged.D)theabilitytopreventapasswordfromeverbeingreusedE)thenumberofdaysforwhichanaccountmaybeloggedintooneormoresessionsbeforeitislockedAnswer:BD解析：虽然题目只要我们选择2个答案，但是根据查询结果可以看出profile能够做的事情。
建一个directory，如果没有权限，会报如下错误：SQL*Loader-816:errorcreatingtemporarydirectoryobjectSYS_SQLLDR_XT_TMPDIR_00000forfileemployees.dat，该题目在062中出现过。
Q166.Choosetwo.WhichtwostatementsaretrueaboutviewsA)Viewscanbeupdatedwithouttheneedtore-grantprivilegesontheview.B)Viewscanbeindexed.C)Aviewmustonlyrefertotablesinitsdefiningquery.D)TheWITHCHECKclausepreventscertainrowsfrombeingdisplayedwhenqueryingtheview.E)TheWITHCHECKclausepreventscertainrowsfrombeingupdatedorinsertedintheunderlyingtablethroughtheview.Answer:AE解析：经过实验，视图是不能创建索引的；withcheck子句是预防通过视图对基表进行update和insert，该答案出现的机率很大Q167.choosetwoWhichtwostatementsaretrueaboutviewsA)Viewscanbeupdatedwithouttheneedtore-grantprivilegesontheview.B)Viewscanbeindexed.
Q170.Choosetwo.WhichtwostatementsaretrueaboutthetoolsusedtoconfigureOracleNetServicesA)Thelsnrctlutilityrequiresalistener.orafiletoexistbeforeitisstarted.B)EnterpriseManagerCloudControlcanbeusedtocentrallyconfigurenetservicenamesforanydatabaseservertarget.
Q174.Choosetwo.WhichtwostatementsaretrueregardingindexesA)TheRECYCLEBINnevercontainsindexes.B)Anupdatetoatablecanresultinupdatestoanyorallofthetable'sindexes.C)Anon-uniqueindexcanbealteredtobeunique.D)Anupdatetoatablecanresultinnoupdatestoanyofthetable'sindexes.E)tablebelongingtooneusercannothaveanindexthatbelongstoadifferentuser.Answer:BD解析：索引应该会被放到回收站，只不过我们看不到而已，通过实验发现表被闪回后，原来的索引的名字都被改成跟回收站的名字一样；一个update操作如果修改了带有索引的列，则就会同时更新，如果没有就不会更新。
Q175.Choosetwo.WhichtwostatementsaretrueconcerninglogicalandphysicaldatabasestructuresA)Asegmentmighthaveonlyoneextent.B)Asegment'sblockscanbeofdifferentsizes.C)Segmentscanspanmultipletablespaces.D)Alltablespacesmayhaveoneormoredatafiles.E)Asegmentcanspanmultipledatafilesinsometablespaces.Answer:AE解析：有关段和区和数据文件的关系每个版本都会出现。
Q176.ChoosetwoWhichtwostatementsaretrueabouttheOraclejoinandANSIjoinsyntaxA)TheOraclejoinsyntaxperformslesswellthantheSQL:1999compliantANSIjoinsyntax.B)TheOraclejoinsyntaxsupportscreationofaCartesianproductoftwotables.C)TheOraclejoinsyntaxlackstheabilitytodoouterjoins.D)TheSQL:1999compliantANSIjoinsyntaxsupportscreationofaCartesianproductoftwotables.E)TheOraclejoinsyntaxperformsbetterthantheSQL:1999compliantANSIjoinsyntax.Answer:DE解析：有关oracle语法和ANSI语法的题考也是每次必考，oracle总得宣传一下自己的好处。
Q177.Choosethree.WhichthreerelationshipsbetweeninstancesandOracledatabasesarepossiblewithoutusingMulti-tenantA)oneinstanceononeserverthathasnodatabasemountedB)oneinstanceononeservermountingandopeningonedatabaseC)oneinstanceononeservermountingandopeningmultipledatabasesD)twoormoreinstancesonseparateserversallmountingandopeningthesamedatabaseE)oneinstanceononeservermountingmultipledatabasesAnswer:ABD解析：实例和数据库的关系比较简单，答案D指的是rac环境。
变种题？和177相似WhichthreeinstancesituationsarepossiblewiththeOracleDatabaseserverwithoutmultitenant(Choosethree.)A.twoormoreinstancesonseparateserversallassociatedwithonedatabaseB.oneinstanceononeserverassociatedwithonedatabaseC.oneinstanceononeserverassociatedwithtwoormoredatabasesonthesameserverD.oneinstanceononeservernotassociatedwithanydatabaseE.oneinstanceononeserverassociatedwithtwoormoredatabasesonseparateserversAnswer:ABD（解析：A的情况适合于RAC环境；B的情况适合于单实例；D的情况针对实例可以和数据库不关联。）
Q178.Choosetwo.ExaminethedescriptionoftheSALEStable:Name-------------------------------PRODUCT_IDCUSTOMER_IDTIME_IDCHANNEL_IDPROMO_IDQUANTITY_SOLDPRICEAMOUNT_SOLDExaminethisstatement:CREATETABLEsales1(prod_id,cust_id,quantity_sold,price)ASSELECTproduct_id,customer_id,quantity_sold,priceFROMsalesWHERE1=1;
WhichtwostatementsaretrueA)SALES1iscreatedwith55,000rows.B)SALES1iscreatedwithnorows.C)SALES1hasNOTNULLconstraintsonanyselectedcolumnswhichhadthoseconstraintsintheSALEStable.D)SALES1hasPRIMARYKEYandUNIQUEconstraintsonanyselectedcolumnswhichhadthoseconstraintsintheSALEStable.E)SALES1iscreatedwith1row.Answer:AC解析:where1=1就是指满足条件；通过子查询创建的表，只有notnull约束能够继承过来，其它的约束都不行。
Oracle11G在表中引入了虚拟列，虚拟列是一个表达式，在运行时计算，不存储在数据库中，不能更新虚拟列的值。定义一个虚拟列的语法：column_name[datatype][GENERATEDALWAYS]AS[expression][VIRTUAL]1.虚拟列可以用在select，update，delete语句的where条件中，但是不能用于DML语句2.可以基于虚拟列来做分区3.可以在虚拟列上建索引，oracle的函数索引就类似。4.可以在虚拟列上建约束
Q182.Choosetwo.WhichtwoareSQLfeaturesA)providinggraphicalcapabilitiesB)providingvariabledefinitioncapabilitiesC)providingdatabasetransactioncontrolD)processingsetsofdataE)providingupdatecapabilitiesfordatainexternalfilesAnswer:CD解析：SQL语句提供数据处理和事务控制功能；变量的定义属于运行环境提供的功能。Q183.Choosethebestanswer.Examinethisstatement:SELECT1ASid,'John'ASfirst_name,NULLAScommissionFROMDUALINTERSECTSELECT1,'John',NULLFROMDUALORDERBY3;WhatisreturneduponexecutionA)2rowsB)1row.C)0rowsD)anerrorAnswer:B解析：经过实验，发现答案B正确，本题让人感到混乱的就是第一条语句，其实就是多了别名的定义
Q184.Choosetwo.ExaminethedescriptionoftheCUSTOMERStable:NameNullType---------------------------------------------------------------CUSTOMER_IDNOTNULLNUMBER(38)CUSTOMER_NAMENOTNULLVARCHAR2(100)INSERT_DATENOTNULLDATEWhichtwostatementswilldoanimplicitconversionA)SELECT*FROMcustomersWHEREcustomer_id='0001';B)SELECT*FROMcustomersWHERETO_CHAR(customer_id)='0001';C)SELECT*FROMcustomersWHEREcustomer_id=0001;
D)SELECT*FROMcustomersWHEREinsert_date='01-JAN-19';E)SELECT*FROMcustomersWHEREinsert_date=DATE'2019-01-01';Answer:AD解析：隐式转换注意日期默认格式
Q185.Choosetwo.WhichtwostatementsaretrueaboutCURRENT_TIMESTAMPA)ThetimeisinthetimezoneofDBTIMEZONE.B)ItalwaysreturnsthesamevalueasSYSTIMESTAMP.C)ThedateisinthetimezoneofDBTIMEZONE.D)ThevaluevariesdependingonthesettingofSESSIONTIMEZONE.E)ItreturnsthesamedateasCURRENT_DATE.F)ItreturnsavalueofdatatypeTIMESTAMP.Answer:DF解析：该题还有其它的变种题考法。
D)CREATESYNONYMSYS.empFORhcm.employee_records;E)CREATESYNONYMempFORhcm.employee_records;Answer:B解析：大家都可以访问的同义词就是public同义词，没有global同义词。Q192.ChoosetwoExaminethedescriptionoftheEMPLOYEEStable:NameNullType----------------------------------------------------------------------------EMP_IDNOTNULLNUMBEREMP_NAMEDEPT_IDVARCHAR2(40)SALARYNUMBER(8,2)HIRE_DATEDATENLS_DATE_FORMATisDD-MON-RR.WhichtwoquerieswillexecutesuccessfullyA)SELECTdept_id,MAX(SUM(salary))FROMemployeesGROUPBYdept_id;B)SELECTAVG(MAX(salary))FROMemployeesGROUPBYsalary;C)SELECTdept_id,AVG(MAX(salary))FROMemployeesGROUPBYdept_id,salary;D)SELECTdept_id,SUM(salary)FROMemployeesWHEREhire_date>'01-JAN-19'GROUPBYdept_id;E)SELECTdept_id,AVG(MAX(salary))FROMemployeesGROUPBYdept_idHAVINGhire_date>'01-JAN-19';Answer:BD解析：经过测试，BD正确，其它错误的都是嵌套了分组函数，因为嵌套一个分组函数等于要以里面的列做为分组，最后外面还以另外一个列为分组列，所以不支持。
Q193.Choosethebestanswer.ExaminethedescriptionoftheEMPLOYEEStable:NameNullType------------------------------------------------------------------EMPLOYEE_IDNOTNULLNUMBER(38)SALARYNOTNULLNUMBER(38)DEPARTMENT_IDNOTNULLNUMBER(38)Whichstatementincreaseseachemployee'sSALARYbytheminimumSALARYfortheirDEPARTMENT_IDA)UPDATEemployeese1SETsalary=salary+(SELECTMIN(salary)FROMemployeese2);B)UPDATEemployeese1SETsalary=(SELECTe2.salary+MIN(e2.salary)FROMemployeese2WHEREe1.department_id=e2.department_idGROUPBYe2.department_id);C)UPDATEemployeese1SETsalary=salary+(SELECTMIN(e1.salary)FROMemployeese2WHEREe1.department_id=e2.department_id);D)UPDATEemployeese1SETsalary=(SELECTe1.salary+MIN(e2.salary)FROMemployeese2WHEREe1.department_id=e2.department_id);Answer:D
Q195.Choosethree.WhichthreestatementsaretrueaboutindexesandtheiradministrationinanOracledatabaseA)Thesametablecolumncanbepartofauniqueandnon-uniqueindex.B)Ifaqueryfiltersonanindexedcolumnthenitwillalwaysbeusedduringexecutionofthequery.C)Adescendingindexisatypeoffunction-basedindex.D)AnINVISIBLEindexisnotmaintainedwhenDMLisperformedonitsunderlyingtable.E)AnUNUSABLEindexismaintainedwhenDMLisperformedonitsunderlyingtable.F)AnindexcanbecreatedaspartofaCREATETABLEstatement.Answer:ACF（解析：索引默认都是升序；在CREATE语句中如果加上主键约束或者唯一约束，则会自动创建索引。）只要索引的基表在物理上被移动了就会失效,比如move到一个表空间，就是其中一种原因。createindex索引名on表名(字段名desc);--唯一索引的一部分SQL>createuniqueindexe_u_nameonemp(ename,sal);--非唯一索引的一部分SQL>createindexe_u2_nameonemp(ename,mgr);
Q197.Choosethebestanswer.ExaminethedatainthePRODUCTStable:PROD_IDPROD_NAMEPROD_LISTCATEGORY_ID------------------------------------------------------------------------------------101Plate101102Cup201103Saucer201104Knife301105Fork301
Examinethesequeries:1.SELECTprod_name,prod_listFROMproductsWHEREprod_list=ANY(10,20)ANDcategory_id=1;2.SELECTprod_name,prod_listFROMproductsWHEREprod_listIN(10,20)ANDcategory_id=1;3.SELECTprod_name,prod_listFROMproductsWHEREprod_list=ALL(10,20)ANDcategory_id=1;WhichqueriesgeneratethesameoutputA)2and3B)1and2C)1and3D)1,2,and3Answer:B解析：本题1和3都执行错误，所以也算是相同的输出。单行比较操作符用在了多行子查询上
D)YouadditionallyrequireonlytheINDEXprivilegeonthetable.E)TheownerofthetablemustalsohavetheCREATEANYINDEXsystemprivilege.F)TheownerofthetablemusthaveeithertheUNLIMITEDTABLESPACEsystemprivilege,orsufficientquotaonthetablespacewheretheindexwillbecreated.Answer:AC解析：实验证明，帮别人创建索引时，由于该索引属于创建人，所以创建人需要有相应的空间来存放索引；只要有该表select的权限，然后有selectanytable的权限就可以创建其它用户表的索引了
Q205.Choosetwo.WhichtwoobjectprivilegescanberestrictedtoasubsetofcolumnsinatableA)ALTERB)UPDATEC)DELETED)INSERTE)INDEXAnswer:BD解析：对题目的意思不是理解得很清楚，根据以往的考题选择BD两个答案。Q206.Choosetwo.WhichtwoaretrueabouttheUSINGclausewhenjoiningtablesA)Itcanneverbeusedwithanaturaljoin.B)Itisusedtospecifyanexplicitjoinconditioninvolvingoperators.C)Itcanneverbeusedwithafullouterjoin..D)AllcolumnnamesinaUSINGclausemustbequalifiedwithatablenameortablealias.E)Itisusedtospecifyanequijoinofcolumnsthathavethesamenameinbothtables.Answer:AE解析：内连接的using用法，using只能用在equiuejoin，而且必须列的名字要相同，类型可以不同，但是可以隐式转过去
Q209.Choosethree.WhichthreestatementsaretrueabouttheOracleDatabaseserverduringandimmediatelyafterSHUTDOWNTRANSACTIONALA)UncommittedtransactionsareallowedtocontinuetothenextROLLBACK.B)Newconnectionrequestsmadetothedatabaseinstancearerefused.C)Instancerecoveryoccursatthenextrestart.D)Instancerecoveryoccursautomaticallyduringtheshutdown.E)Sessionsremainconnectedeveniftheyarenotrunningatransaction.F)UncommittedtransactionsareallowedtocontinuetothenextCOMMIT.Answer:ABF解析：基于事务的关闭的特点就是会等待事务完成后关库
Q210.Choosefour.WhichfourstatementsaretrueabouttheOracleServerarchitectureA)Apersonorprogramcanhavemorethanonesessionwithaninstancebylogginginwithdifferentusernames.B)Apersonorprogramcanhavemorethanonesessionwithaninstancebylogginginwiththesameusername.C)Aconnectionrepresentsthestateofauser'slogintoaninstance.D)EachserverprocessandbackgroundprocesshastheirownProgramGlobalArea(PGA).E)Thebuffercacheandtheredologbufferareheldinthelargepool.F)Theentiredatadictionaryisalwayscachedinthesharedpooltoimproveperformance.G)Asessionrepresentsthestateofauser'slogintoaninstance.Answer:ABCG解析：该题考的是有个体现架构的知识点，答案F看起来是对的，但是并不是整个数据字典都存放在共享池，而是访问过的数据字典会放在共享池。
Q211.Choosetwo.Examinethiscommand:ALTERDATABASEMOVEDATAFILE'/u01/sales01.dbf'TO'/u02/sales02.dbf';WhichtwostatementsaretrueA)CompressedobjectsinSALES01.DBFwillbeuncompressedinSALES02.DBFafterthemove.B)DMLmaybeperformedontableswithoneormoreextentsinthisdatafileduringtheexecutionofthiscommand.C)Tableswithoneormoreextentsinthisdatafilemaybequeriedduringtheexecutionofthiscommand.D)The"TO"clausecontainingthenewfilenamemustbespecifiedevenifOracleManagedFiles(OMF)isused.E)ItoverwritesanyexistingfilewiththenameSALES02.DBFin/u02bydefault.Answer:BC解析：从12c版本开始，就支持在线移动数据库，该题是一种变题，答案换了一种形式。
D答案经过测试是错的，只要使用OMF方式，就不需要指定数据文件路径。Q212.Choosethebestanswer.YourdatabaseinstanceisstartedwithanSPFILE.APFILEisalsoavailable.Youexecutethiscommand:ALTERSYSTEMSETDB_CACHE_SIZE=100K;WhereisthevaluechangedA)intheSPFILEandPFILEB)intheSPFILEandinmemoryC)onlyinmemoryD)onlyintheSPFILEE)intheSPFILE,PFILE,andmemoryAnswer:B解析：该题属于最基本的基础题。
Q213.Choosetwo.WhichtwoOracledatabasespacemanagementfeaturesrequiretheuseoflocallymanagedtablespacesA)Automaticdatafileextension(AUTOEXTEND)B)OnlinesegmentshrinkC)FreespacemanagementwithbitmapsD)OracleManagedFiles(OMF)E)Server-generatedtablespacespacealertsAnswer:BC解析：如果需要在线shrink空间，则需要本地管理的表空间，这是经典的答案，本地管理的表空间使用位图
Q214.Choosethebestanswer.WhichcompressionmethodisrecommendedforDirect-PathInsertoperationsA)COLUMNSTORECOMPRESSADVANCEDB)ROWSTORECOMPRESSBASICC)COLUMNSTORECOMPRESSBASICD)ROWSTORECOMPRESSADVANCEDAnswer:D解析：各种压缩技术优缺点：基本表压缩适合directpath插入和受限数据类型sql高级行压缩支持所有的数据类型和所有sql混合列压缩适合不频繁的update归档压缩仅适合directpathinserted，传统的插入和更新也是支持的，不过得使用ADO（automaticdataoptimization)策略移动行达到要求的混合列压缩级别高级行压缩是理想压缩方式，其他级别都是冷门压缩）（1）可以将insert数据跳过buffer_cahce，省掉了bufferblock的格式化与DBWR操作，直接从PGA写入磁盘（2）不检查表中现有的block是否有空闲空间，直接在表的高水位线（HWM）以上插入（3）如果在数据库处于非归档模式下，或者是数据就处于归档模式，表级处于nologging状态下，只有少量的空间信息redo写入、不写入数据undo（因为要回滚时可以直接回退
A)DB_32K_CACHE_SIZEmustbesettoavaluethatcanbeaccommodatedintheSGA.B)DB_32K_CACHE_SIZEmustbelessthanDB_CACHE_SIZE.C)DB_32K_CACHE_SIZEshouldbesettoavaluegreaterthanDB_CACHE_SIZE.D)Theoperatingsystemmustusea32kblocksiE)The/u02filesystemmusthaveatleast100gspaceforthedatafile.F)DB_CACHE_SIZEmustbesettoasizethatissmallerthanDB_32K_CACHE_SIZE.Answer:AE解析：非标准块表空间创建之前首先要分配内存空间。
Q223.Choosethree.WhichthreestatementsaretrueabouttheAutomaticDiagnosticRepository(ADR)A)Itisafile-basedrepositoryheldoutsideanydatabase.B)ItisheldinsideanOracledatabaseschema.C)TheADRbaseisspecifiedintheDIAGNOSTIC_DESTdatabaseparameter.D)ItisonlyusedforOracleDatabasediagnosticinformation.E)Itcanbeusedforproblemdiagnosisofadatabasewhenthatdatabase'sinstanceisdown.Answer:ACE解析：ADR的数据是存放在数据库外面的，无需数据库启动就能够诊断；C答案是经典答案
Q224.Choosetwo.WhichtwostatementsaretrueaboutUserAuthenticationinanOracleDatabase
A)PasswordFileauthenticationissupportedforanytypeofdatabaseuser.B)REMOTE_LOGIN_PASSWORDFILEmustbesettoexclusivetopermitpasswordchangesforsystem-privilegedadministrativeusers.C)OperatingSystemauthenticationmaybeusedforsystem-privilegedadministrativeusers.D)Passwordauthenticationmustbeusedforsystemprivilegedadministrativeusers.E)PasswordFileauthenticationmustbeusedforsystem-privilegedadministrativeusers.Answer:BC解析：密码文件就是给那些有特权用户使用的，但是通过操作系统验证方式也支持。
Q225.Choosethebestanswer.ExaminethedescriptionoftheSALES1table:NameNullType----------------------------------------------------------------------SALES_IDSTORE_IDITEMS_IDQUANTITYSALES_DATESALES2isatablewiththesamedescriptionasSALES1.Somesalesdataiscontainederroneouslyinbothtables.YoumustdisplayrowsfromSALES1andSALES2andwishtoseetheduplicatestoo.WhichsetoperatorgeneratestherequiredoutputA)SUBTRACTB)UNIONALLC)INTERSECTD)UNIONE)MINUSAnswer:B解析：能够看到重复的行，选择答案BQ226.Choosethree.WhichthreeresourcelimitscanbeconfiguredusingOracleprofilesA)limitingconnecttimeforusersessionsB)limitingCPUusedbyauserC)limitingidletimeforusersessionsonlywhenblockingothersessionsD)limitingidletimeforusersessionsonlywhennotblockingothersessionsE)limitingidletimeforusersessionsF)limitingCPUusedbyasessionAnswer:AEF解析：这是有关profile的比较简单的问法
Q227.Choosetwo.WhichtwostatementsaretrueaboutunexpiredundoA)ItisrequiredwhenatransactionissuesaROLLBACKstatement.B)ItisthefirsttypeofUNDOoverwrittenwhentryingtoavoidout-of-spaceerrors.C)Itmaybeusedtosupportflashbackqueries.D)ItisretainedifpossibleforthetimespecifiedinUNDO_RETENTION.E)ItconsistsofUNDOonlyforuncommittedtransactions.Answer:CD解析：该题的重点是要区分活动的和非活动的undo，unexpired是属于非活动的一种。Q228.Choosethree.WhichthreearebenefitsofusingtempUNDOwhenperformingDMLonglobaltemporarytablesA)ItreducesI/OstotheSYSTEMtablespace.B)Itreducestheamountofredogenerated.C)ItreducestheamountofUNDOstoredintheUNDOtablespace.D)ItpermitsDMLonGlobaltemporarytablesevenifthedatabaseisopenedreadonly.E)ItreducesI/OstotheSYSAUXtablespace.Answer:BCD解析：答案BC是容易判断的，但是答案D描述的不太完整，这个操作只能允许在物理standby数据库上操作，如果是主库只读打开，是不允许进行dml操作，这个可能也为备库进行dml测试提供一个方便。
Q229.Choosetwo.YoustartyourdatabaseinstanceinNOMOUNTstate.WhichtwoactionsareperformedA)Thecontrolfilesareopened.B)MemoryisallocatedfortheSGA.C)YScanaccessthedatabase.D)Allrequiredbackgroundprocessesarestarted.E)Theconsistencyofthedatabaseischecked.Answer:BDQ230.Choosethree.WhichthreestatementsaretrueaboutUNDOandREDOA)UNDOisusedforsomeflashbackoperations.B)BothREDOandUNDOcanbemultiplexed.C)UNDOisusedforreadconsistency.D)REDOisusedforROLLBACK.E)REDOisusedforreadconsistency.F)REDOisusedforinstancerecovery.Answer:ACF解析：有关undo和redo的考点概念比较简单。Q231.Choosethree.WhichthreestatementsaretrueaboutResumableSpaceAllocationinOracledatabases
Q233.Choosethebestanswer.WhywouldyouusetheTNSPINGhrcommandA)toverifyifarequestedserviceisavailableB)todetermineifthelistenerfortheHRnetservicenamecanbereachedC)toverifyiftheHRdatabaseinstanceisupD)tocheckandlistmultipleORACLE_HOMElocationsontheserverE)tovalidatethenetworkconnectivitybetweenaclientandthedatabaseinstanceAnswer:B解析：AB两个答案都对，但是B答案描述更加完整Q234.Choosetwo.WhichtwostatementsaretrueabouttheUNLIMITEDTABLESPACEsystemprivilegeandspacequotasA)Itisoverriddenbyaspacequotaspecifiedfortheuser.B)Itallowsausertohaveunlimitedspaceinanytablespaceinthedatabase.C)Bydefault,usershavenoquotaontheirdefaultpermanenttablespace.D)Itallowsausertohaveunlimitedspaceonlyintheirdefaultpermanenttablespace.E)Itallowsaroletohaveunlimitedspaceinanytablespaceinthedatabase.Answer:BE解析：unlimitedtablespace的权限覆盖分配的quota，可以把给权限分配给用户和角色。
Q246.WhichfouraccountmanagementcapabilitiescanbeconfiguredusingOracleprofiles(Choosefour.)A.thenumberofhoursforwhichanaccountislockedaftertheconfigurednumberofloginattemptshasbeenreachedB.thenumberofdaysforwhichanaccountmaybeinactivebeforeitislockedC.themaximumamountofCPUtimeallowedforauser’ssessionsbeforetheiraccountislockedD.theabilitytopreventapasswordfromeverbeingreused
E.thenumberofpasswordchangesrequiredwithinaperiodoftimebeforeapasswordcanbereusedF.thenumberofdaysforwhichanaccountislockedaftertheconfigurednumberofloginattemptshasbeenreachedG.themaximumnumberofsessionspermittedforauserbeforetheaccountislockedAnswer:BDEF解析：使用profile可以实现密码安全的管理，以及其它的资源管理，这是个很重要的考点方向。
Answer:ACE解析：有个profile的考点一直是ocp考试中的内容，概念简单明了。但是题目中把RESOURCE_LIMIT设置成false，意味着资源管理不生效，那么SESSIONS_PER_USER和IDLE_TIME参数属于kenerl类型，所以设置无法生效，该题带有一定的隐秘性。）1*selectPROFILE,RESOURCE_NAME,RESOURCE_TYPE,limitfromdba_profilesorderbyPROFILE;PROFILERESOURCE_NAMERESOURCE_TLIMIT
Q254.Whichtwostatementsaretrueregardingsinglerowfunctions(Choosetwo.)A.MOD:returnsthequotientofadivision.B.TRUNC:canbeusedwithNUMBERandDATEvalues.C.CONCAT:canbeusedtocombineanynumberofvalues.D.SYSDATE:returnsthedatabaseservercurrentdateandtime.E.INSTR:canbeusedtofindonlythefirstoccurrenceofacharacterinastring.F.TRIM:canbeusedtoremovealltheoccurrencesofacharacterfromastring.Answer:BD解析：了解各个函数的功能A：返回除法的商C：可用于组合任意数量的值E:只能用于查找字符串中第一个出现的字符F：可用于从字符串中删除所有出现的字符Q255.YouwanttousetablecompressionsuitableforOLTPthatwill:1.CompressrowsforallDMLstatementsonthattable2.Minimizetheoverheadsassociatedwithcompression
Q257.WhichtwostatementsaretrueaboutthePMONbackgroundprocess(Choosetwo.)A)ItregistersdatabaseserviceswithalllocalandremotelistenersknowntothedatabaseinstanceB)ItfreesresourcesheldbyabnormallyterminatedprocessesC)ItrecordscheckpointinformationinthecontrolfileD)ItfreesunusedtemporarysegmentsE)ItkillssessionsthatexceedidletimeAnswer:BE解析：从11g开始，实例注册到监听的事情就不是由pmon负责了，B答案是很肯定的，E答案没有明确的文档描述Q258.TheEMPLOYEEStablecontainscolumnsEMP_IDofdatatypeNUMBERandHIRE_DATEofdatatypeDATE.YouwanttodisplaythedateofthefirstMondayafterthecompletionofsixmonthssincehiring.TheNLS_TERRITORYparameterissettoAMERICAinthesessionand,therefore,Sundayisthefirstdayonthewee.Whichquerycanbeused
SALES1.Somesalesdataisduplicatedinbothtables.YouwanttodisplaytherowsfromtheSALES1tablewhicharenotpresentintheSALES2table.WhichsetoperatorgeneratestherequiredoutputA)INTERSECTB)UNIONALLC)UNIOND)SUBTRACTE)MINUSAnswer:E解析：题目要求取两表间的差异，所以用MINUS操作Q262.YourdatabaseinstanceisstartedwithaPFILE.Examinetheseparameters:Youwanttoincreasethesizeofthebuffercache.Freememoryisavailabletoincreasethesizeofthebuffercache.Youexecutethecommand:SQL>ALTERSYSTEMSETDB_CACHE_SIZE=1024M;WhatistheoutcomeA)hevalueischangedonlyinthePFILEandtakeseffectatthenextinstancestartupB)ThevalueischangedforthecurrentinstanceandinthePFILEC)ItfailsbecausetheSCOPEclauseismissingD)Changeisappliedtothecurrentinstance,butdoesnotpersistafterinstancerestartAnswer:D解析：这道题是传统的考点，考的是内存修改的生效范围，如果是pfile文件，则不会动态修改，所以下一次启动还是原来的值。
Q263.Whichthreestatementsaretrueaboutpfiles,spfilesorbothA)Allspfileparameterscanbemodifiedsuccessfullywiththescope=bothclause.B)Aspfilecanbecreatedbyanidleinstance.C)spfilesandpfilesmaybothbeeditedwithanO/Seditingutility.D)Somespfileparameterscanbemodifiedsuccessfullywiththescope=memoryclause.E)Apfilecanbecreatedbyanidleinstance.F)Allspfileparameterscanbemodifiedsuccessfullywiththescope=memoryclause.Answer:BDE解析：spfile和pfile可以互相转换，而且不需要实例启动，参数分为静态参数和动态参数，只有动态参数才能够马上修改生效。
Q264.WhichfourstatementsaretrueaboutdatabaseinstancebehaviorA)Redologfilescanberenamedinmountstate.B)Datafilescanberenamedinmountstate.C)Alldynamicperformanceviews(v$views)returndatawhenqueriedfromasessionconnectedtoanInstanceinnomountstate.D)Anspfilecanbeupdatedwhenconnectedtoanidleinstance.E)Theconsistencyofredologsanddatafilesischeckedwhenmountingthedatabase.
F)Anidleinstanceiscreatedwhenastartupnomountissuccessful.Answer:ABDF解析：答案AB是没有问题的，但是这道题的anidleinstance指的是什么不明确，如果一个实例没有启动，那么我们连接的时候会告诉我们：Connectedtoanidleinstance.，所以这里不清楚实例启动后是不是算是idleinstance，但是从另外一个角度，如果只是启动实例，没有跟数据库扯上关系，那么也算是空闲实例。
Q265.Yourdatabaseinstanceisstartedbyusingaserverparameterfile(SPFILE).YouexecutethefollowingcommandtochangethevalueoftheLOG_BUFFERinitializationparameter:ALTERSYSTEMSETLOG_BUFFER=32M;WhatistheoutcomeofthiscommandA)TheparametervalueischangedanditcomesintoeffectassoonasspacebecomesavailableintheSGA.B)Theparametervalueischangedanditcomesintoeffectatthenextinstancestartup.C)ItreturnsanerrorbecauseSCOPEshouldbesettoMEMORY.D)Itreturnsanerrorbecausethevalueofthisparametercannotbechangeddynamically.Answer:D解析：本考点考的是哪些参数是静态参数。
Q267.Choosethebestanswer.Examinethisscenario:connect/assysdbaConnected.SHUTDOWNTRANSACTIONALDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.STARTUPNowexaminethesesteps:1.Oracleinstanceisstarted.
2.Thecontrolfileisread.3.Instancerecoveryisstarted.4.Datafilesandredologfilesareopened.5.Thespfileorpfileisread.6.Thedatabaseisavailableforuse.7.Thedatabaseismounted.8.Instancerecoveryiscompleted.Identifythecorrectstepsandtheordertheywillbeexecutedinfollowingthiscommand:A)5,2,1,7,4,6B)5,1,2,7,4,3,8,6C)5,2,1,4,7,6D)5,1,2,7,4,3,6,8E)5,1,2,7,4,6F)5,1,3,2,7,4,8,6Answer:E解析：本题考点是数据库正常关闭后的启动顺序，概念比较简单。
Q272.Inoneofyourdatabases,youcreateauser,HR,andthenexecutethiscommand:GRANTCREATESESSIONTOhrWITHADMINOPTION;WhichthreeactionscanHRperform(Choosethree.)A)RevoketheCREATESESSIONprivilegefromotherusersB)RevoketheCREATESESSIONprivilegefromuserHRC)LogintothedatabaseinstanceD)GranttheCREATESESSIONprivilegewithADMINOPTIONtootherusersE)ExecuteDDLstatementsintheHRschemaF)ExecuteDMLstatementsintheHRschemaAnswer:ABD解析：这道题考的是WITHADMINOPTION的权限，实验证明拥有该权限的可以把其它用户、
或者自己、以及sys用户的CREATESESSION权限给撤除，没有深入的研究，很难选择正确答案
Q273.WhichtwostatementsaretrueabouttheWHEREandHAVINGclausesinaSELECTstatement(Choosetwo.)A)AggregatingfunctionsandcolumnsusedinHAVINGclausesmustbespecifiedintheSELECTlistofaqueryB)WHEREandHAVINGclausescanbeusedinthesamestatementonlyifappliedtodifferenttablecolumnsC)TheHAVINGclausecanbeusedwithaggregatingfunctionsinsubqueriesD)TheWHEREclausecanbeusedtoexcluderowsbeforedividingthemintogroupsE)TheWHEREclausecanbeusedtoexcluderowsafterdividingthemintogroupsAnswer:CD解析：having子句就是用在条件中有聚组函数；where子句会在数据输入到聚组函数处理前先过滤数据，以此减少数据的处理量
C)ItdisplaystheNOTNULLconstraintforanycolumnsthathavethatconstraintD)ItcanbeusedtodisplaythestructureofanexistingviewE)ItdisplaysallconstraintsthataredefinedforeachcolumnF)ItcanbeusedonlyfromSQL*PlusAnswer:BCD解析：desc命令可以显示表和视图的结构，包含notnull约束，而且可以在sql*plus和developer上显示Q277.ExaminethedescriptionoftheCUSTOMERStable:
Forcustomerswhoseincomelevelhasavalue,youwanttodisplaythefirstnameanddueamountas5%oftheircreditlimit.Customerswhosedueamountisnullshouldnotbedisplayed.WhichqueryshouldbeusedA)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_incoms_levelISNOTNULLANDdue_amountISNOTNULL;B)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level!=NULLANDcust_credit_level!=NULL;C)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level<>NULLANDdue_amount<>NULL;D)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_level!=NULLANDdue_amount!=NULL;E)SELECTcust_first_name,cust_credit_limit*.05ASDUE_AMOUNTFROMcustomersWHEREcust_income_levelISNOTNULLANDcust_credit_limitISNOTNULL;Answer:AE解析：题目要求对于收入水平有值的客户，您希望将名字和到期金额显示为其信用额度的5%。不应显示到期金额为空的客户。别名不能当作条件列，所以a答案错误，但是可以当作排序列
Q278.Choosetwo.WhichtwostatementsaretrueaboutEnterpriseManagerDatabaseExpressA)ThesameportnumbercanbeusedforDatabaseExpressconfigurationsfordatabasesondifferenthosts.B)ItcanbeusedtoswitchadatabaseintoARCHIVELOGMODE.C)Itcanbeusedtoperformdatabaserecoverywhenthedatabaseismounted.D)ThesameportnumbercanbeusedformultipleDatabaseExpressconfigurationsformultipledatabasesonthesamehost.E)Itcanbeusedtoperformdatabaserecoverywhenthedatabaseisopened.F)Itisavailableonlywhenthedatabaseisopen.Answer:AF
解析：12c的EMDE就是一个阉割版的oem，很多功能都没有，但是使用起来很方便，只能做为简单的监视作用。
Q279.Choosetwo.ExaminethedescriptionoftheEMPLOYEEStable:NameNullType------------------------------------------------------------------EMPLOYEE_IDNOTNULLNUMBER(3)FIRST_NAMENOTNULLVARCHAR2(15)LAST_NAMEVARCHAR2(15)SALARYNUMBER(6,2)WhichtwoquerieswillresultinanerrorA)SELECTfirst_namelast_nameFROMemployees;B)SELECTfirst_name,last_nameFROMemployees;C)SELECTlast_name,12*salaryASannual_salaryFROMemployeesWHEREannual_salary>100000ORDERBY12*salary;D)SELECTlast_name,12*salaryASannual_salaryFROMemployeesWHEREannual_salary>100000ORDERBYannual_salary;E)SELECTlast_name,12*salaryASannual_salaryFROMemployeesWHERE12*salary>100000ORDERBYannual_salary;F)SELECTlast_name,12*salaryASannual_salaryFROMemployeesWHERE12*salary>100000ORDERBY12*salary;Answer:CD解析：注意别名不能当作条件列，但是可以用在排序上。
Q280.AuserestablishesaconnectiontoadatabaseinstancebyusinganOracleNetconnection.Youwanttoensurethefollowing:1.Theuseraccountmustbelockedafterfiveunsuccessfulloginattempts.2.Datareadpersessionmustbelimitedfortheuser.3.Theusercannothavemorethanthreesimultaneoussessions.4.Theusermusthaveamaximumof10minutessessionidletimebeforebeingloggedoffautomatically.HowwouldyouaccomplishthisA.bygrantingasecureapplicationroletotheuserB.byimplementingDatabaseResourceManagerC.byusingOracleLabelSecurityoptionsD.byassigningaprofiletotheuserAnswer:D解析：这里要弄清楚profile和资源管理器的内容。Q281.WhichthreestatementsaretureaboutadedicatedserverconfigurationA)Adedicatedserverprocesscanbespawnedbythelistenerwhenusinglocalclients.B)Adedicatedserverprocesscanbespawnedwithoutalistenerwhenusinglocalclients.
285.Evaluatethesecommandswhichexecutesuccessfully:
WhichtwostatementsaretrueabouttheORD_ITEMStableandtheORD_SEQsequence(Choosetwo.)A.IfsequenceORD_SEQisdroppedthenthedefaultvalueforcolumnORD_NOwillbeNULLforrowsinsertedintoORD_ITEMSB.AnyuserinsertingrowsintotableORD_ITEMSmusthavebeengrantedaccesstosequenceORD_SEQC.ColumnORD_NOgetsthenextnumberfromsequenceORD_SEQwheneverarowisinsertedintoORD_ITEMSandnoexplicitvalueisgivenforORD_NOD.SequenceORD_SEQcyclesbackto1afterevery5000numbersandcancycle20timesE.SequenceORD_SEQisguaranteednottogenerateduplicatenumbersAnswer:BC解析：定义表时列的默认值是来自于序列，所以insert的时候即使没有显式插入该列，也会自动插入该列的值，如果其它用户想插入该列的值，则需要有访问该序列的权限。
新增加题：286.Choosethebestanswer.Examinethisschemainformation:1.EMPLOYEES.DEPARTMENT_IDhasaforeignkeyreferencingDEPARTMENTS.DEPARTMENT_ID.2.EMP_VIEWisbasedontheEMPLOYEESandDEPARTMENTStables.3.EMP_VIEWhascolumnsEMPLOYEE_ID,EMPLOYEE_NAMEandDEPARTMENT_NAME.Youmustaddanewcolumn,MANAGER_ID,fromtheEMPLOYEEStable,totheview,showingeachemployeesmanager.WhichstatementwilldothisA)CREATEORREPLACEVIEWemp_viewASSELECTemployee_id,employee_name,department_name,manager_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;B)ALTERVIEWemp_viewADD(employee.manager_id);
C)ALTERVIEWemp_viewMODIFY(SELECTemployee_id,employee_name,department_name,manager_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id);D)ALTERVIEWemp_viewADD(SELECTmanager_idFROMemployees);Answer：A解析：视图不能进行alter，只能重建287.Choosetwo.ExaminethedescriptionoftheCUSTOMERStable:CUSTOMER_IDCUSTOMER_NAME--------------------------------------------10MARK20Mandy30Mary40MARVIN20
WhichtwoSELECTstatementswillreturntheseresults:CUSTOMER_NAME-------------------------MandyMaryA)SELECTcustomer_nameFROMcustomersWHEREcustomer_nameLIKEMa*;B)SELECTcustomer_nameFROMcustomersWHEREcustomer_nameLIKE%a%;C)SELECTcustomer_nameFROMcustomersWHEREUPPER(customer_name)LIKEMA%;D)SELECTcustomer_nameFROMcustomersWHEREcustomer_nameLIKE*Ma*;E)SELECTcustomer_nameFROMcustomersWHEREcustomer_nameLIKEMa%;F)SELECTcustomer_nameFROMcustomersWHEREUPPER(customer_name)LIKEMA*;G)SELECTcustomer_nameFROMcustomersWHEREcustomer_name=*Ma*;Answer：BE解析：注意通配符的用法
287.Choosethebestanswer.YouneedtoallowuserANDREWto:1.ModifytheTITLEandADDRESScolumnsofyourCUSTOMERStable.2.GRANTthatpermissiontootherusers.WhichstatementwilldothisA)GRANTUPDATEONcustomers.title,customers.addressTOandrew;B)GRANTUPDATE(title,address)ONcustomersTOandrewWITHGRANTOPTION;C)GRANTUPDATEONcustomers.title,customers.addressTOandrewWITHADMINOPTION;D)GRANTUPDATEONcustomers.title,customers.addressTOandrewWITHGRANTOPTION;E)GRANTUPDATE(title,address)ONcustomersTOandrew;F)GRANTUPDATE(title,address)ONcustomersTOandrewWITHADMINOPTION
289.Choosetwo.WhichtwoaretrueaboutconstraintsA)Allconstraintscanbedefinedatthetableorcolumnlevel.B)Aconstraintcanbedisabledeveniftheconstrainedcolumncontainsdata.C)AcolumnwithaFOREIGNKEYconstraintcannevercontainaNULLvalue.D)AcolumnwithaUNIQUEconstraintcancontainaNULLvalue.E)ConstraintsareenforcedonlyduringINSERToperations.Answer：BD290.Choosethree.WhichthreearetrueaboutprivilegesA)Allschemaobjectshaveassociatedobjectprivileges.B)Schemaownerscangrantanyobjectprivilegeonanyobjectintheirschematoanyotheruserorrole.C)Acombinationofobjectandsystemprivilegescanbegrantedtoarole.D)Objectprivilegesgrantedonatableautomaticallyapplytoallsynonymsforthattable.E)OnlyuserswiththeGRANTANYPRIVILEGEprivilegecangrantandrevokesystemprivilegesfromotherusers.F)OnlyuserswiththeDBArolecancreateroles.Answer：ABD解析：
322.Youmustdetermineifanycustomers'detailshavebeenenteredmorethanonceusinga