实用教程章其他概念

第12章

其他概念12.1数据库链接12.2快照12.3序列12.1数据库链接12.1.1创建数据库链接1．利用OEM创建数据库链接【例12.1】

利用OEM创建数据库链接MY_LINK。在“方案”属性页的“数据库对象”栏选择“数据库链接”，进入“数据库链接搜索”页面。单击“创建”按钮，进入“创建数据库链接”页面，如图12.1所示。在“创建数据库链接”页面指定创建数据库链接的设置。12.1.1创建数据库链接2．利用CREATEDATABASELINK命令创建数据库链接语法格式：CREATE[PUBLIC]DATABASELINKdblink_name [CONNECTTOuserIDENTIFIEDBYpassword] USINGconnect_string【例12.2】

为XSCJ数据库创建一个名为MY_PLINK的公用链接。CREATEPUBLICDATABASELINKMY_PLINK CONNECTTOSCOTTIDENTIFIEDBYtiger USING'XSCJ';12.1.2使用数据库链接【例12.3】查询远程数据库XSCJ表KCB2中的所有课程情况。SELECT*FROMSCOTT.KCB2@MY_PLINK;上述查询将通过MY_PLINK数据库链接来访问KCB2表，对于经常使用的数据库链接，可以建立一个本地的同义词，方便使用。【例12.4】为XSCJ远程数据库表KCB2创建一个同义词。CREATEPUBLICSYNONYMKCB2_syn FORSCOTT.KCB2@MY_PLINK;12.1.3删除数据库链接使用PL/SQL删除数据库链接的语法格式如下：DROP[PUBLIC]DATABASELINKdblink_namedblink_name为要删除的数据库链接名称。【例12.5】删除公用数据库链接MY_PLINK。DROPPUBLICDATABASELINKMY_PLINK;12.2快照在创建一个快照之前，首先要在本地数据库中创建一个到源数据库的链接。下面的例子创建一个名为SH_LINK的私有数据库链接。【例12.6】创建一个名为SH_LINK的私有数据库链接。CREATEDATABASELINKSH_LINK CONNECTTOSCOTTIDENTIFIEDBYtiger USING'XSCJ';12.2.1创建快照1．使用OEM创建快照在“方案”属性页的“实体化视图”栏选中“实体化视图”，单击鼠标左键，进入“实体化视图搜索”页面。单击“创建”按钮，进入“创建实体化视图”页面，如图12.2所示。“创建实体化视图”页面有五个选项页面：一般信息、刷新、存储、索引存储和选项。（1）“一般信息”选项页面：指定实体化视图的相关信息，如图12.2所示。OverviewofMaterializedViews

