大型数据库技术：Oracle基础

Oracle基础数据库项目失败的最常见原因之一是：对数据库的实际认识不足，缺乏对所用基本工具的了解了解你的数据库，掌握它是怎样工作的，弄清楚它能为你做什么，并且最大限度地加以利用建议阅读入门资料DBA手册OracleConcept盖国强，循序渐进Oracle数据库管理、优化与备份恢复进阶资料ThomasKyte，Oracle9i&10g编程艺术：深入数据库体系结构ThomasKyte，Oracle专家高级编程ThomasKyte，Oracle高效设计在线资源OracleTechnologyNetworkCNOUGselectdeptno,ename,sal,sum(sal)over(partitionbydeptnoorderbysal,ename)cum_sal,round(100*ratio_to_report(sal)over(partitionbydeptno),1)pct_dept,round(100*ratio_to_report(sal)over(),1)pct_over_allfromemporderbydeptno,sal;分析函数Oracle体系结构PasswordfileInstanceSGARedoLog

BufferSharedPoolDataDictionary

CacheLibraryCacheDBWRSMONPMONCKPTLGWROthersUser

processServer

processPGAControlfilesDatafilesDatabaseDatabase

BufferCacheRedoLogfilesJavaPoolLargePoolParameterfileArchivedLogfilesOracleServerAnOracleserverIsadatabasemanagementsystemthatprovidesanopen,comprehensive,integratedapproachtoinformationmanagementConsistsofanOracleinstanceandanOracledatabaseOracleServerOracleInstanceAnOracleinstanceIsameanstoaccessanOracledatabaseAlwaysopensoneandonlyonedatabaseConsistsofmemoryandprocessstructuresBackgroundstructuresMemorystructuresSGARedolog

buffercacheDatabase

buffercacheSharedpoolDBWRSMONPMONCKPTLGWROthersDataDictionary

cacheLibrarycacheInstanceEstablishingaConnection

andCreatingaSessionConnectingtoanOracleinstanceconsistsofestablishingauserconnectionandcreatingasessionConnectionestablishedSessioncreatedDatabaseuserUser

processOracleserverServer

processOracleDatabaseAnOracledatabaseIsacollectionofdatathatistreatedasaunitConsistsofthreefiletypesControlfilesDatafiles

ArchivedlogfilesParameterfilePasswordfileRedologfilesOracleDatabasePhysicalStructureThephysicalstructureofanOracledatabaseisdeterminedbytheoperatingsystemfilesthatprovidetheactualphysicalstoragefordatabaseinformation.ControlfilesDatafilesRedologfilesControlfilesDatafiles(includesdatadictionary)

HeaderOnlineredologfilesMemoryStructureOracle’smemorystructureconsistsoftwomemoryareasknownasSystemGlobalArea(SGA):Allocatedatinstancestartup,andisafundamentalcomponentofanOracleInstanceProgramGlobalArea(PGA):AllocatedwhentheserverprocessisstartedSystemGlobalArea(SGA)TheSGAconsistsofseveralmemorystructures:SharedpoolDatabasebuffercacheRedologbufferOtherstructures(e.g.lockandlatchmanagement,statisticaldata)TherearetwooptionalmemorystructuresthatcanbeconfiguredwithintheSGA:LargepoolJavapoolSystemGlobalArea(SGA)SGAisdynamicandsizedusingSGA_MAX_SIZE.SGAmemoryallocatedandtrackedingranulesbySGAcomponentsContiguousvirtualmemoryallocationSizebasedonSGA_MAX_SIZESharedPoolThesharedpoolisusedtostorethemostrecentlyexecutedSQLstatementsandthemostrecentlyuseddatadefinitions.Itconsistsoftwokeyperformance-relatedmemorystructures:LibrarycacheDatadictionarycacheSizedbytheparameterSHARED_POOL_SIZE.SharedpoolDatadictionarycacheLibrarycacheALTERSYSTEMSETSHARED_POOL_SIZE=64M;LibraryCacheThelibrarycachestoresinformationaboutthemostrecentlyusedSQLandPL/SQLstatements.Thelibrarycache:EnablesthesharingofcommonlyusedstatementsIsmanagedbyaleastrecentlyused(LRU)algorithmConsistsoftwostructures:SharedSQLareaSharedPL/SQLareaHasitssizedeterminedbythesharedpoolsizingDataDictionaryCacheThedatadictionarycacheisacollectionofthemostrecentlyuseddefinitionsinthedatabase.Itincludesinformationaboutdatabasefiles,tables,indexes,columns,users,privileges,andotherdatabaseobjects.Duringtheparsephase,theserverprocesslooksatthedatadictionaryforinformationtoresolveobjectnamesandvalidateaccess.Cachingthedatadictionaryinformationintomemoryimprovesresponsetimeonqueries.Sizeisdeterminedbythesharedpoolsizing.DatabaseBufferCacheThedatabasebuffercachestorescopiesofdatablocksthathavebeenretrievedfromthedatafiles.Itenablesgreatperformancegainswhenyouobtainandupdatedata.Itismanagedthroughaleastrecentlyused(LRU)algorithm.DB_BLOCK_SIZE

