being a blind eyed Oracle instructor – system partitioning in 11g

Tonight I met with Laurent Schneider and we had a couple of beers together.

 We chatted about God and the world and we had lots of fun.

Amoung others we talked about things in Oracle 11g which do not really work yet and¦or are undocumented or documented wrong.
I was lucky to be able to tell him about things which he did not know about yet
like the not yet documented parameter DDL_LOGGING, see my post here.  I think that Oracle development just forgot to document it.

It is hard to catch up with Laurent sometimes!

I am the reviewer of Laurent’s yet to be published book on 11g SQL for advanced developers and DBAs and he demonstrated some features he had filed a BUG for in the last days.

Of course Oracle 11g was a big topic and we have found out some interesting things about system partitioning ( it is more that he found out than me, altough I teach the stuff to people in my New Features courses !!!!)

He  talked about something in one of the chapters in his book which he had found about system partitioning in 11g and which did not work. 

I got suspicious because I was so sure that it was possible, since it was something I teach to my students in my 1day 11g New Features Overview classes.
But I had to back down and confess again that I did not really verify every single bit of code of the Oracle Coureseware.

This is why I want to share the lecture I had to take tonight (luckily with a couple of beers!):

Oracle has introduced a number of new partitioning algorithms with release 11g.  One of these new features is called SYSTEM PARTITIONING. This is very handy if you want to partition a table but cannot make uo your mind about a partitioning key for some reason. Maybe there is only one column holding xml data in the table or what so ever.

The current version of the courseware for the 11g New  Features Courses says that the following syntax is valid and Laurent showed me that it is not:

– I create a table which is system partitioned:

SYS AS SYSDBA @ orcl SQL> create table lutz.t3 (col1 number, col2 number)
  2  partition by system
  3  (partition p1);
Table created.

– I create a bind varable (first time a number second time a string)
– which I want to use to tell Oracle which partition to user for an INSERT
–and assign it the value 1 and try an INSERT:

SYS AS SYSDBA @ orcl SQL> var par number
SYS AS SYSDBA @ orcl SQL> exec :par:=1
SYS AS SYSDBA @ orcl SQL> insert into lutz.t3 partition (:par) values (1,2);
insert into lutz.t3 partition (:par) values (1,2)
                               *
ERROR at line 1:
ORA-14108: illegal partition-extended table name syntax

– I try another time with a bid variable of type string:

SYS AS SYSDBA @ orcl SQL> var par varchar2(2)
SYS AS SYSDBA @ orcl SQL> exec :par:=’p1′
SYS AS SYSDBA @ orcl SQL> insert into lutz.t3 partition (:par) values (1,2);
insert into lutz.t3 partition (:par) values (1,2)
                               *
ERROR at line 1:
ORA-14108: illegal partition-extended table name syntax

– I try it for a last time and now I try to use the partition number directly:

SYS AS SYSDBA @ orcl SQL> insert into lutz.t3 partition (1) values (1,2);

insert into lutz.t3 partition (1) values (1,2)
                               *
ERROR at line 1:
ORA-14108: illegal partition-extended table name syntax

The same error pops up!

And here is the explanation of the error:

[oracle@rhas4 ~]$ oerr ora 14108
14108, 00000, “illegal partition-extended table name syntax”
// *Cause:  Partition to be accessed may only be specified using its name.
//          User attempted to use a partition number or a bind variable.
// *Action: Modify statement to refer to a partition using its name

You can only reference a system partition with its name not by its number or even via a bind variable! At least not in 11.0.6!!!

Sorry people but I just could not yet test all of the more than 500 New Features in the courseware. But I will keep on hitting the road, for sure!

=;-)