ORACLE培训

Oracle9iDB/RACArchitecture,Installation,[e-mail]3,2005Agenda•OracleRDBMSArchitecture•OracleRACInstruction•Oracle9iRACInstallation•Export,Import,andSQL*Loader•PerformanceTuningOracleRDBMSArchitectureDatabaseOracle数据库结构InstanceSGARedologbufferDatabuffercacheSharedpoolDatadict.cacheLibrarycachePMONDBW0SMONLGWRCKPTOthersUserprocessServerprocessPGAControlfilesDatafilesRedologfilesArchivedlogfilesParameterfilePasswordfileOracle数据文件PasswordfileParameterfileArchivedlogfilesControlfilesDatafilesRedologfilesDatabaseGroup1Log1a.rdoLog1b.rdoGroup2Log2a.rdoLog2b.rdoGroup3Log3a.rdoLog3b.rdoGroup1Log1a.rdoLog1b.rdo多组重作日志文件Disk1Disk2(Member1)(Member2)其他关键的物理文件DatabasePasswordfileParameterfileArchivedlogfilesOracle实例BackgroundprocessesAnOracleinstance:•IsameanstoaccessanOracledatabase•AlwaysopensoneandonlyonedatabaseMemorystructuresInstanceSGARedologbufferDatabuffercacheSharedpoolPMONDBW0SMONLGWRCKPTOthers数据库后台进程PasswordfileParameterfileDatafile3Redologfile2Datafile2ControlfilesRedologfile1Datafile1DatabaseArchivedlogfilesdest1UserprocessServerprocessPGAInstanceSGARedologbufferDatabuffercacheLargepoolLocksSharedpoolDatadict.cacheSharedSQLandPL/SQLPMONDBWnSMONLGWRCKPTARC0ARC1Archivedlogfilesdest2Database逻辑结构物理结构TablespaceDatafileOSblockOracleblockSegmentExtent数据库的存储结构数据库的存储结构DatabaseTABLESPACETABLESPACETABLESPACETABLESPACE数据库的存储结构TABLESPACEDatafileDatafileDatafileDatafileSegmentSegmentSegmentSYSTEM逻辑结构物理结构TEMPINDEXTOOLSUSEROracle9i缺省表空间CREATEDATABASECommandCREATEDATABASEuser01USERSYSIDENTIFIEDBYORACLEUSERSYSTEMIDENTIFIEDBYMANAGERCONTROLFILEREUSELOGFILEGROUP1('$HOME/ORADATA/u01/redo01.log')SIZE100M,GROUP2('$HOME/ORADATA/u02/redo02.log')SIZE100M,GROUP3('$HOME/ORADATA/u03/redo03.log')SIZE100MMAXLOGFILES5MAXLOGMEMBERS5MAXLOGHISTORY1MAXDATAFILES100MAXINSTANCES1ARCHIVELOGFORCELOGGINGCHARACTERSETUS7ASCII--ZHS16GBKNATIONALCHARACTERSETAL16UTF16DATAFILE'/$HOME/ORADATA/u01/system01.dbf'SIZE325MDEFAULTTEMPORARYTABLESPACEtempUNDOTABLESPACEundotbsSETTIME_ZONE='America/New_York'创建数据库表空间TablespacesCREATETABLESPACEapp_dataDATAFILE'/DISK4/app_data_01.dbf'SIZE100M,'/DISK5/appdata_02.dbf'SIZE100MMINIMUMEXTENT500KDEFAULTSTORAGE(INITIAL500KNEXT500KMAXEXTENTS500PCTINCREASE0);表空间的空间管理方法•字典管理的表空间(dictionary-managed):–Defaulttechnique–Freeextentsrecordedindatadictionarytables•本地管理的表空间(locally-managed):–Freeextentsrecordedinbitmap–Eachbitcorrespondstoablockorgroupofblocks–Bitvalueindicatesfreeorused本地管理的表空间•Reducedrecursivespacemanagement•Reducedcontentionondatadictionarytables•Norollbackgenerated•NocoalescingrequiredCREATETABLESPACEuser_dataDATAFILE'/DISK2/user_data_01.dbf'SIZE500MEXTENTMANAGEMENTLOCALUNIFORMSIZE10M;临时表空间•Usedforsortoperations•Cannotcontainanypermanentobjects•Locallymanagedextentsrecommended•UNIFORMSIZE=SORT_AREA_SIZE*nCREATETEMPORARYTABLESPACEtempTEMPFILE'/DISK2/temp_01.dbf'SIZE500MEXTENTMANAGEMENTLOCALUNIFORMSIZE10M;删除表空间DROPTABLESPACEapp_dataINCLUDINGCONTENTS;•Tablespaceremovedfromdatadictionary•Optionally,contentsremovedfromdatadictionary•OSfilesnotdeleted改变表空间的大小•Changethesizeofadatafile:–Automatically–Manually•AddadatafileTablespaceAPP_DATA100Mapp_data_02.dbf100Mapp_data_01.dbf100Mapp_data_03.dbf200M启动数据文件的自动扩展ALTERTABLESPACEapp_dataADDDATAFILE'/DISK6/app_data_04.dbf'SIZE200MAUTOEXTENDONNEXT10MMAXSIZE500M;TablespaceAPP_DATAapp_data_02.dbf100Mapp_data_01.dbf100Mapp_data_04.dbf200Mapp_data_03.dbf200MALTERDATABASEDATAFILE'/DISK5/app_data_02.dbf'RESIZE200M;TablespaceAPP_DATA100Mapp_data_02.dbf100Mapp_data_01.dbf100Mapp_data_04.dbf200Mapp_data_03.dbf200M手工改变数据文件的大小向表空间添加数据文件ALTERTABLESPACEapp_dataADDDATAFILE'/DISK5/app_data_03.dbf'SIZE200M;TablespaceAPP_DATAapp_data_02.dbf100Mapp_data_01.dbf100Mapp_data_03.dbf200M更改数据文件的位置•Thetablespacemustbeoffline.•Thetargetdatafilesmustexist.ALTERTABLESPACEapp_dataRENAMEDATAFILE'/DISK4/app_data_01.dbf'TO'/DISK5/app_data_01.dbf';更改数据文件的位置•Thedatabasemustbemounted.•Thetargetdatafilemustexist.ALTERDATABASERENAMEFILE'/DISK1/system_01.dbf'TO'/DISK2/system_01.dbf';获取表空间的信息•Tablespaceinformation:–DBA_TABLESPACES–V$TABLESPACE•Datafileinformation:–DBA_DATA_FILES–V$DATAFILE•Tempfileinformation:–DBA_TEMP_FILES–V$TEMPFILE表空间的空间使用情况•表空间的大小dba_data_files–Selecttablespace_name,sum(bytes)fromdba_data_filesgroupbytablespace_name•剩余空间dba_free_space–Selecttablespace_name,sum(bytes)fromdba_free_spacegroupbytablespace_name数据表类型RegulartableClusterPartitionedtableIndex-organizedtableOracle数据类型CHAR(N),NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)DATERAW(N)BLOB,CLOB,NCLOB,BFILELONG,LONGRAWROWID,UROWIDVARRAYTABLEREFDatatypeBuilt-inUser-definedScalarRelationshipCollection创建数据库表CREATETABLEemployee(idNUMBER(7),last_nameVARCHAR2(25),dept_idNUMBER(7))PCTFREE20PCTUSED50STORAGE(INITIAL200KNEXT200KPCTINCREASE0MAXEXTENTS50)TABLESPACEdata;创建临时表Therowsareprivatetothesession.Tablesretaindataonlyforthedurationofatransactionorsession.DMLlocksarenotacquiredonthedata.DMLsdonotgenerateredologs.ONCOMMITPRESERVEROWS;CREATEGLOBALTEMPORARYTABLEemployee_tempASSELECT*FROMemployee;块空间的使用InsertsInsertsInsertsInser