determinestheprimaryblocksize.Databasebuffer

cacheDatabaseBufferCacheConsistsofindependentsub-caches:DB_CACHE_SIZEDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDatabasebuffercachecanbedynamicallyresizedtogroworshrinkusingALTERSYSTEM.DB_CACHE_ADVICEcanbesettogatherstatisticsforpredictingdifferentcachesizebehavior.ALTERSYSTEMSETDB_CACHE_SIZE=96M;RedoLogBufferCacheTheredologbuffercacherecordsallchangesmadetothedatabasedatablocks.Itsprimarypurposeisrecovery.Changesrecordedwithinarecalledredoentries.Redoentriescontaininformationtoreconstructorredochanges.SizeisdefinedbyLOG_BUFFER.Redolog

buffercacheLargePoolThelargepoolisanoptionalareaofmemoryintheSGAconfiguredonlyinasharedserverenvironment.Itrelievestheburdenplacedonthesharedpool.Thisconfiguredmemoryareaisusedforsessionmemory(UGA),I/Oslaves,andbackupandrestoreoperations.Unlikethesharedpool,thelargepooldoesnotuseanLRUlist.SizedbyLARGE_POOL_SIZE.ALTERSYSTEMSETLARGE_POOL_SIZE=64M;JavaPoolTheJavapoolservicestheparsingrequirementsforJavacommands.RequiredifinstallingandusingJava.ItisstoredmuchthesamewayasPL/SQLindatabasetables.ItissizedbytheJAVA_POOL_SIZEparameter.User

processProgramGlobalArea(PGA)ThePGAismemoryreservedforeachuserprocessthatconnectstoanOracledatabase.StackspaceSessioninformationsortarea,cursorinformationSGASharedSQLareasSGASessioninformation

PGADedicatedserverSharedserverServer

processSharedSQLareasStackspacesortarea,cursorinformationProcessStructureAnOracleprocessisaprogramthatdependingonitstypecanrequestinformation,executeaseriesofsteps,orperformaspecifictask.Oracletakesadvantageofvarioustypesofprocesses:Userprocess:StartedatthetimeadatabaseuserrequestsconnectiontotheOracleserverServerprocess:ConnectstotheOracleInstanceandisstartedwhenauserestablishesasession.Backgroundprocess:AvailablewhenanOracleinstanceisstartedUserProcessAuserprocessisaprogramthatrequestsinteractionwiththeOracleserver.Itmustfirstestablishaconnection.ItdoesnotinteractdirectlywiththeOracleserver.DatabaseuserServer

processUser

processConnectionestablishedServerProcessAserverprocessisaprogramthatdirectlyinteractswiththeOracleserverItfulfillscallsgeneratedandreturnsresults.Canbededicatedorsharedserver.ConnectionestablishedSessioncreatedDatabaseuserUser

processOracleserverServer

processBackgroundProcessesTherelationshipbetweenthephysicalandmemorystructuresismaintainedandenforcedbyOracle’sbackgroundprocessesMandatorybackgroundprocessesDBWn PMON CKPTLGWR SMON RECOOptionalbackgroundprocessesARCn LMON SnnnQMNn LMDn CJQ0 PnnnLCKn DnnnDatabaseWriter(DBWn)DBWnwriteswhen:CheckpointDirtybuffersthresholdreachedNofreebuffersTimeoutRACpingrequestTablespaceofflineTablespacereadonlyTableDROPorTRUNCATETablespaceBEGINBACKUPInstanceSGADatabasebuffer

