Database Resident Connection Pooling (DRCP) is pool of connections. Establishing connection is very expensive process so DRCP solves it by sharing such connections between users. It’s important for “stateless” applications which are establishing connections very often (Web based).

DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.

Continue reading →

This article presents how to use new operators PIVOT, UNPIVOT in Oracle 11g

• PIVOT is used to write cross-tabulation queries that rotate column values into new columns and aggregates data. It increases number of columns :).

PIVOTING quarter

Continue reading →

This article presents new feature 11g Direct NFS Client.

Oracle database kernel implements NFS version 3 client protocol. It means Oracle rdbms is not using anymore OS NFS drivers to communicate with NFS V3 NAS devices but own implementation. This results in consistent performance for many platforms and easy setup. It eliminates scenario where data is cached on OS level and in SGA. Oracle Direct NFS client is using asynchronous I/O and supports up to 4 parallel network paths (no need for bonded expensive network interfaces).

This presentation is based on following article:

Install Oracle 11.2.0.3 on OEL6

Continue reading →

This article presents new feature of 11g Virtual column.

Virtual column – a column that is not stored on disk. Value of the column is calculated by expression or function.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

GENERATED ALWAYS and VIRTUAL keywords are optional

Example:

--create deterministic function used by virtual column
CREATE OR REPLACE FUNCTION add_fnc(p_id NUMBER)
RETURN NUMBER
deterministic
IS
BEGIN
  RETURN p_id + 3;
END;
/
 
CREATE TABLE test_tbl
(
  static_col   NUMBER,
  --virtual column using simple syntax expression
  vir_col1 NUMBER AS (static_col + 1),
  --virtual column using long syntax expression
  vir_col2 NUMBER generated always AS (static_col+2)        virtual, 
  --virtual column long syntax with function
  vir_col3 NUMBER generated always AS (add_fnc(static_col)) virtual
)
--partition by virtual columns
PARTITION BY RANGE(vir_col2)
subpartition BY hash(vir_col1) subpartitions 4
(PARTITION p1 VALUES less than(maxvalue));
 
--add primary key using virtual column
ALTER TABLE test_tbl add constraint test_tbl primary key(vir_col1);

Continue reading →

There are 3 new options in 11g:

• compound triggers
• order of execution triggers
• enable/disable at creation time

Continue reading →

This article presents new features of 11g

• sequence enhancements
• CONTINUE in PL/SQL
• named and mixed notation in SQL
• read only table

Continue reading →

This article presents new features of 11g for regular expressions:

• access to the n-th subexpression in REGEXP_INSTR and REGEXP_SUBSTR functions
• new function REGEXP_COUNT returns number of times a pattern match is found in an input string

Continue reading →

This article presents new feature of 11g for pfile/spfile

• New FROM MEMORY clause for CREATE PFILE/SPFILE

CREATE PFILE [='pfile_name'] 
FROM { { SPFILE [='spfile_name'] } | MEMORY };

CREATE SPFILE [='pfile_name']
FROM { { PFILE [='spfile_name'] } | MEMORY };

• Instance parameters written to alert.log are in better format to facilitate cutting and pasting
• COMPATIBLE must be set to 11.0.0.0 or higher

Continue reading →

This article presents new features of 11g for temporary tablespaces.

• Locally managed TEMP tablespaces can be shrink online (useful after big sorts)
• Shrink can be done on TABLESPACE or TEMPFILE level

--shrink tablespace to smallest possible size
ALTER TABLESPACE temp SHRINK SPACE;

--shrink tablespace and keep only 100M
ALTER TABLESPACE temp SHRINK KEEP 100M;

--shrink tempfile to smallest possible size
ALTER TABLESPACE temp SHRINK TEMPFILE 'temp01.dbf';

--shrink tempfile and keep only 100M
ALTER TABLESPACE temp SHRINK TEMPFILE 'temp01.dbf' KEEP 100M;

Continue reading →

This article presents new features of 11g related to locking.

• DDL commands can wait for DML locks to be released:
  • DDL_LOCK_TIMEOUT initialization parameter  0..100000 seconds
• New WAIT[timeout] clause for LOCK TABLE command
• Exclusive locks(X) switch for shared exclusive for following commands

  • CREATE INDEX ONLINE

  • CREATE MATERIALIZED VIEW LOG

  • ALTER TABLE ENABLE CONSTRAINT NOVALIDATE

Continue reading →