Materializedviewsarequeryresultsthathavebeenstoredor"materialized"inadvanceasschemaobjects.TheFROMclauseofthequerycannametables,views,andmaterializedviews.Collectivelytheseobjectsarecalledmastertables(areplicationterm)ordetailtables(adatawarehousingterm).Materializedviewsareusedtosummarize,compute,replicate,anddistributedata.Theyaresuitableinvariouscomputingenvironments,suchasthefollowing:■Indatawarehouses,youcanusematerializedviewstocomputeandstoredatageneratedfromaggregatefunctionssuchassumsandaverages.Inmaterializedviewreplication,theviewcontainsacompleteorpartialcopyofatablefromasinglepointintime.Inmobilecomputingenvironments,youcanusematerializedviewstodownloadadatasubsetfromcentralserverstomobileclients,withperiodicrefreshesfromthecentralserversandpropagationofupdatesbyclientstothecentralservers.Inareplicationenvironment,amaterializedviewsharesdatawithatableinadifferentdatabase,calledamasterdatabase.Thetableassociatedwiththematerializedviewatthemastersiteisthemastertable.Figure4–7illustratesamaterializedviewinonedatabasebasedonamastertableinanotherdatabase.Updatestothemastertablereplicatetothematerializedviewdatabase.12.2.1创建快照（2）“刷新”选项页面：切换到“刷新”选项卡页面，如图12.3所示。在该选项页面中指定关于实体化视图的刷新特性的信息。12.2.1创建快照（3）“存储”选项页面：“存储”页面如图12.4所示。在该选项页面可以指定实体化视图的存储特征，具体设置请参照创建表相关的内容。12.2.1创建快照（4）“索引存储”选项页面：“索引存储”页面如图12.5所示。在该选项页面可以进行如下设置。12.2.1创建快照（5）“选项”选项页面：“选项”页面如图12.6所示。12.2.1创建快照2．使用SQL命令创建快照语法格式：CREATESNAPSHOT[schema.]snapshot_name /*将要创建的快照名称*/ [PCTFEEinteger] [PCTUSEDinteger] [INITRANSinteger] [MAXTRANSinteger] [STORAGEstorage_clasue] /*快照的存储特征*/ [TABLESPACEtablespace] /*指定表空间*/ [USINGINDEX[PCTFEEinteger] /*使用索引*/ [PCTUSEDinteger] [INITRANSinteger] [MAXTRANSinteger]] [REFRESH[FAST|COMPLETE|FORCE][STARTWITHdate][NEXTdate]] /*指定快照的刷新特性的信息*/ [FORUPDATE]ASsubquery /*用于置入快照的SQL查询*/12.2.1创建快照【例12.7】在本地服务器上创建快照。CREATESNAPSHOTKC_COUNT PCTFREE5 TABLESPACESYSTEM REFRESHCOMPLETE STARTWITHSysDate NEXTSysDate+7 AS SELECTCOUNT(*) FROMSCOTT.KCB2@SH_LINK;12.2.2修改快照使用PL/SQL方式修改快照的语法格式如下：ALTERSNAPSHOT[schema.]snapshot_name [PCTFEEinteger] [PCTUSEDinteger] [INITRANSinteger] [MAXTRANSinteger] [STORAGEstorage_clasue] /*快照的存储特征*/ [TABLESPACEtablespace] /*指定表空间*/ [USINGINDEX[PCTFEEinteger] /*使用索引*/ [PCTUSEDinteger] [INITRANSinteger] [MAXTRANSinteger]] [REFRESH[FAST|COMPLETE|FORCE][STARTWITHdate][NEXTdate]]12.2.2修改快照【例12.8】修改例12.7中的快照。ALTERSNAPSHOTKC_COUNT PCTFREE10 PCTUSED25 INITRANS1 MAXTRANS20;12.2.3删除快照用SQL命令删除快照的语法格式为：DROPSNAPSHOTsnapshotname;snapshotname为要删除的快照名称。例如，要删除KC_COUNT快照，可使用如下语句：DROPSNAPSHOTKC_COUNT;12.3序列12.3.1创建序列1．利用OEM创建序列在“方案”属性页的“数据库对象”栏中选择“序列”，单击鼠标左键，进入“序列搜索”页面。单击“创建”按钮，进入“创建序列”页面，如图12.7所示。12.3.1创建序列2．利用SQL命令创建序列也可以使用SQL命令创建序列。语法格式：CREATESEQUENCE[schema.]sequence_name/*将要创建的序列名称*/ [INCREMENTBYinteger] /*递增或递减值*/ [STARTWITHinteger] /*初始值*/ [MAXVALUEinteger|NOMAXVALUE] /*最大值*/ [MINVALUEinteger|NOMINVALUE] /*最小值*/ [CYCLE|NOCYCLE] /*是否循环*/ [CACHEinteger|NOCACHE] /*高速缓冲区设置*/ [ORDER|NOORDER] /*序列号是序列否，按照顺序生成*/12.3.1创建序列【例12.9】创建一个降序序列。CREATESEQUENCES_TEST INCREMENTBY-2STARTWITH4500 MAXVALUE4500 MINVALUE1 CYCLE CACHE20 NOORDER;12.3.2修改序列界面方式修改序列的方法与创建序列类似，这里不再赘述，本节主要介绍使用SQL命令修改序列的方法。修改序列使用ALTERSEQUENCE语句，语法格式：ALTERSEQUENCE[schema.]sequence_name [INCREMENTBYinteger] /*递增或递减值*/ [MAXVALUEinteger|NOMAXVALUE] /*最大值*/ [MINVALUEinteger|NOMINVALUE] /*最小值*/ [CYCLE|NOCYCLE] /*是否循环*/ [CACHEinteger|NOCACHE] /*高速缓冲区设置*/ [ORDER|NOORDER]【例12.10】修改例12.9中序列。ALTERSEQUENCES_TEST INCREMENTBY-1 MAXVALUE9000 MINVALUE4500 NOORDER;12.3.3删除序列用SQL命令删除快照的语法格式如下所示。DROPSEQUENCEsequence_namesequence_name为要删除的序列名称。例如，要删除S_TEST序列，可使用如下语句。DROPSEQUENCES_TEST;WhatAreDatabaseLinks?Adatabaselinkisapointerthatdefinesaone-waycommunicationpathfromanOracleDatabaseservertoanotherdatabaseserver.Thelinkpointerisactuallydefinedasanentryinadatadictionarytable.Toaccessthelink,youmustbeconnectedtothelocaldatabasethatcontainsthedatadictionaryentry.Adatabaselinkconnectionisone-wayinthesensethataclientconnectedtolocaldatabaseAcanusealinkstoredindatabaseAtoaccessinformationinremotedatabaseB,butusersconnectedtodatabaseBcannotusethesamelinktoaccessdataindatabaseA.IflocalusersondatabaseBwanttoaccessdataondatabaseA,thentheymustdefinealinkthatisstoredinthedatadictionaryofdatabaseB.Adatabaselinkconnectionallowslocaluserstoaccessdataonaremotedatabase.Forthisconnectiontooccur,eachdatabaseinthedistributedsystemmusthaveauniqueglobaldatabasenameinthenetworkdomain.Theglobaldatabasenameuniquelyidentifiesadatabaseserverinadistributedsystem.Figure31–3showsanexampleofuserscottaccessingtheemptableontheremotedatabasewiththeglobalname:Databaselinksareeitherprivateorpublic.Iftheyareprivate,thenonlytheuserwhocreatedthelinkhasaccess;iftheyarepublic,thenalldatabaseusershaveaccess.Oneprincipaldifferenceamongdatabaselinksisthewaythatconnectionstoaremotedatabaseoccur.Usersaccessaremotedatabasethroughthefollowingtypesoflinks:ConnecteduserlinkUsersconnectasthemselves,whichmeansthattheymusthaveanaccountontheremotedatabasewiththesameusernameandpasswordastheiraccountonthelocaldatabase.FixeduserlinkUsersconnectusingtheusernameandpasswordreferencedinthelink.Forexample,ifJaneusesafixeduserlinkthatconnectstothehqdatabasewiththeusernameandpasswordscott/password,thensheconnectsasscott,Janehasalltheprivilegesinhqgrantedtoscottdirectly,andallthedefaultrolesthatscotthasbeengrantedinthehqdatabase.CurrentuserlinkAuserconnectsasaglobaluser.Alocalusercanconnectasaglobaluserinthecontextofastoredprocedure,withoutstoringtheglobaluser'spasswordinalinkdefinition.Forexample,JanecanaccessaprocedurethatScottwrote,accessingScott'saccountandScott'sschemaonthehqdatabase.CurrentuserlinksareanaspectofOracleAdvancedSecurity.CreatedatabaselinksusingtheCREATEDATABASELINKstatement.Afteralinkiscreated,youcanuseittospecifyschemaobjectsinSQLstatements.AboutSequencesSequencesaredatabaseobjectsfromwhichmultipleuserscangenerateuniqueintegers.Thesequencegeneratorgeneratessequentialnumbers,whichcanbeusedtogenerateuniqueprimarykeysautomatically,andtocoordinatekeysacrossmultiplerowsortables.Withoutsequences,sequentialvaluescanonlybeproducedprogrammatically.Anewprimarykeyvaluecanbeobtainedbyselectingthemostrecentlyproducedvalueandincrementingit.Thismethodrequiresalockduringthetransactionandcausesmultipleuserstowaitforthenextvalueoftheprimarykey;thiswaitingisknownasserialization.Ifdevelopershavesuchconstructsinapplications,thenyoushouldencouragethedeveloperstoreplacethemwithaccesstosequences.Sequenceseliminateserializationandimprovetheconcurrencyofanapplication.OverviewofSequences