cacheDBWnControlfilesDatafilesRedologfilesDatabaseLogWriter(LGWR)LGWRwrites:AtcommitWhenone-thirdfullWhenthereis1MBofredoEvery3secondsBeforeDBWnwritesInstanceSGADBWnRedologbufferControlfilesDatafilesRedologfilesLGWRDatabaseSystemMonitor(SMON)Responsibilities:Instancerecovery:RollsforwardchangesintheredologsOpensthedatabaseforuseraccessRollsbackuncommittedtransactionsCoalescesfreespaceever3secDeallocatestemporarysegmentsInstanceSGASMONControlfilesDatafilesRedologfilesInstanceSGASMONDatabaseProcessMonitor(PMON)CleansupafterfailedprocessesbyRollingbackthetransactionReleasinglocksReleasingotherresourcesRestartsdeaddispatchersInstanceSGAPMONPGAareaCheckpoint(CKPT)Responsiblefor:SignallingDBWnatcheckpointsUpdatingdatafileheaderswithcheckpointinformationUpdatingcontrolfileswithcheckpointinformationInstanceSGADWW0RedoLogBufferLGWRInstanceSGADBWnLGWRCKPTControlfilesDatafilesRedologfilesAneventcalledacheckpointoccurswhentheOraclebackgroundprocessDBWnwritesallthemodifieddatabasebuffersintheSGA,includingbothcommittedanduncommitteddata,tothedatafiles.Checkpointsareimplementedforthefollowingreasons:Checkpointsensurethatdatablocksinmemorythatchangefrequentlyarewrittentodatafilesregularly.BecauseoftheleastrecentlyusedalgorithmofDBWn,adatablockthatchangesfrequentlymightneverqualifyastheleastrecentlyusedblockandthusmightneverbewrittentodiskifcheckpointsdidnotoccur.Checkpoint(CKPT)Becausealldatabasechangesuptothecheckpointhavebeenrecordedinthedatafiles,redologentriesbeforethecheckpointnolongerneedtobeappliedtothedatafilesifinstancerecoveryisrequired.Therefore,checkpointsareusefulbecausetheycanexpediteinstancerecovery.Checkpoint(CKPT)Archiver(ARCn)OptionalbackgroundprocessAutomaticallyarchivesonlineredologswhenARCHIVELOGmodeissetPreservestherecordofallchangesmadetothedatabaseControlfilesDatafilesRedologfilesArchivedRedologfilesARCnLogicalStructureThelogicalstructureoftheOraclearchitecturedictateshowthephysicalspaceofadatabaseistobeused.Ahierarchyexistsinthisstructurethatconsistsoftablespaces,segments,extents,andblocks.TablespaceDatafileSegmentBlocksExtentSegment表空间数据库由称为表空间的逻辑单位组成保留相关数据库对象的组Oracle数据库中的典型表空间包括SYSTEM表空间DATA表空间USER表空间TOOLS表空间TEMP表空间数据库的控制空间分配（例如表和索引）为数据库用户设置空间配额备份或恢复数据表空间段表空间中存储在数据库空间分配中的逻辑单位称为段定义为分配给逻辑数据库结构的扩展区集合不同类型的段数据段索引段回滚段临时段扩展区扩展区的集合由一定数目的相邻数据块和段组成增量扩展区是与上次该段中分配的扩展区大小相同或更大的后续扩展区Oracle使用独立的算法搜索整个自由空间查找第一个具有与增量扩展区具有相同的或更大大小的自由、临近数据块集合扩展区一旦Oracle在表空间中找到并分配了必需的可用空间，则它将分配一部分与新增扩展区大小相对应的可用空间Oracle更新段标题和数据词典，以显示新的扩展区已经被分配而且分配的空间不再可用数据块代表数据库存储的最佳粒度级别DatabaseSchemaSchemaObjectsTablesTriggersConstraintsIndexesViewsSequencesStoredprogramunitsSynonymsUser-defineddatatypesDatabaselinksAschemaisanamedcollectionofobjectsAuseriscreated,andacorrespondingschemaiscreatedUsercanbeassociatedonlywithoneschemaUsernameandschemaareoftenusedinterchangeablyOracle的语句处理DDLDML大多数DML都包含了Queryupdateempsetename='KingSley'whereename='King';QueryQuery和DML在Oracle的执行阶段非常类似Query返回结果集，DML修改结果集中的值Oracle的语句处理解析对提交的语句进行语法和语义检查优化生成一个可在Oracle中用来执行语句的最佳计划行资源生成为会话取得最佳计划和建立执行计划执行完成实际执行查询的行资源生成步骤的输出Oracle的语句处理解析(Parsing)：将已经提交的语句分解，判定属于哪种类型，并在其上执行各种检验操作语法检查：正确表述，符合SQL规则？语义分析：正确应用SQL对象？授权？歧义？检查SharedPool：已被其他Session处理过？Oracle的语句处理SharedPoolSGA的一部分，高速缓存以及以前执行过的SQL、PL/SQL、DataDictionary等恰当使用SharedPool是在Oracle中建立可伸缩解决方案的关键Oracle的语句处理硬解析(hardparse)语句通过执行阶段的每一个步骤：从解析到优化、到行资源生成和执行软解析(softparse)语句通过执行阶段的某些步骤，特别是跳过优化步骤（最昂贵的步骤）Oracle的语句处理当Oracle接收到语句后，就会对其进行HASH处理V$SQL动态性能视图生成HASH值后，Oracle在SharedPool中搜索，寻找具有相同HASH值的语句将找到的SQL_TEXT与用户的SQL语句进行比较，确保两者完全相同Oracle的语句处理Oracle确认用户的SQL语句和SharedPool中的相同后，还必须确定两者在语义上相同不同用户可能有相同名称的表验证查询是在相同的环境中解析环境是指能够影响到查询方案生成的所有设置，如SORT_AREA_SIZE、OPTIMIZER_MODE等如果各个查询的优化器模式不同，它们也是不相同的索引存储在常规表中行采用没有特定的次序存储Oracle将获取的名字与ROWID进行关联ROWID是表中行的物理地址，可以告知对象的来源、所处的文件以及文件中特定数据块Oracle的语句处理到目前为止，当Oracle完成了所有工作，并且找到了匹配查询，它就可以从解析过程中返回，报告已经进行了一次软解析如果没有找到匹配查询，就需要进行硬解析索引B树索引(BalencedTree)B-TreeIndexIndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentryBitmapIndex<Blue,10.0.3,12.8.3,1000100100010010100><Green,10.0.3,12.8.3,0001010000100100000><Red,10.0.3,12.8.3,0100000011000001001><Yellow,10.0.3,12.8.3,0010001000001000010>keystartROWIDendROWID

