620 likes | 781 Views
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
E N D
Oracle 10.2New Features Julian Dyke Independent Consultant Web Version juliandyke.com
A Brief History of Oracle • Continued....
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
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>]);
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
Mandatory Columns • Required in DML Error Logging Table
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
Example: CAR table EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('CAR'.'ERR_CAR');
Example: ERR_CAR table MandatoryColumns OptionalColumns
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
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
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
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
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';
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
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
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;/
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
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
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>;
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
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;
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
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;
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
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
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;
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
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
V$PROCESS_MEMORY • Introduced in Oracle 10.2 • Included in STATSPACK report in Oracle 10.2 and above
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
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
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 (...);
V$PARAMETER_VALID_VALUES • Introduced in Oracle 10.2 • Returns one row for each valid value for each parameter taking scalar values
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;
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 '#%';
X$KSPVLD_VALUES • Introduced in 10.2 • Contains all valid parameter values • Includes supported and unsupported parameters
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;