大型數(shù)據(jù)庫(kù)技術(shù)：Oracle基礎(chǔ)

Oracle基礎(chǔ)數(shù)據(jù)庫(kù)項(xiàng)目失敗的最常見(jiàn)原因之一是：對(duì)數(shù)據(jù)庫(kù)的實(shí)際認(rèn)識(shí)不足，缺乏對(duì)所用基本工具的了解了解你的數(shù)據(jù)庫(kù)，掌握它是怎樣工作的，弄清楚它能為你做什么，并且最大限度地加以利用建議閱讀入門(mén)資料DBA手冊(cè)O(shè)racleConcept進(jìn)階資料ThomasKyte，Oracle9i&10g編程藝術(shù)：深入數(shù)據(jù)庫(kù)體系結(jié)構(gòu)，人民郵電出版社ThomasKyte，Oracle專家高級(jí)編程，清華大學(xué)出版社ThomasKyte，Oracle高效設(shè)計(jì)，機(jī)械工業(yè)出版社在線資源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;分析函數(shù)Oracle體系結(jié)構(gòu)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 RECOOptionalbackgroundprocesses

ARCn LMON Snnn

QMNn

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表空間數(shù)據(jù)庫(kù)由稱為表空間的邏輯單位組成保留相關(guān)數(shù)據(jù)庫(kù)對(duì)象的組Oracle數(shù)據(jù)庫(kù)中的典型表空間包括SYSTEM表空間DATA表空間USER表空間TOOLS表空間TEMP表空間數(shù)據(jù)庫(kù)的控制空間分配（例如表和索引）為數(shù)據(jù)庫(kù)用戶設(shè)置空間配額備份或恢復(fù)數(shù)據(jù)表空間段表空間中存儲(chǔ)在數(shù)據(jù)庫(kù)空間分配中的邏輯單位稱為段定義為分配給邏輯數(shù)據(jù)庫(kù)結(jié)構(gòu)的擴(kuò)展區(qū)集合不同類型的段數(shù)據(jù)段索引段回滾段臨時(shí)段擴(kuò)展區(qū)擴(kuò)展區(qū)的集合由一定數(shù)目的相鄰數(shù)據(jù)塊和段組成增量擴(kuò)展區(qū)是與上次該段中分配的擴(kuò)展區(qū)大小相同或更大的后續(xù)擴(kuò)展區(qū)Oracle使用獨(dú)立的算法搜索整個(gè)自由空間查找第一個(gè)具有與增量擴(kuò)展區(qū)具有相同的或更大大小的自由、臨近數(shù)據(jù)塊集合擴(kuò)展區(qū)一旦Oracle在表空間中找到并分配了必需的可用空間，則它將分配一部分與新增擴(kuò)展區(qū)大小相對(duì)應(yīng)的可用空間Oracle更新段標(biāo)題和數(shù)據(jù)詞典，以顯示新的擴(kuò)展區(qū)已經(jīng)被分配而且分配的空間不再可用數(shù)據(jù)塊代表數(shù)據(jù)庫(kù)存儲(chǔ)的最佳粒度級(jí)別DatabaseSchemaSchemaObjectsTablesTriggersConstraintsIndexesViewsSequencesStoredprogramunitsSynonymsUser-defineddatatypesDatabaselinksAschemaisanamedcollectionofobjectsAuseriscreated,andacorrespondingschemaiscreatedUsercanbeassociatedonlywithoneschemaUsernameandschemaareoftenusedinterchangeablyOracle的語(yǔ)句處理DDLDML大多數(shù)DML都包含了Queryupdateempsetename='KingSley'whereename='King';QueryQuery和DML在Oracle的執(zhí)行階段非常類似Query返回結(jié)果集，DML修改結(jié)果集中的值Oracle的語(yǔ)句處理解析對(duì)提交的語(yǔ)句進(jìn)行語(yǔ)法和語(yǔ)義檢查優(yōu)化生成一個(gè)可在Oracle中用來(lái)執(zhí)行語(yǔ)句的最佳計(jì)劃行資源生成為會(huì)話取得最佳計(jì)劃和建立執(zhí)行計(jì)劃執(zhí)行完成實(shí)際執(zhí)行查詢的行資源生成步驟的輸出Oracle的語(yǔ)句處理解析(Parsing)：將已經(jīng)提交的語(yǔ)句分解，判定屬于哪種類型，并在其上執(zhí)行各種檢驗(yàn)操作語(yǔ)法檢查：正確表述，符合SQL規(guī)則？語(yǔ)義分析：正確應(yīng)用SQL對(duì)象？授權(quán)？歧義？檢查SharedPool：已被其他Session處理過(guò)？Oracle的語(yǔ)句處理SharedPoolSGA的一部分，高速緩存以及以前執(zhí)行過(guò)的SQL、PL/SQL、DataDictionary等恰當(dāng)使用SharedPool是在Oracle中建立可伸縮解決方案的關(guān)鍵Oracle的語(yǔ)句處理硬解析(hardparse)語(yǔ)句通過(guò)執(zhí)行階段的每一個(gè)步驟：從解析到優(yōu)化、到行資源生成和執(zhí)行軟解析(softparse)語(yǔ)句通過(guò)執(zhí)行階段的某些步驟，特別是跳過(guò)優(yōu)化步驟（最昂貴的步驟）Oracle的語(yǔ)句處理當(dāng)Oracle接收到語(yǔ)句后，就會(huì)對(duì)其進(jìn)行HASH處理V$SQL動(dòng)態(tài)性能視圖生成HASH值后，Oracle在SharedPool中搜索，尋找具有相同HASH值的語(yǔ)句將找到的SQL_TEXT與用戶的SQL語(yǔ)句進(jìn)行比較，確保兩者完全相同Oracle的語(yǔ)句處理Oracle確認(rèn)用戶的SQL語(yǔ)句和SharedPool中的相同后，還必須確定兩者在語(yǔ)義上相同不同用戶可能有相同名稱的表驗(yàn)證查詢是在相同的環(huán)境中解析環(huán)境是指能夠影響到查詢方案生成的所有設(shè)置，如SORT_AREA_SIZE、OPTIMIZER_MODE等如果各個(gè)查詢的優(yōu)化器模式不同，它們也是不相同的索引存儲(chǔ)在常規(guī)表中行采用沒(méi)有特定的次序存儲(chǔ)Oracle將獲取的名字與ROWID進(jìn)行關(guān)聯(lián)ROWID是表中行的物理地址，可以告知對(duì)象的來(lái)源、所處的文件以及文件中特定數(shù)據(jù)塊Oracle的語(yǔ)句處理到目前為止，當(dāng)Oracle完成了所有工作，并且找到了匹配查詢，它就可以從解析過(guò)程中返回，報(bào)告已經(jīng)進(jìn)行了一次軟解析如果沒(méi)有找到匹配查詢，就需要進(jìn)行硬解析索引B樹(shù)索引(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索引進(jìn)行FTS(FullTableScan)時(shí)，進(jìn)行批量數(shù)據(jù)讀取如果Oracle認(rèn)為用戶的查詢將選取超過(guò)全體記錄的2%~5%，那么就會(huì)趨向使用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'sschemaDynamicPerformanceTablesDynamicperformanceviewsrecordcurrentdatabaseactivity.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

WITHADMINOPTIONGRANTREVOKEnocascadingeffectswhenasystemprivilegeisrevoked,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

WITHGRANTOPTIONRevokingobjectprivilegeswillcascadewhengivenWITHGRANTOPTIONObtainingPrivilegesInformationDataDictionaryViewsDBA_SYS_PRIVSSESSION_PRIVSDBA_TAB_PRIVSDBA_COL_PRIVSRolesUsersPrivilegesRolesUPDATE

ONJOBSINSERTONJOBSSELECT

ONJOBS

CREATETABLECREATESESSIONHR_CLERKHR_MGRABCBenefitsofRoles

EasierprivilegemanagementDynamicprivilegemanagementSelectiveavailabilityofprivilegesCanbegrantedthroughtheoperatingsystemImprovedperformanceCreatingRolesCREATEROLEoe_clerk;CREATEROLEhr_clerk IDENTIFIEDBYbonus;CREATEROLEhr_manager IDENTIFIEDEXTERNALLY;

PredefinedRolesModifyingRolesALTERROLEhr_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_RODisplayingRoleInformationConditionalExpressionsProvidetheuseofIF-THEN-ELSElogicwithinaSQLstatementUsetwomethods:CASEexpressionDECODEfunctionTheCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofanIF-THEN-ELSEstatement:CASEexprWHENcomparison_expr1THENreturn_expr1

[WHENcomparison_expr2THENreturn_expr2WHENcomparison_exprnTHENreturn_exprnELSEelse_expr]ENDUsingtheCASEExpressionFacilitatesconditionalinquiriesbydoingtheworkofanIF-THEN-ELSEstatement:TheDECODEFunctionFacilitatesconditionalinquiriesbydoingtheworkofaCASEorIF-THEN-ELSEstatement:DECODE(col|expression,search1,result1

[,search2,result2,...,]

[,default])UsingtheDECODEFunctionUsingtheDECODEFunctionDisplaytheapplicabletaxrateforeachemployeeindepartment80.WriteyourINSERTstatementwithasubquery.

DonotusetheVALUESclause.MatchthenumberanddatatypeofcolumnsintheINSERTclausetothoseinthesubquery.CopyingRows

FromAnotherTableUPDATEemployeesSETjob_id=(SELECTjob_id

FROMemployeesWHEREemployee_id=205),salary=(SELECTsalaryFROMemployeesWHEREemployee_id=205)WHEREemployee_id=114;1rowupdated.UpdatingTwoColumnsWithaSubqueryUpdateemployee114’sjobandsalarytomatchthatofemployee205.TheMERGEStatementProvidestheabilitytoconditionallyupdateorinsertdataintoadatabasetablePerformsanUPDATEiftherowexists,andanINSERTifitisanewrow:AvoidsseparateupdatesIncreasesperformanceandea