bitmapTableIndexBlock10Block11Block12File3ComparingB-Treeand

BitmapIndexesB-tree Suitableforhigh-cardinality

columnsUpdatesonkeysrelatively

inexpensiveInefficientforqueries

usingORpredicates

UsefulforOLTPBitmapSuitableforlow-cardinality

columnsUpdatestokeycolumnsvery

expensiveEfficientforqueries

usingORpredicates

Usefulfordatawarehousing索引进行FTS(FullTableScan)时，进行批量数据读取如果Oracle认为用户的查询将选取超过全体记录的2%~5%，那么就会趋向使用FTSStartingUpaDatabase

NOMOUNTOPENMOUNTNOMOUNTSHUTDOWNInstance

startedSTARTUPSHUTDOWNStartingUpaDatabase

MOUNTOPENMOUNTNOMOUNTSHUTDOWNControlfileopenedforthisinstanceInstance

startedSTARTUPSHUTDOWNStartingUpaDatabase

OPENOPENMOUNTNOMOUNTSHUTDOWNAllfilesopenedasdescribedbythecontrolfileforthisinstanceControlfileopenedforthisinstanceInstance

startedSTARTUPSHUTDOWNSTARTUPCommandSTARTUPStartuptheinstanceandopenthedatabase:TheALTERDATABASECommandChangethestateofthedatabasefromNOMOUNTtoMOUNT:ALTERDATABASEdb01MOUNT;Openthedatabaseasaread-onlydatabase:ALTERDATABASEdb01OPENREADONLY;OpeningaDatabaseinRestrictedModeUsetheSTARTUPcommandtorestrictaccesstoadatabase:UsetheALTERSYSTEMcommandtoplaceaninstanceinrestrictedmode:STARTUPRESTRICTALTERSYSTEMENABLERESTRICTEDSESSION;

