Laurent Schneider

One year ago I started writing a book on SQL. Writing a book is an amazing amount of work and I am glad I got helped from my five reviewers Chen, Andrew, Tom, Marco and Lutz.

It will ship in December 2008.

http://amazon.com/Advanced-Oracle-SQL-Programming-Focus/dp/0977671585

I wrote about dba_users changes in 11g .

When spooling alter user commands in 11g, it is important to understand the mechanism. Oracle 11g supports both sensitive and insensitive passwords.

When issuing an CREATE/ALTER USER IDENTIFIED BY PASSWORD, both the insensitive and the sensitive hashes are saved.

SQL> create user u identified by u;
User created.
SQL> grant create session to u;
Grant succeeded.
SQL> connect u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect u/u
Connected.

Per default only the proper case works

SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> connect u/U
Connected.
SQL> conn u/u
Connected.

When sec_case_sensitive_logon=false, both uppercase and lowercase passwords work (10g behavior).

When issuing a create user identified by values, you must chose if you want to have both passwords, only the case insensitive or only the case sensitive.

SQL> select password,spare4 from user$ where name='U';

PASSWORD
------------------------------
SPARE4
--------------------------------------------------------------
18FE58AECB6217DB
S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934DABBB809A4

The hashes are in USER$.

SQL> alter user u identified by values '18FE58AECB6217DB';
User altered.
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When only the 10g oracle hash is used as a value, the password is case insensitive whatever the setting of sec_case_sensitive_logon is.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4';
User altered.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied

When only the 11g oracle hash is used as a value, the password is case sensitive and if the setting of sec_case_sensitive_logon is on false, the login failed as there is no 10g string. This setting is probably the most secure setting as the 10g string is not saved in USER$.

SQL> alter user u identified by values 
'S:8B1765172812D9F6B62C2A2B1E5FEF203200A44B4B87F9D934-
DABBB809A4;18FE58AECB6217DB';
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> conn u/u
Connected.
SQL> conn u/U
Connected.

When using both hashes, switching back and forth to 11g mechanism is possible.

In Oracle11g the alert log is an XML file. The old style alertSID.log is created out of the log.xml for backward compatibility only. However, some exciting enhancement are not noticable in the old one.

$ tail -3 alert*.log
Mon Feb 04 15:52:38 2008
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;

If I check in the xml file, I get more info about the execution time, which contains milliseconds

$ adrci exec="show alert -term"|tail -4
2008-02-04 15:52:38.366000 +01:00
ALTER SYSTEM SET recyclebin='OFF' SCOPE=SPFILE;
2008-02-04 15:52:42.101000 +01:00
ALTER SYSTEM SET recyclebin='ON' SCOPE=SPFILE;

This timestamp could be really usefull !

There is also a command -tail for adrci, but it outputed the whole log for some unkown reason

The 11g OCP certification should be available this year. You can register for the production exam 1Z0-050 New Features on Prometrics and pass the exam on Mon Feb 18th or later, the OCA exams 1Z1-051 SQL Fundamentals and 1Z1-052 Admin I are in beta and the OCP exam 1Z1-053 Admin II is planned for early 2008 (well, the OCA exams are planned for late 2007, so do not rely on this timeframe).
Check the official page :
Oracle Education 11g certification

The 11g OCM is planned for a later date (whatever that means). Well, I am no longer going to wait for the 10g OCM upgrade for 9i OCM… I am not going to do a certification for an older release since 11g exams are available. I will rather do the 11g ocp this year. As I wrote earlier, the 10g OCM was planned for late 2004…

Online Patching : you can apply or roll back online patches while the RDBMS instance is running

1) download an interim patch for 11g, f.ex. dummy patch 6198642
2) unzip p6198642_111060_LINUX.zip
3) cd 6198642
4) $ORACLE_HOME/OPatch/opatch apply -silent -connectString LSC08 -runSql

Invoking OPatch 11.1.0.6.0

