Every release contains many new features and enhancements that are not highlighted in the marketing material. Oracle assumes all customers will move on to new versions when they are released. Here are few examples of those – New  features failed to spot.

DBMS_SYSTEM.KSDWRT

  • undocumented package
  • installed in all databases
  • Used to write messages to trace files and/or alert log use

DBMS_SYSTEM.KSDWRT
(
DEST NUMBER,    — 1 = Trace File, 2 = Alert Log, 3 = Both
TST  VARCHAR2    — Message
);
eg:
DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’);
DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);
DBMS_SYSTEM.KSDWRT (3, ’Output to both’);

BITAND

  • Initially undocumented built-in function
  • Performs a bit-wise AND between two operators

eg:  SELECT BITAND (42,1) FROM dual;

  • Can be used as basis for OR and XOR functions

DBMS_MONITOR

  • Introduced in Oracle 10.1
  • To enable trace in another session use

DBMS_MONITOR.SESSION_TRACE_ENABLE
(
SESSION_ID NUMBER,    — SID
SERIAL_NUM NUMBER,    — Serial Number
WAITS BOOLEAN,        — Include Waits
BINDS BOOLEAN    — Include Binds
);

  • Waits (event 10046  level 8 ) are enabled by default
  • Binds (event 10046 level 4) are disabled by default
  • To disable trace in another session use

DBMS_MONITOR.SESSION_TRACE_DISABLE
(
SESSION_ID NUMBER,    — SID
SERIAL_NUM NUMBER    — Serial Number
);

  • Can be enabled at database level in Oracle 10.2
  • To enable trace for all database sessions use

DBMS_MONITOR.DATABASE_TRACE_ENABLE
(
WAITS BOOLEAN,    — Include Waits
BINDS BOOLEAN    — Include Binds
INSTANCE_NAME VARCHAR2    — Instance Name
);

Fixed Date

  • Initialization Parameter
  • Useful for deterministic testing
  • In Oracle 8.0 and above can be set dynamically using ALTER SYSTEM
  • To set date only use FIXED_DATE = ‘DD-MON-YY’
  • To set date and time use FIXED_DATE = YYYY-MM-DD-HH24:MI:SS

Flushing the Buffer Cache

  • Introduced in Oracle 10.1
  • Flushes all unpinned buffers from the buffer cache

ALTER SYSTEM FLUSH BUFFER_CACHE;

  • In Oracle 9.0.1 and above the following command has the same effect

ALTER SESSION SET EVENTS
‘IMMEDIATE TRACE NAME FLUSH_CACHE’;

  • Useful for deterministic testing but…
  • After issuing this statement
  • Warm up the cache before testing workloads

Optimizer Environment Variables

  • In Oracle 10.1 and above, optimizer environment variables are externalized at :
  • instance level     – V$SYS_OPTIMIZER_ENV
  • session level     – V$SES_OPTIMIZER_ENV
  • statement level – V$SQL_OPTIMIZER_ENV
  • Use the values in these views when determining why execution plans differ

DBMS_XPLAN

  • Introduced in Oracle 9.2
  • Formats PLAN_TABLE contents generated by EXPLAIN PLAN
  • SELECT * FROM TABLE (dbms_xplan.display);
  • DISPLAY function parameters include TABLE_NAME – name of plan table STATEMENT_ID – statement ID in plan table
  • In Oracle 10.1 and above

New DISPLAY_CURSOR function, By default displays plan for last statement executed in session
SELECT * FROM TABLE (dbms_xplan.display_cursor);

V$SESSION_WAIT_HISTORY

  • Introduced in Oracle 10.1
  • Externalises last 10 wait events for each session
  • Similar information to V$SESSION_WAIT – but much more user friendly

Renaming Database Objects

  • To rename a table:
  • RENAME oldname TO newname;
  • To rename an index
  • ALTER INDEX oldname RENAME TO newname;
  • In Oracle 9.2 and above to rename a column
  • ALTER TABLE t1 RENAME COLUMN oldname TO newname;
  • In Oracle 9.2 and above to rename a constraint
  • ALTER TABLE t1 RENAME CONSTRAINT oldname TO newname;
  • In Oracle 10.1 and above to rename a tablespace
  • ALTER TABLESPACE  oldname RENAME TO newname;

Resumable Statements

Oracle 9.0.1 and above long running operations encountering out of space errors can be resumed
Resumable operations include

  • Queries
  • DML Statements
  • SQL*Loader operations
  • Import operations
  • DDL statements

Out of space errors include

  • Tablespace full
  • Maximum number of extents reached for object
  • Tablespace quota exceeded for a user

When resumable space allocation is enabled, Operation suspends if an out of space error occurs. Details of the error are written to DBA_RESUMABLE

  • DBA can optionally be alerted
  • DBA can either  fix the error
  • Operation automatically resumes execution when error is fixed
  • If a further error is detected, operation will suspend again

eg:
Resumable space allocation is enabled
ALTER SESSION ENABLE RESUMABLE
NAME ‘Batch Update’ TIMEOUT 3600;

INSERT INTO tab2
SELECT * FROM tab1;

Out of space error occurs
ORA-01653: unable to extend table US01.T2 by 210 in tablespace ‘TS99’

  • Error is written to alert log
  • DBA queries DBA_RESUMABLE view for details of suspended operation
  • DBA fixes error condition
  • Suspended operation resumes automatically
  • Operation completes successfully
PrintFriendly

Viewers of this post also viewed...