OpeningaDatabaseinRead-OnlyModeAdatabasescanbeopenedasaread-onlydatabase.Aread-onlydatabasecanbeusedto:ExecutequeriesExecutedisksortsusinglocallymanagedtablespacesTakedatafilesofflineandonline,nottablespacesPerformrecoveryofofflinedatafilesandtablespacesShuttingDowntheDatabaseShutdownMode:NORMALTRANSACTIONALIMMEDIATEABORTAxxxxTxxooIxxxoShutdownModeAllownewconnectionsWaituntilcurrentsessionsendWaituntilcurrenttransactionsendForceacheckpointandclosefilesNxoooYESNOxoShutdownOptionsDuringaShutdownNormal,ShutdownTransactionalorShutdownImmediateConsistentDatabase(cleandatabase)Onthewaydown:DatabasebuffercachewrittentothedatafilesUncommittedchangesrolledbackResourcesreleased.Onthewayup:NoinstancerecoveryShutdownOptionsDuringaShutdownAbortorInstanceFailureorStartupForceInconsistentDatabase(dirtydatabase)Onthewaydown:ModifiedbuffersarenotwrittentothedatafilesUncommittedchangesarenotrolledbackOnthewayup:RedologsusedtoreapplychangesUndosegmentsusedtorollbackuncommittedchangesResourcesreleasedDataDictionaryDuringdatabasecreation,theOracleservercreatesadditionalobjectstructureswithinthedatafiles.DatadictionarytablesDynamicperformancetablesControlfilesDatafilesRedologfilesDatabaseDataDictionaryTablesDynamicPerformanceTablesDataDictionaryThedatadictionaryisasetofread-onlytables

andviewsthatrecord,verify,andprovideinformationaboutitsassociateddatabase.DescribesthedatabaseanditsobjectsIncludestwotypesofobjects:BasetablesStoredescriptionofdatabaseCreatedwithCREATEDATABASEDataDictionaryviewsSummarizebasetableinformationCreatedusingcatalog.sqlscriptDataDictionaryContentsThedatadictionaryprovidesinformationabout:LogicalandphysicaldatabasestructureDefinitionsandspaceallocationsofobjectsIntegrityconstraintsUsersRolesPrivilegesAuditingHowtheDataDictionaryIsUsedThedatadictionaryhasthreeprimaryuses:TheOracleserverusesittofindinformationabout:UsersSchemaobjectsStoragestructuresTheOracleservermodifiesitwhenaDDLstatementisexecuted.UsersandDBAscanuseitasaread-onlyreferenceforinformationaboutthedatabase.DataDictionaryViewCategoriesThedatadictionaryconsistsofthreemainsetsofstaticviewsdistinguishedfromeachotherbytheirscope:DBA:WhatisinalltheschemasALL:WhattheusercanaccessUSER:Whatisintheuser'sschemaDBA_xxxUSER_xxxALL_xxxObjectsownedbythecurrentuserObjectsaccessiblebythecurrentuserAlloftheobjectsinthedatabaseDynamicPerformanceTablesDynamicperformanceviewsrecordcurrentdatabaseactivity.ViewsarecontinuallyupdatedwhilethedatabaseisoperationalInformationisaccessedfrom:MemoryControlfileDBAusesdynamicviewstomonitorandtunethedatabaseDynamicviewsareownedbySYS

userDMLisnotallowedQueryingtheDataDictionaryandDynamicPerformanceViewsDatadictionaryanddynamicperformanceviewscanbequeriedforinformation.AlistingofviewsavailablecanberetrievedbyqueryingtheDICTIONARYview.AlistingofthecolumnsanditscontentscanbeaccessedusingDESCRIBEandSELECT.Columncommentsareavailabletoretrievemoreinsightintowhatacolumncontentmeanswithinaparticularview.DataDictionaryExamplesGeneralOverviewDICTIONARY,DICT_COLUMNSSchemaobjectsDBA_TABLES,DBA_INDEXES,DBA_TAB_COLUMNS,DBA_CONSTRAINTSSpaceallocationDBA_SEGMENTS,DBA_EXTENTSDatabasestructureDBA_TABLESPACES,DBA_DATA_FILESManagingPrivilegesTwotypesofOracleuserprivileges:System:EnablesuserstoperformparticularactionsinthedatabaseObject:EnablesuserstoaccessandmanipulateaspecificobjectSystemPrivilegesThereareover100distinctsystemprivilegesTheANYkeywordintheprivilegessignifiesthatusershavetheprivilegeinanyschemaTheGRANTcommandaddsaprivilegetoauseroragroupofusersTheREVOKEcommanddeletestheprivilegesSystemPrivileges:ExamplesCategory Examples