Oracle Interim Patch Installer version 11.1.0.6.0
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /opt/oracle/product/11/db_4
Central Inventory : /opt/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.0
OUI version       : 11.1.0.6.0
OUI location      : /opt/oracle/product/11/db_4/oui
Log file location : /opt/oracle/product/11/db_4/cfgtoollogs/
    opatch/opatch2007-12-05_21-23-42PM.log

RollbackSession rolling back interim patch '6198642' from OH
    '/opt/oracle/product/11/db_4'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory 
    and will patch the local system only.

Please shutdown Oracle instances running out of this 
    ORACLE_HOME on the local system.
(Oracle Home = '/opt/oracle/product/11/db_4')

Is the local system ready for patching? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
Backing up files affected by the patch '6198642' for restore. 
    This might take a while...
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/
    scripts/pre -rollback 6198642 ':

Return Code = 0

Patching component oracle.rdbms, 11.1.0.6.0...
Copying file to "/opt/oracle/product/11/db_4/cpu/CPUDummy2007/
    catcpu.sql"
RollbackSession removing interim patch '6198642' from inventory

---------------------------------------------------------------
This is a dummy patch for testing only
---------------------------------------------------------------
Execution of 'sh /opt/oracle/product/11/db_4/.patch_storage/
    6198642_May_07_2007_00_50_36/original_patch/custom/scripts/
    post -rollback 6198642 ':

Return Code = 0

Running the "apply" sql script "/opt/oracle/product/11/db_4/cpu/
    CPUDummy2007/catcpu.sql" with reference to 'patchmd.xml' file 
    for the patch "6198642"...

The local system has been patched and can be restarted.

OPatch succeeded.

OPatch did run the necessary script (catcpu for Dummy2007) on the various instances (LSC08). It needed only one step and 34 seconds on my notebook. This patch is a dummy patch, let’s wait for CPU January to see if it is online applicable

One may pretend bigfile tablespace is bad, because you cannot backup/restore in parallel.

SQL> create bigfile tablespace big datafile size 1g;

Tablespace created.

RMAN> configure device type disk parallelism 4;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE 
  TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace big section size 100m;

Starting backup at 30.11.2007 07:23:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=42 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=41 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 30.11.2007 07:23:37
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 30.11.2007 07:23:41
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 30.11.2007 07:23:45
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_4: starting piece 4 at 30.11.2007 07:23:48
channel ORA_DISK_1: finished piece 1 at 30.11.2007 07:23:49
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_1_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 30.11.2007 07:23:53
channel ORA_DISK_2: finished piece 2 at 30.11.2007 07:23:53
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_2_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 30.11.2007 07:23:57
channel ORA_DISK_3: finished piece 3 at 30.11.2007 07:23:57
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_3_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:12
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 30.11.2007 07:24:01
channel ORA_DISK_1: finished piece 5 at 30.11.2007 07:24:01
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_5_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 30.11.2007 07:24:05
channel ORA_DISK_2: finished piece 6 at 30.11.2007 07:24:05
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_6_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 30.11.2007 07:24:09
channel ORA_DISK_4: finished piece 4 at 30.11.2007 07:24:09
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_4_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:21
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 115201 through 128000
channel ORA_DISK_4: starting piece 10 at 30.11.2007 07:24:13
channel ORA_DISK_1: finished piece 8 at 30.11.2007 07:24:13
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_8_1 
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007
  name=/oradata/LSC08/datafile/o1_mf_big_3nzbd7h5_.dbf
backing up blocks 128001 through 131072
channel ORA_DISK_1: starting piece 11 at 30.11.2007 07:24:18
channel ORA_DISK_2: finished piece 9 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_9_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:09
channel ORA_DISK_3: finished piece 7 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_7_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_4: finished piece 10 at 30.11.2007 07:24:18
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_10_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_1: finished piece 11 at 30.11.2007 07:24:19
piece handle=/opt/oracle/product/11/db_4/dbs/01j2avjk_11_1
  tag=TAG20071130T072332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30.11.2007 07:24:19

