Oracle Database 11g New Features

Oracle announced the lots of new features which could be part of new release of Oracle 11g Database and I have listed few key features are as follows.

• Query result-set caching as opposite to Data block
• Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library.
• Server side connection pooling (multiple Oracle clients to share a server-side pool of sessions, but USERIDs should match)
• Faster optimizer stats creation
• Interval partitioning, a partitioning scheme that automatically creates time-based partitions as new data is added.
• Interval partitioning, a partitioning scheme that automatically creates time-based partitions as new data is added.
• Virtual columns, columns that are actually functions and similarly, virtual indexes that are based on functions
• REF partitioning, allowing you to partition a table based on the partition scheme of another. Allows you to partition an order_items table based off of the order_date column in an orders table.
• New Oracle11g Advisors - New 11g Oracle Streams Performance Advisor and Partitioning Advisor.
• Faster DML triggers
• Capture/replay database workloads – We can capture the workload in prod and apply it in development
• Enhanced Read only tables
• Table trigger firing order - Oracle 11g PL/SQL will you to specify trigger firing order.
• New table Data Type "simple_integer"
  

I apologize for any typos or spelling errors in my blogs.

-thiru

Oracle DBA Toolbar

Oracle has introduced Oracle Toolbar which helps to access oracle related resource at single click. Visit http://www.oracle.com/technology/toolbar/install/index.html to know more.

Multi table INSERT in Oracle

I have given an example which demonstrates the Oracle’s strength to insert data into multiple tables using single SQL statement

SQL> create table temp1(a number(2),b varchar2(5));

Table created.

SQL> create table temp2(a number(2),b varchar2(5));

Table created.

SQL> insert all
when rownumt <10>=10 then into temp2 values(rownumt, String)
SELECT ROWNUM rownumt,DBMS_RANDOM.STRING('A',5) String FROM DUAL CONNECT BY LEVEL <=20;

20 rows created.

SQL> select * from temp1;

A B
---------- -----
1 yKXDU
2 iQPtH
3 kbBcy
4 lmUoZ
5 JiLBi
6 udQEl
7 HVDzE
8 ULixZ
9 gNUSO

9 rows selected.

SQL> select * from temp2;

A B
---------- -----
10 RYyLv
11 LnkFo
12 gEBUZ
13 TYlxG
14 pvMKv
15 KSiIr
16 Xzkzt
17 ppEpq
18 wCIAY
19 tfGiX
20 nCPeZ

11 rows selected.

SQL>