INDEX

CREATEANYINDEX

ALTERANYINDEX

DROPANYINDEX TABLE CREATETABLE

CREATEANYTABLE

ALTERANYTABLE

DROPANYTABLE

SELECTANYTABLE

UPDATEANYTABLE

DELETEANYTABLESESSION CREATESESSION

ALTERSESSION

RESTRICTEDSESSIONTABLESPACE CREATETABLESPACE

ALTERTABLESPACE

DROPTABLESPACE

UNLIMITEDTABLESPACEGrantingSystemPrivilegesGRANTCREATESESSIONTOemi;GRANTCREATESESSIONTOemiWITHADMINOPTION;SYSDBAandSYSOPERPrivilegesCategoryExamplesSYSOPERSTARTUPSHUTDOWNALTERDATABASEOPEN|MOUNTALTERDATABASEBACKUPCONTROLFILETORECOVERDATABASEALTERDATABASEARCHIVELOGSYSDBASYSOPERPRIVILEGESWITHADMINOPTIONCREATEDATABASEALTERDATABASEBEGIN/ENDBACKUPRESTRICTEDSESSEIONRECOVERDATABASEUNTILSystemPrivilegeRestrictionsO7_DICTIONARY_ACCESSIBILITY

parameterControlsrestrictionsonSYSTEMprivilegesIfsettoTRUE,accesstoobjectsinSYSschemaisallowedDefaultisFALSE

EnsuresthatsystemprivilegesthatallowaccesstoanyschemadonotallowaccesstoSYSschemaRevokingSystemPrivilegesREVOKECREATETABLEFROMemi;RevokingSystemPrivileges

WITHADMINOPTIONGRANTREVOKEDBAJeffEmiJeffEmiDBAnocascadingeffectswhenasystemprivilegeisrevoked,regardlessofwhetheritwasgivenWITHADMINOPTIONObjectPrivilegesObjectpriv. Table View Sequence ProcedureALTER Ö ÖÖDELETE Ö ÖEXECUTE ÖINDEX Ö ÖINSERT Ö ÖREFERENCES ÖSELECT Ö Ö Ö UPDATE Ö ÖGrantingObjectPrivilegesGRANTEXECUTEONdbms_outputTOjeff;GRANTUPDATEONemi.customersTOjeffWITHGRANTOPTION;GRANT{object_privilege[(column_list)] [,object_privilege[(column_list)]]... |ALL[PRIVILEGES]}ON [schema.]objectTO {user|role|PUBLIC}[,{user|role|PUBLIC}]... [WITHGRANTOPTION]RevokingObjectPrivilegesREVOKESELECTONemi.ordersFROMjeff;REVOKE{object_privilege [,object_privilege]... |ALL[PRIVILEGES]}ON [schema.]objectFROM{user|role|PUBLIC} [,{user|role|PUBLIC}]... [CASCADECONSTRAINTS]GRANTREVOKERevokingObjectPrivileges

WITHGRANTOPTIONBobJeffEmiEmiJeffBobRevokingobjectprivilegeswillcascadewhengivenWITHGRANTOPTIONObtainingPrivilegesInformationDataDictionaryViewsDBA_SYS_PRIVSSESSION_PRIVSDBA_TAB_PRIVSDBA_COL_PRIVSRolesUsersPrivilegesRolesUPDATE

ONJOBSINSERTONJOBSSELECT

ONJOBS

CREATETABLECREATESESSIONHR_CLERKHR_MGRABCBenefitsofRoles

EasierprivilegemanagementDynamicprivilegemanagementSelectiveavailabilityofprivilegesCanbegrantedthroughtheoperatingsystemImprovedperformanceCreatingRolesCREATEROLEoe_clerk;CREATEROLEhr_clerk IDENTIFIEDBYbonus;CREATEROLEhr_manager IDENTIFIEDEXTERNALLY;

PredefinedRolesRoleName DescriptionCONNECT, Theserolesareprovided

RESOURCE,DBA forbackwardcompatibilityEXP_FULL_DATABASE Privilegestoexportthe

databaseIMP_FULL_DATABASE Privilegestoimportthe

databaseDELETE_CATALOG_ROLE

DELETEprivilegeson datadictionarytablesEXECUTE_CATALOG_ROLE

EXECUTEprivilegeon datadictionarypackagesSELECT_CATALOG_ROLE