This works fine

SECTION SIZE requires 11gR1 or later

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.

One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.

Unfortunately, the feature has not been implemented in 11g Release 1.

As you can see, the CREATE EDITION command does not work :

SQL> create edition v1;
create edition v1
       *
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist

The error messages ORA-38801 to ORA-38816 refer to EDITIONS.

SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE                         YES

SQL> alter session set edition=ora$base;

Session altered.

Apparently the feature is implemented, but not ready to be used. Be patient …

I just read on Renaps Blog that Oracle 11g for Windows is out.

Oracle Database Download

Niall Litchfield just wrote about 11g availability on 64bits linux.

Happy Birthday Niall
Download Oracle Database

Today I opened two SR about flashback archive in 11g. In one of them, I complained that user SCOTT was not allowed to create a flashback archive. In the doc that I downloaded a few weeks ago I read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

So as I was getting an ORA-55611, I opened a SR. The support engineer pointed me to the online documentation where I was astonished to read :
Prerequisites
You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA.

Well, Read The Fine Online Manual !!!

The second tar is related to long retention (about the age of the earth)

SQL> alter flashback archive fba01 
  modify retention 4106694757 year;

Flashback archive altered.

SQL> select retention_in_days
  from DBA_FLASHBACK_ARCHIVE;
RETENTION_IN_DAYS
-----------------
                1

I tried this in 11g
TABLE T

X
123
-1.2e-3
abc

select x, 
  to_number(
    xmlquery('number($X)' 
      passing x as x 
      returning content)) n 
from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

it is quite a common task to extract numbers from varchar2 and to dig out poor quality data.

select x, to_number(x) from t;
ERROR:
ORA-01722: invalid number

A well-known PL/SQL approach would be to use exception. Ex:

create or replace function f(x varchar2) 
return number is 
begin return to_number(x); 
exception when others then return null; 
end;
/
select x, f(x) n from t;
X                N
------- ----------
123            123
-1.2e-3     -.0012
abc

another approach in plain sql could involve CASE and REGEXP