Asequenceisaschemaobjectfromwhichmultipleuserscangenerateuniqueintegers.Asequencegeneratorprovidesahighlyscalableandwell-performingmethodtogeneratesurrogatekeysforanumberdatatype.SequenceCharacteristics

Asequencedefinitionindicatesgeneralinformation,suchasthefollowing:■Thenameofthesequence■Whetherthesequenceascendsordescends■Theintervalbetweennumbers■Whetherthedatabaseshouldcachesetsofgeneratedsequencenumbersinmemory■WhetherthesequenceshouldcyclewhenalimitisreachedThefollowingexamplecreatesthesequencecustomers_seqinthesampleschemaoe.AnapplicationcouldusethissequencetoprovidecustomerIDnumberswhenrowsareaddedtothecustomerstable.CREATESEQUENCEcustomers_seqSTARTWITH1000INCREMENTBY1NOCACHENOCYCLE;Thefirstreferencetocustomers_seq.nextvalreturns1000.Thesecondreturns1001.Eachsubsequentreferencereturnsavalue1greaterthanthepreviousreference.ConcurrentAccesstoSequences

Thesamesequencegeneratorcangeneratenumbersformultipletables.Inthisway,thedatabasecangenerateprimarykeysautomaticallyandcoordinatekeysacrossmultiplerowsortables.Forexample,asequencecangenerateprimarykeysforanorderstableandacustomerstable.ThesequencegeneratorisusefulinmultiuserenvironmentsforgeneratinguniquenumberswithouttheoverheadofdiskI/Oortransactionlocking.Forexample,twouserssimultaneouslyinsertnewrowsintotheorderstable.Byusingasequencetogenerateuniquenumbersfortheorder_idcolumn,neitheruserhastowaitfortheothertoenterthenextavailableordernumber.Thesequenceautomaticallygeneratesthecorrectvaluesforeachus