SELECTprivilegeondata

dictionarytablesModifyingRolesALTERROLEhr_clerk IDENTIFIEDEXTERNALLY;ALTERROLEhr_manager NOTIDENTIFIED;ALTERROLEoe_clerk

IDENTIFIEDBYorder;AssigningRolesGRANThr_clerkTOhr_manager;GRANToe_clerkTOscott;GRANThr_managerTOscottWITHADMINOPTION;EstablishingDefaultRolesALTERUSERscott

DEFAULTROLEhr_clerk,oe_clerk;ALTERUSERscottDEFAULTROLEALL;ALTERUSERscottDEFAULTROLEALLEXCEPT hr_clerk;ALTERUSERscottDEFAULTROLENONE;ApplicationRolesApplicationrolescanbeenabledonlybyauthorizedPL/SQLpackagesTheUSINGpackageclausecreatesanApplicationRoleCREATEROLEadmin_role IDENTIFIEDUSINGhr.employee;EnablingandDisablingRolesDisablearoletorevoketherolefromausertemporarilyEnablearoletograntittemporarilyTheSETROLEcommandenablesand

disablesrolesDefaultrolesareenabledforauseratlogin.Apasswordmayberequiredtoenablearole.EnablingandDisablingRolesSETROLEhr_clerk;SETROLEoe_clerkIDENTIFIEDBYorder;SETROLEALLEXCEPToe_clerk;RemovingRolesfromUsersREVOKEhr_managerFROMPUBLIC;REVOKEoe_clerkFROMscott;RemovingRolesDROPROLEhr_manager;GuidelinesforCreatingRolesBENEFITSPAYROLLHR_MANAGERHR_CLERKPAY_CLERKUserrolesApplicationrolesApplicationprivilegesUsersPayrollprivilegesBenefitsprivilegesGuidelinesforUsingPasswordsandDefaultRolesDefaultrolePasswordprotected(notdefault)SelectprivilegesINSERT,UPDATE,DELETE,

andSELECTprivilegesPAY_CLERKPAY_CLERK_RODisplayingRoleInformationRoleView DescriptionDBA_ROLES AllrolesthatexistinthedatabaseDBA_ROLE_PRIVS RolesgrantedtousersandrolesROLE_ROLE_PRIVS RolesthataregrantedtorolesDBA_SYS_PRIVS Systemprivilegesgrantedtousers androlesROLE_SYS_PRIVS SystemprivilegesgrantedtorolesROLE_TAB_PRIVS ObjectprivilegesgrantedtorolesSESSION_ROLES Rolesthattheusercurrentlyhas enabledConditionalExpressionsProvidetheuseofIF-THEN-ELSElogicwithinaSQLstatementUsetwomethods:CASEexpressionDECODEfunctionTheCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofanIF-THEN-ELSEstatement:CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDUsingtheCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofanIF-THEN-ELSEstatement:SELECTlast_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THEN1.10*salaryWHEN'ST_CLERK'THEN1.15*salaryWHEN'SA_REP'THEN1.20*salaryELSEsalaryEND"REVISED_SALARY"FROMemployees;……TheDECODEFunctionFacilitatesconditionalinquiriesbydoingtheworkofaCASEorIF-THEN-ELSEstatement:DECODE(col|expression,search1,result1

[,search2,result2,...,]

[,default])UsingtheDECODEFunctionSELECTlast_name,job_id,salary,DECODE(job_id,'IT_PROG',1.10*salary,'ST_CLERK',1.15*salary,'SA_REP',1.20*salary,salary)REVISED_SALARYFROMemployees;……UsingtheDECODEFunctionDisplaytheapplicabletaxrateforeachemployeeindepartment80.SELECTlast_name,salary,DECODE(TRUNC(salary/2000,0),0,0.00,1,0.09,2,0.20,3,0.30,4,0.40,5,0.42,6,0.44,0.45)TAX_RATEFROMemployeesWHEREdepartment_id=80;WriteyourINSERTstatementwithasubquery.

DonotusetheVALUESclause.MatchthenumberanddatatypeofcolumnsintheINSERTclausetothoseinthesubquery.CopyingRows

FromAnotherTableINSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';4rowscreated.UPDATEemployeesSETjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.UpdatingTwoColumnsWithaSubqueryUpdateemployee114’sjobandsalarytomatchthatofemployee205.TheMERGESt