1 / 62

Oracle 10.2 New Features

Oracle 10.2 New Features. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Introduction. A Brief History of Oracle. A Brief History of Oracle. Continued. Comparison Between 10.1 and 10.2. DML Error Logging. DML Error Logging. Introduced in Oracle 10.2

suelita
Download Presentation

Oracle 10.2 New Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle 10.2New Features Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Introduction

  3. A Brief History of Oracle

  4. A Brief History of Oracle • Continued....

  5. Comparison Between 10.1 and 10.2

  6. DML ErrorLogging

  7. DML Error Logging • Introduced in Oracle 10.2 • Works with DML statements: • INSERT • UPDATE • MERGE • DELETE • Logs errors encountered during DML operations in error logging table • Avoids rolling back entire statement if an error occurs

  8. DML Error Logging Table • Information about failed rows written to DML Error Logging Table • Default name is ERR$_ plus first 25 characters of table name • Contains • Mandatory columns - Oracle control information • Optional columns - contain data from failed rows • Can be created manually or using DBMS_ERRLOG package EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(<DML table_name>,[<error_table_name>]);

  9. DML Error Logging Table • To create a DML error logging table use: EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(<DML table_name>); • For example: EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('CAR'); • Creates DML error table called ERR$_CAR • Can optionally specify name for DML error table EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('CAR'.'ERR_CAR'); • DBMS_ERRLOG creates columns with recommended data types in DML Error Table

  10. Mandatory Columns • Required in DML Error Logging Table

  11. Optional Columns • Can have zero, one or more columns • Contain data from failed rows • Error table column names same as DML table column names • Error table data types may differ from DML table data types • Capture type conversion errors • Column overflow

  12. Example: CAR table EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('CAR'.'ERR_CAR');

  13. Example: ERR_CAR table MandatoryColumns OptionalColumns

  14. LOG ERRORS Clause • Syntax is: LOG ERRORS INTO <error_table>[('<tag>')] REJECT LIMIT <limit>; • Can optionally specify a REJECT LIMIT subclause • number of errors before statement terminates and rolls back • can also specify UNLIMITED • default value is 0 • if statement rolls back, error logging table retains log entries • Can optionally specify tag to correlate failed rows with DML statement

  15. Example: INSERT STATEMENT • For example: INSERT INTO car( season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notes)SELECT season_key, race_key, position, driver_key, team_key, engine_key, laps_completed, classification_key, notesFROM external_carLOG ERRORS INTO err_car REJECT LIMIT UNLIMITED; • Note: INSERT statement does not return any error messages even if rows are written to DML error table

  16. AsynchronousCommit

  17. Asynchronous Commit • In Oracle 10.2 and above COMMITs can be optionally deferred • New syntax for COMMIT statement COMMIT [ WRITE [ IMMEDIATE|BATCH] [WAIT | NOWAIT] ] • WRITE clause • IMMEDIATE specifies redo should be written immediately by LGWR process when transaction is committed (default) • BATCH causes redo to be buffered to redo log • WAIT specifies commit will not return until redo is persistent in online redo log (default) • NOWAIT allows commit to return before redo is persistent in redo log

  18. Asynchronous Commit • COMMIT Statement Examples COMMIT; -- IMMEDIATE WAIT COMMIT WRITE; -- Same as COMMIT; COMMIT WRITE IMMEDIATE; -- Same as COMMIT; COMMIT WRITE IMMEDIATE WAIT; -- Same as COMMIT; COMMIT WRITE BATCH; -- BATCH WAIT COMMIT WRITE BATCH NOWAIT; -- BATCH NOWAIT

  19. Asynchronous Commit • COMMIT_WRITE initialization parameter • Controls default behaviour for commit operation • Default value is • Determines default value of COMMIT WRITE statement • If neither is set then commit records are written to disk before control is returned to user (Oracle 10.1 behaviour) • Can be modified using ALTER SESSION statement ALTER SESSION SET COMMIT_WRITE = 'IMMEDIATE,WAIT'; ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT';

  20. Asynchronous Commit • Advantages: • Eliminates the wait for an I/O to the redo log • Can improve performance by reducing latency • Improves response times • Disadvantages: • Must be able to tolerate loss of asynchronously committed transaction • Applicable where: • high volume of update transactions generate frequent redo log writes to disk • response times degraded by waits for redo log writes to disk

  21. Asynchronous Commit • Some thoughts... • Difficult to test on single CPU systems • Can lose data therefore cannot be used in OLTP or web environments unless middleware detects and resolves errors • Might work in DSS and batch environments where processes are repeatable e.g. • Parallel batch processing • Parallel direct load • But only of benefit if there are high number of transactions • Does not apply in DSS or batch environments • Change application to reduce number of COMMITs

  22. PL/SQLConditionalCompilation

  23. Conditional Compilation • In Oracle 10.2 and above, PL/SQL can include conditional compilation directives • Useful for • Compatibility between releases (10.2 and upwards only) • Trace / Debugging • Testing / Quality Assurance • For example CREATE OR REPLACE PROCEDURE p1BEGIN DBMS_OUTPUT.PUT_LINE ('Before'); $IF $$trace_enabled $THEN DBMS_OUTPUT.PUT_LINE ('Conditional Code');$END DBMS_OUTPUT.PUT_LINE ('After');END;/

  24. Conditional Compilation • By default conditional compilation flag will be NULL SET SERVEROUTPUT ON EXECUTE p1; BeforeAfter • Conditional compilation can be enabled as follows: ALTER PROCEDURE p1 COMPILEPLSQL_CCFLAGS = 'trace_enabled: true' REUSE SETTINGS; EXECUTE p1; BeforeConditional codeAfter

  25. ColumnEncryption

  26. Column Encryption • In Oracle 10.2 and above individual columns can be encrypted • Columns are encrypted transparently to applications • Columns are encrypted on disk • Requires a wallet • Directory containing encryption keys • Password protected • Defined in SQLNET.ORA • Directory must be created manually

  27. Column Encryption • Define wallet in SQLNET.ORA WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/TEST/wallet) )) • Restart instance and open wallet ALTER SYSTEM SET ENCRYPTION WALLET OPENIDENTIFIED BY <password>; • Set up an encryption key ALTER SYSTEM SET ENCRYPTION KEYIDENTIFIED BY <password>;

  28. Column Encryption • Create a table with an encrypted column CREATE TABLE t1( c1 VARCHAR2(10), c2 VARCHAR2(10) ENCRYPT); INSERT INTO t1 VALUES ('ABCDEFGHIJ','ABCDEFGHIJ');INSERT INTO t1 VALUES ('ABCDEFGHIJ','ABCDEFGHIJ'); • Columns are decrypted when queried SELECT c1, c2 FROM t1; C1 C2---------- ----------ABCDEFGHIJ ABCDEFGHIJ

  29. Column Encryption • Columns are encrypted on disk SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO (ROWID,USER,'T1'), DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)FROM t1; File Number Block Number----------- ------------ 4 63ALTER SESSION SET EVENTS '10389 trace name context forever, level 1'; ALTER SYSTEM DUMP DATAFILE 4 BLOCK 63;

  30. Column Encryption • Columns are encrypted on disk tab 0, row 0, @0x1f55tl: 67 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 41 42 43 44 45 46 47 48 49 4acol 1: [52] c0 55 15 73 ec 73 c8 a9 ed b2 6f fa e0 17 c8 be 45 f1 7e 19 7a 6a 49 77 0a 31 83 19 b3 4f b9 78 ef f2 fb 38 7e 57 13 75 a0 fe 98 b7 ed ae d4 a6 78 a4 df 8dtab 0, row 1, @0x1f12tl: 67 fb: --H-FL-- lb: 0x1 cc: 2col 0: [10] 41 42 43 44 45 46 47 48 49 4acol 1: [52] 7d b1 af 57 74 e0 65 48 c9 c3 ec f6 de 2d 31 65 cf 40 eb 54 7e 76 6a a5 5b 67 21 b0 1f 6d 11 44 07 b9 ca 92 b0 30 b0 55 9c 28 46 b9 53 62 69 eb 15 4f 94 50 • Column length changes on disk • Actual lengths not reported by DUMP or VSIZE

  31. Space Management

  32. Online Shrink • In Oracle 10.1 and above, tables and indexes can be shrunk • Must be using Automatic Segment Space Management • Must enable row movement ALTER TABLE t1 ENABLE ROW MOVEMENT; • Must also disable ROWID-based triggers • To shrink table ALTER TABLE t1 SHRINK SPACE; • By default • compacts space • adjusts high water mark • releases space • To shrink dependent objects ALTER TABLE t1 SHRINK SPACE CASCADE;

  33. Online Shrink • In Oracle 10.1 and above can shrink • Tables • Indexes • IOT • IOT Secondary Indexes • Partitions • Subpartitions • Materialized Views • Materialized View Logs • In Oracle 10.2 and above can also shrink • LOB Segments • Function Based Indexes • IOT Overflow Segments

  34. Dropping Empty Datafiles • In Oracle 10.2 and above, empty datafiles can be dropped • Useful in conjunction with online shrink ALTER TABLESPACE test DROP DATAFILE 'test1.dbf'; • Cannot drop non-empty datafiles ORA-03262: the file is non-empty • Cannot drop first file in tablespace ORA-03263: cannot drop the first file of tablespace TEST

  35. Renaming Temporary Files • In Oracle 10.2 and above temporary files can be renamed • For example to rename temp1.dbf to temp2.dbf • Take file offline using: ALTER DATABASE TEMPFILE 'temp1.dbf' OFFLINE; • Move file at operating system level $ mv temp1.dbf temp2.dbf • Rename file using: ALTER DATABASE RENAME FILE 'temp1.dbf' TO 'temp2.dbf'; • Bring file online again using: ALTER DATABASE TEMPFILE 'temp2.dbf' ONLINE;

  36. Trace andDiagnostics

  37. Database / Instance Level Trace • In Oracle 10.2 and above includes new procedures to enable and disable trace at database and/or instance level • New procedures in DBMS_MONITOR package • PROCEDURE DATABASE_TRACE_ENABLE • PROCEDURE DATABASE_TRACE_DISABLE

  38. Database / Instance Level Trace • Examples - database level EXECUTE dbms_monitor.database_trace_enable; EXECUTE dbms_monitor.database_trace_enable (binds=>TRUE); EXECUTE dbms_monitor.database_trace_enable (waits=>TRUE); • Example - instance level EXECUTE dbms_monitor.database_trace_enable (instance_name=>'RAC1); • Note - cannot disable instance level trace in Oracle 10.2.0.1

  39. V$PROCESS_MEMORY • Introduced in Oracle 10.2 • Included in STATSPACK report in Oracle 10.2 and above

  40. V$PROCESS_MEMORY • Example - can be used to verify size of SQL and PL/SQL areas for a process SELECT * FROM V$PROCESS_MEMORYWHERE pid = 26; • Base view is X$KSMPGST

  41. SQL*Plus

  42. SQL*Plus XQUERY • In Oracle 10.2 and above SQL*Plus can run XQuery 1.0 commands against database • For example SQL> SET LONG 200SQL> SET LINESIZE 200SQL> XQUERY FOR $i IN ORA:VIEW ("CIRCUIT") RETURN $iSQL> / • Returns <ROW><CIRCUIT_KEY>SHA</CIRCUIT_KEY><CIRCUIT_NAME>Shanghai</CIRCUIT_NAME><COUNTRY_KEY>CHI</COUNTRY_KEY></ROW> <ROW><CIRCUIT_KEY>BAH</CIRCUIT_KEY><CIRCUIT_NAME>Bahrain</CIRCUIT_NAME><COUNTRY_KEY>BAH</COUNTRY_KEY></ROW> ... 55 item(s) selected

  43. DBMS_OUTPUT maximum output buffer size In Oracle 10.1 and below - 1000000 bytes In Oracle 10.2 and above - unlimited DBMS_OUTPUT • DBMS_OUTPUT maximum line length • In Oracle 10.1 and below - 255 bytes • In Oracle 10.2 and above - 32767 bytes SET SERVEROUTPUT ON DBMS_OUTPUT.ENABLE (1000000); DBMS_OUTPUT.PUT_LINE (...); SET SERVEROUTPUT ON SIZE 1000000 DBMS_OUTPUT.PUT_LINE (...);

  44. Parameters

  45. V$PARAMETER_VALID_VALUES • Introduced in Oracle 10.2 • Returns one row for each valid value for each parameter taking scalar values

  46. V$PARAMETER_VALID_VALUES • E.g in Oracle 10.2 valid values for CURSOR_SHARING parameter are: • FORCE • EXACT • SIMILAR SELECT name, value, isdefaultFROM v$parameter_valid_valuesWHERE name = 'cursor_sharing'ORDER BY ordinal;

  47. V$PARAMETER_VALID_VALUES • View definition SELECT view_definition FROM v$fixed_view_definitionWHERE view_name = 'GV$PARAMETER_VALID_VALUES'; SELECT inst_id, parno_kspvld_values, name_kspvld_values, ordinal_kspvld_values, value_kspvld_values, isdefault_kspvld_valuesFROM x$kspvld_valuesWHERE TRANSLATE (name_kspvld_values,'_','#') NOT LIKE '#%';

  48. X$KSPVLD_VALUES • Introduced in 10.2 • Contains all valid parameter values • Includes supported and unsupported parameters

  49. X$KSPVLD_VALUES • To select valid values for all unsupported parameters use SELECT name_kspvld_values, value_kspvld_values, isdefault_kspvld_valuesFROM x$kspvld_valuesWHERE TRANSLATE (name_kspvld_values,'_','#') LIKE '#%';ORDER BY name_kspvld_values, ordinal_kspvld_values;

  50. Auditing

More Related