select x,
case when
regexp_like(x,
‘^-?(\+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
then to_number(x)
end n
from t;
X N
——- ———-
123 123
-1.2e-3 -.0012
abc

I am always delighted to read the top features by Arup Nanda.

He started his 11g series : Oracle Database 11g: The Top Features for DBAs and Developers

There are many partitioning enhancements. The most exciting feature for me is the INTERVAL partitioning. A huge cause of downtime and waste of storage is the range partitioning. In 10g and before, a partitioning by dates required that the partition are defined before values are inserted.

Now we have automatic partition creation

create table t(d date) 
partition by range(d) 
interval(interval '1' month) 
(partition p1 values less than (date '0001-01-01'));

One partition must be created manually, here the partition will contain all dates from 1-JAN-4712BC to 31-DEC-0000 (which is not a legal date by the way)

There is also new syntax to query the partition

SQL> insert into t values (date '2000-01-10');

1 row created.

SQL> insert into t values (date '2000-01-20');

1 row created.

SQL> insert into t values (date '2000-03-30');

1 row created.

SQL> select * from t partition for (date '2000-01-01');
D
-------------------
10.01.2000 00:00:00
20.01.2000 00:00:00

Note the syntax can be used in any form of partitioning. Here in a list-list composite

SQL> create table t(x number, y number) 
  partition by list(x) 
  subpartition by list(y) 
    subpartition template (
      subpartition sp1 values(1),
      subpartition sp2 values(2)) 
  (partition values(1), partition values(2));

Table created.

SQL> insert into t values(1,2); 
1 row created.

SQL> select * from t subpartition for (1,2);
         X          Y
---------- ----------
         1          2

Ok, one more feature Arup introduced is the REF partitioning, where you have a schema with both the parent and child tables partitioned, and you want to partition on a column of the parent table that is not in the child table (as you had bitmap join indexes, you have now ref partitions). Check it on his site.

Finally Arup explained SYSTEM partitioning, which is not inconceivable, but will hardly be used.

Imagine you have a table containing just one single LOB column, and a LOB cannot be used as a partition key.

SQL> create table t(x clob)
  partition by system (
    partition p1, 
    partition p2, 
    partition p3, 
    partition p4);

Table created.

So far this seems fine. So what the problem? You cannot insert in that table!

SQL> insert into t values(1);
insert into t values(1)
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable
must be used for DMLs on tables partitioned by the
System method

so you must define in which partition you want to add data. For example round robin. Or random. Whatever.

SQL> insert into t partition (P1)  values ('x');

1 row created.

SQL> insert into t partition (P2)  values ('y');

1 row created.

If you want to use bind variable, you can use dataobj_to_partition

SQL> select object_id 
  from user_objects 
  where object_name='T' 
    and subobject_name is not null;
 OBJECT_ID
----------
     55852
     55853
     55854
     55855

SQL> var partition_id number
SQL> exec :partition_id := 55852

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.
SQL> exec :partition_id := 55853

PL/SQL procedure successfully completed.

SQL> insert into t 
  partition (dataobj_to_partition("T",:partition_id))
  values ('x');

1 row created.

Actually, SYSTEM partitioning is misleading, YOU are responsible for choosing the partition in which you want to insert, not the system

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables.

Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g…

Well, with 11g, you can create a flashback archive, and it will save all change until end of retention (many years if you want).

Here it is :

SQL> connect / as sysdba
Connected.
SQL> create tablespace s;

Tablespace created.

SQL> create flashback archive default fba01 tablespace s 
  retention 1 month;

Flashback archive created.

SQL> connect scott/tiger
Connected.
SQL> create table t(x number) flashback archive;

Table created.

SQL> host sleep 10

SQL> insert into t(x) values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                  337754

SQL> update t set x=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t as of scn 337754;
         X
----------
         1

SQL> alter table t no flashback archive;

Table altered.

SQL> drop table t;

Table dropped.

SQL> select FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS,
  STATUS from DBA_FLASHBACK_ARCHIVE;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS STATUS
---------------------- ----------------- -------
FBA01                                 30 DEFAULT

SQL> connect / as sysdba
Connected.
SQL> drop flashback archive fba01;

Flashback archive dropped.

SQL> drop tablespace s;

Tablespace dropped.

note that a month is 30 days. If you try to create a flashback archive in a non-empty tablespace you may get
ORA-55603: Invalid Flashback Archive command
which is not a very helpful message

This is a very neat feature in 11g.

I have a script called foo.sql

create table t(x number primary key);
insert into t(x) values (1);
insert into t(x) values (2);
insert into t(x) values (2);
insert into t(x) values (3);
commit;

It is eyes-popping that this script will return an error, but which one?

Let’s errorlog !

SQL>set errorl on
SQL> @foo

Table created.

1 row created.

1 row created.

insert into t(x) values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

1 row created.

Commit complete.

SQL> set errorl off
SQL> select timestamp,script,statement,message from sperrorlog;
TIMESTAMP  SCRIPT  STATEMENT
---------- ------- ---------------------------
MESSAGE
---------------------------------------------------------
11:18:56   foo.sql insert into t(x) values (2)
ORA-00001: unique constraint (SCOTT.SYS_C004200) violated

There is also a huge bonus

You can use it with 9i and 10g databases too! Only the client must be 11g. To download the 11g client only, go to Oracle E-Delivery Website

Even small, this is one of my favorite new features!

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g.

select username,password 
from dba_users 
where username='SCOTT';
USERNAME PASSWORD
-------- ------------------------------
SCOTT

select name,password 
from sys.user$ 
where name='SCOTT';
NAME  PASSWORD
----- ------------------------------
SCOTT F894844C34402B67

on the one hand, it is good for the security.

On the other hand, it is a huge change which is not documented (I immediately sent comments to the Security and Reference book authors) and it will make a lot of script failing (scripts that use to change the password to log in and change it back to the original value afterwards).

Protecting the hash is extremely important, check your scripts for 11g compatibility!

follow this link to access the certification matrix, which has been updated for 11g
http://otn.oracle.com/support/metalink
as of today, 24-AUG-2007, here is the certification matrix for Linux x86

OS	Product	Status
SLES-10	11g	Certified
Red Hat Enterprise AS/ES 5	11g	Certified
Red Hat Enterprise AS/ES 4	11g	Certified
Oracle Enterprise Linux 5	11g	Certified
Oracle Enterprise Linux 4	11g	Certified

http://www.oracle.com/technology/software/products/database/index.html

I read on this site that Oracle11g GA is for 2007 August

http://www.dba-oracle.com/t_oracle_11g_hits_the_streets.htm

watch the launch event live
http://www.oracle.com/pls/ebn/live_viewer.main?p_shows_id=5717957

Lutz announced his 11g courses in Switzerland in this postOracle University Switzerland announces brand new Oracle 11g courses

Today is a good day, I have been accepted to the beta program and will be able to download 11gR1 beta 4 shortly.

There is a Confidential Disclosure Agreement, so do not expect to read more on 11g here before the production release.

My blog articles about 11g in OpenWorld 2006:
11g New Features
11g New Features part II

First I apologize for the confusion with previous post of mines, where I defined row generators as pivot table.

Here I talking about transposing rows into columns PIVOT and transposing columns into rows UNPIVOT

Ok, back in 2003, I had a data model were all attributes were stored in same table (a miracle of java tools generating data models)

select person.name,
  property.type,
  property.value
from person, property
where 
  person.id=property.person;

NAME TYPE     VALUE
---- -------- ------
John gender   male
Mary category junior
Mary gender   female

for datawarehousing purpose, I had to get the attributes, if set, as a column, so I started with outer joining for each attribute (they were plenty, not just two)

select name, 
  gender.value gender, 
  category.value category
from person,
  property gender,
  property category
where
  person.id = gender.person(+) 
  and gender.type(+)='gender'
  and person.id = category.person(+)
  and category.type(+)='category';

NAME GENDER CATEGO
---- ------ ------
Mary female junior
John male

By using the Tom Kyte method described on asktom, I could have used aggregation.

select name,
  max(decode(type,'gender',value)) gender,
  max(decode(type,'category',value)) category
from person , property
where person.id = property.person (+)
group by name;

NAME GENDER CATEGO
---- ------ ------
John male
Mary female junior

To do the opposite, I posted once in a forum

select deptno,
  decode(x,1,'DNAME','LOC') type,
  decode(x,1,dname,loc) value
from dept,
 (select 1 x from dual union all
  select 2 from dual);

    DEPTNO TYPE  VALUE
---------- ----- --------------
        10 DNAME ACCOUNTING
        20 DNAME RESEARCH
        30 DNAME SALES
        40 DNAME OPERATIONS
        10 LOC   NEW YORK
        20 LOC   DALLAS
        30 LOC   CHICAGO
        40 LOC   BOSTON

Well, in the next generation database, this is going to be easier, maybe.

With the introduction of pivot keyword, the following should work

select name, type, value
from person , property
<b>pivot</b> (max(value) 
for type in (
  'gender' as gender,
  'category' as category))
where person.id = property.person (+);

and with the unpivot keyword

select * 
from dept 
<b>unpivot</b> (value 
for type in (
  dname as 'DNAME',
  loc as 'LOC'));

It would be interesting to compare the execution plans !

I have been following 2 sessions with Juan Loaiza about new features.

A very few of them here :

SQL> create edition v1_0;
SQL> alter session set edition=v1_0;

You can create a versioning of your objects, so you will be able to have many versions of the same package at the same time. It is a bit similar to workspace, where you have many versions of the data at the same time. It is very profitable in release management and availability.

About the cache result hint, you can also use
alter session cache results;, which can be useful for lookup table. As commented on a previous post, you can still use the old alter table cache; or the keep buffer pool. It depends.

Performance enhancement in the RAC interconnect.

Server side connection pooling. An additional layer to the shared server, to enable faster [actually to bypass] session creation.
edit: check Database Resident Connection Pooling

Securefiles. This is a good one. It is a huge improvement to BLOB. Faster, with compression, encryption.

Partition interval. This is a marvelous one ! You can now partition by date, one partition per month for example, with automatic partition creation.

Rman backup can bypass undo. Undo tablespaces are getting huge, but contain lots of useless information. Now rman can bypass those types of tablespace. Great for exporting a tablespace from backup.

Capture/replay workload. Sounds appealing. You can capture the workload in prod and apply it in development.

Many other enhancement.

Juan expect to see a Petabyte single database, with a terabyte of memory and 1000 cpus before the end of the decade

this was a 2006 announcement, not all features announced have been implemented in prod

The presentation of Bryn Llewellyn was a good one! He talked about the 11g new features. Well, he said it is maybe not named 11g and maybe none of the features will be available. But since the beta is already available, I guess all of the features he talked will be available. It is very rich, and went fast, so I apologize for not relating all of them, or any mistakes that I include.

The result set caching. My favorite. This sounds like a huge performance improvement for many applications. I have an application which is doing a select count(*) at every screen. It is the bottleneck. A possible workaround is to use query rewrite and materialized view. Now in 11g, you just add a /*+result_cache*/ hint, so the result of the query will be cached. Justin Cave asked about the integrity. The answer is : there is no compromise of the integrity. There is an aggressive cache invalidation, so whatever DML happens on one dependent table, the cache result is invalidated. As for the materialized view rewrite in 9iR1, I guess there will be quite a few P1 bug for that hint in 11.1.0, but still it is a great announcement. This hint also works for PL/SQL functions, the declaration of the function can include result_cache relies on (emp)

Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.

Regular expression improvements. This is rather a SQL improvement, glad Bryn did presented it too. One of this is a new function called regexp_count. All of the existing functions have been improved. I love that kind of new features, because they offer new way of solving queries. I hope I will be able to reanswer Re: Can any one retrive Nth column from a table with one less function in 11g.

Using the => syntax in sql. In 11g you can use select f(x=>1) from dual, which is a goodie.

Fine grained access control to the TCP packages family (utl_tcp, utl_smtp, …). This is a good one. You can grant execute on the package only for specific IP addresses.

Read-only table. Ok, it reminds me one thread how-to-set-a-table-in-read-only-mode. Glad to see this one too!

Fast triggers. Nothing to do about it, triggers are just faster in 11g.

Intra-Unit inlining. This is the 3rd level of PLSQL_optimize_level.

Trigger order. You have many triggers on one table, you can now specify in which order they will be fired.

Compound trigger. A new powerfull type of trigger, which enables you to specify a declarative section (and to have a kind of package global variable), a before procedure, an after each row procedure, and an after procedure. All this in a single trigger.

Create disabled trigger. It is now possible to create a disabled trigger. You can create an invalid trigger, and compile it later, but if not used appropriately by the developers, it may create a new kind of invalid objects in the databases. Good for the developer. Not necessarily pleasant for the DBA.

Using sequence in PL/SQL without DML. It is possible to use x:=s.nextval in plsql, instead of selecting from dual.

PLSQL_Warning. You can compile a procedure with PLSQL_WARNING=enable all, to get errors when others exceptions are caught and not raised. Tom Kyte does appreciate this one

A special mention to the super keyword. I am enthusiastic but somehow skeptic about the implementation of inheritance in PL/SQL. Well, I am looking forward from reading more about this.

Faster and real native compilation. Improvement have been done to bypass the filesystem and the external C compiler in this release.

DBMS_SQL improvement. Ways to bypass the 32k limitation. I have not understand very well this change.

The continue keyword. You can use the continue keyword in your plsql loops, as you would do in another languages.

disclaimer: the post was based on OOW 2006 announcement and may not reflect the production release of 11g