Oracle 10.2 New Features.ppt

1©2005JulianDykeOracle10.2NewFeaturesJulianDykeIndependentConsultantWebVersionjuliandyke.com©2005JulianDykejuliandyke.com2Introduction©2005JulianDykejuliandyke.com3ABriefHistoryofOracleVersionDate2June19793March19834October19845.0April19856.0July19887.0June19927.1May19947.2May19957.3February1996©2005JulianDykejuliandyke.com4ABriefHistoryofOracleContinued....VersionDateReleaseName8.0June1997Oracle88.1.5February1999Oracle8iRelease18.1.6November1999Oracle8iRelease28.1.7August2000Oracle8iRelease39.0.1June2001Oracle9iRelease19.2May2002Oracle9iRelease210.1January2004Oracle10gRelease110.2July2005Oracle10gRelease2©2005JulianDykejuliandyke.com5ComparisonBetween10.1and10.2Version10.110.2SupportedParameters255258UnsupportedParameters9181127DynamicPerformanceViews(V$)340396FixedViews(X$)529597Events(Waits)811874Statistics332363Latches348382BackgroundProcesses(FixedSGA)109157©2005JulianDykejuliandyke.com6DMLErrorLogging©2005JulianDykejuliandyke.com7DMLErrorLoggingIntroducedinOracle10.2WorkswithDMLstatements:INSERTUPDATEMERGEDELETELogserrorsencounteredduringDMLoperationsinerrorloggingtableAvoidsrollingbackentirestatementifanerroroccurs©2005JulianDykejuliandyke.com8DMLErrorLoggingTableInformationaboutfailedrowswrittentoDMLErrorLoggingTableDefaultnameisERR$_plusfirst25charactersoftablenameContainsMandatorycolumns-OraclecontrolinformationOptionalcolumns-containdatafromfailedrowsCanbecreatedmanuallyorusingDBMS_ERRLOGpackageEXECUTEDBMS_ERRLOG.CREATE_ERROR_LOG(DMLtable_name,[error_table_name]);©2005JulianDykejuliandyke.com9DMLErrorLoggingTableTocreateaDMLerrorloggingtableuse:EXECUTEDBMS_ERRLOG.CREATE_ERROR_LOG(DMLtable_name);Forexample:EXECUTEDBMS_ERRLOG.CREATE_ERROR_LOG('CAR');CreatesDMLerrortablecalledERR$_CAREXECUTEDBMS_ERRLOG.CREATE_ERROR_LOG('CAR'.'ERR_CAR');DBMS_ERRLOGcreatescolumnswithrecommendeddatatypesinDMLErrorTableCanoptionallyspecifynameforDMLerrortable©2005JulianDykejuliandyke.com10MandatoryColumnsRequiredinDMLErrorLoggingTableColumnNameDataTypeDescriptionORA_ERR_NUMBER$NUMBEROracleerrornumberORA_ERR_MESG$VARCHAR2(2000)OracleerrormessagetextORA_ERR_ROWID$ROWIDRowidoftherowinerror(updateanddeleteonly)ORA_ERR_OPTYP$VARCHAR2(2)TypeofoperationI=insert,U=update,D=deleteORA_ERR_TAG$VARCHAR2(2000)Usersuppliedtag©2005JulianDykejuliandyke.com11OptionalColumnsCanhavezero,oneormorecolumnsContaindatafromfailedrowsErrortablecolumnnamessameasDMLtablecolumnnamesErrortabledatatypesmaydifferfromDMLtabledatatypesCapturetypeconversionerrorsColumnoverflowDMLTableColumnTypeErrorLoggingTableColumnTypeNUMBERVARCHAR2(4000)CHAR/VARCHAR2(n)VARCHAR2(4000)NCHAR/NVARCHAR2(n)NVARCHAR2(4000)DATE/TIMESTAMPVARCHAR2(4000)RAWRAW(2000)ROWIDUROWIDLONG/LOBNotsupportedUser-definedtypesNotsupported©2005JulianDykejuliandyke.com12Example:CARtableColumnNameDataTypeSEASON_KEYVARCHAR2(4)RACE_KEYVARCHAR2(2)POSITIONNUMBERDRIVER_KEYVARCHAR2(4)TEAM_KEYVARCHAR2(3)ENGINE_KEYVARCHAR2(3)LAPS_COMPLETEDNUMBERCLASSIFICATION_KEYVARCHAR2(4)NOTESVARCHAR2(100)DRIVER_POINTSNUMBERTEAM_POINTSNUMBEREXECUTEDBMS_ERRLOG.CREATE_ERROR_LOG('CAR'.'ERR_CAR');©2005JulianDykejuliandyke.com13Example:ERR_CARtableColumnNameDataTypeORA_ERR_NUMBER$NUMBERORA_ERR_MESG$VARCHAR2(2000)ORA_ERR_ROWID$ROWIDORA_ERR_OPTYP$VARCHAR2(2)ORA_ERR_TAG$VARCHAR2(2000)SEASON_KEYVARCHAR2(4000)RACE_KEYVARCHAR2(4000)POSITIONVARCHAR2(4000)DRIVER_KEYVARCHAR2(4000)TEAM_KEYVARCHAR2(4000)ENGINE_KEYVARCHAR2(4000)LAPS_COMPLETEDVARCHAR2(4000)CLASSIFICATION_KEYVARCHAR2(4000)NOTESVARCHAR2(4000)DRIVER_POINTSVARCHAR2(4000)TEAM_POINTSVARCHAR2(4000)MandatoryColumnsOptionalColumns©2005JulianDykejuliandyke.com14LOGERRORSClauseSyntaxis:LOGERRORSINTOerror_table[('tag')]REJECTLIMITlimit;CanoptionallyspecifyaREJECTLIMITsubclausenumberoferrorsbeforestatementterminatesandrollsbackcanalsospecifyUNLIMITEDdefaultvalueis0ifstatementrollsback,errorloggingtableretainslogentriesCanoptionallyspecifytagtocorrelatefailedrowswithDMLstatement©2005JulianDykejuliandyke.com15Example:INSERTSTATEMENTForexample:INSERTINTOcar(season_key,race_key,position,driver_key,team_key,engine_key,laps_completed,classification_key,notes)SELECTseason_key,race_key,position,driver_key,team_key,engine_key,laps_completed,classification_key,notesFROMexternal_carLOGERRORSINTOerr_carREJECTLIMITUNLIMITED;Note:INSERTstatementdoesnotreturnanyerrormessagesevenifrowsarewrittentoDMLerrortable©2005JulianDykejuliandyke.com16AsynchronousCommit©2005JulianDykejuliandyke.com17AsynchronousCommitInOracle10.2andaboveCOMMITscanbeoptionallydeferredNewsyntaxforCOMMITstatementCOMMIT[WRITE[IMMEDIATE|BATCH][WAIT|NOWAIT]]WRITEclauseIMMEDIATEspecifiesredoshouldbewrittenimmediatelybyLGWRprocesswhentransactioniscommitted(default)BATCHcausesredotobebufferedtoredologWAITspecifiescommitwillnotreturnuntilredoispersistentinonlineredolog(default)NOWAITallowscommittoreturnbeforeredoispersistentinredolog©2005JulianDykejuliandyke.com18AsynchronousCommitCOMMITStatementExamplesCOMMIT;--IMMEDIATEWAITCOMMITWRITE;--SameasCOMMIT;COMMITWRITEIMMEDIATE;--Sameas