Ok, so what happened up there on stage...

I gave an 11g for DBA's "new feature" talk at UKOUG.  It was going pretty good - until...  Well, let me back up and start at the beginning.  I began my talk with:

You are going to see for the first time ever on screen a series of scripts.  I have not used these before in public - live.  Fingers crossed, let's hope for the best.

I shouldn't have done that - I jinxed myself :)

Everything was going great - until I got to some partitioning examples, interval partitioning in particular.  The script goes like this...

Ok, there is this new feature with partitioning called interval partitioning.  If you use this - then Oracle will automatically create new partitions based on your template, your description of how the partitions should be created.  This means you do not have to preallocate your partitions for a rolling window anymore.  For example, suppose you have an audit trail range partitioned by month - at the end of each month, you have to make sure you create the partition for NEXT month to avoid any errors.  With interval partitioning you can tell us to create a new partition on the fly as needed - without DBA intervention.  For example:

ops$tkyte%ORA11GR1> create table audit_trail
  2  ( ts    timestamp,
  3    data  varchar2(30)
  4  )
  5  partition by range(ts)
  6  interval (numtodsinterval(1,'day'))
  7  store in (users, example )
  8  (partition p0 values less than
  9   (to_date('23-feb-2007','dd-mon-yyyy'))
 10  )
 11  /
Table created.

 That creates a range partitioned table with everything having a TS attribute value less than 23-Feb-2007 stored in partition P0.  Further, we desire each new partition automatically added by Oracle to contain 1 days worth of data (numtodsinterval is a function that turns a number into a day to second interval). 

In order to demonstrate that Oracle does "logical things" here, I will insert a date in the future with regards to 23-Feb-2007, please note how Oracle did not create dozens of partitions for all of the interior values - it creates the MINIMAL set of partitions needed to contain our data:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '02-jun-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'AUDIT_TRAIL'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0         USERS      TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201   EXAMPLE    TIMESTAMP' 2020-06-03 00:00:00'

 and then another date further out in the future, we'll use this to show that - even though it looks like all of the data between the 3rd of June and 15th of Sept will go into it - it won't:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '16-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'AUDIT_TRAIL'
  4   order by partition_position;

two PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0         USERS      TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201   EXAMPLE    TIMESTAMP' 2020-06-03 00:00:00'
SYS_P202   EXAMPLE    TIMESTAMP' 2020-09-17 00:00:00'

Right now, it might appear that anything between 3-jun and 16-sep would go into SYS_P202, but if we insert a date in between the existing dates:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( '11-sep-2007', 'xx' );
1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'AUDIT_TRAIL'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0         USERS      TIMESTAMP' 2007-02-23 00:00:00'
SYS_P201   EXAMPLE    TIMESTAMP' 2020-06-03 00:00:00'
SYS_P203   USERS      TIMESTAMP' 2020-09-12 00:00:00'
SYS_P202   EXAMPLE    TIMESTAMP' 2020-09-17 00:00:00'

We can see that Oracle created SYS_P203 - it will fill in the interior partitions as needed...

It was at this point I received a question from the audience.  It was simply:

Why are your range high values in the year 2020?

Indeed.  I had not noticed that.  I pointed out right there and then "Hey, this is the first time after all...", but I did not see immediately what I had done.  I knew what I changed in my scripts (I used to use SYSDATE in the example, but as time marches on - I'd prefer to have static dates so I can have a predicable experience).  But it was not immediately obvious - so I said "Watch my blog next week, I'll write about it"

Which brings us here - what happened?

A few people pointed it out for me after the session - it was the use of the default Oracle DATE format to insert a string into a TIMESTAMP!  On my system, the NLS settings were:

ops$tkyte%ORA11GR1> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

 See that NLS_TIMESTAMP_FORMAT - when applied to 11-sep-2007 for example, it becomes:

• 11 for the DAY
  
• -
  
• sep for the MONTH
  
• -
  
• 20 for the RR year - which is 2020
  
• (space is ignored in the format, unless you use fx - for Format eXact)
  
• 07 for the HOUR

We can see this easily:

ops$tkyte%ORA11GR1> create table t ( x timestamp, y timestamp );
Table created.

ops$tkyte%ORA11GR1> insert into t values ( '23-feb-2007', to_date( '23-feb-2007' ) );
1 row created.

ops$tkyte%ORA11GR1> select * from t;

X                                   Y
----------------------------------- ------------------------------
23-FEB-20 07.00.00.000000 AM        23-FEB-07 12.00.00.000000 AM

 When we convert a string directly into the timestamp - we get 23-feb-2020, hour 7am.  If we use a DATE - which has a different default mask of dd-mon-rr - which works also with dd-mon-rrrr dates since the time is NOT included - we get the expected value.

So, the problem I encountered was an implicit conversion - introduced by me right before the session.  (Note to self: stop changing stuff before sessions, it is a bad idea).

The fix would be to use to_timestamp:

ops$tkyte%ORA11GR1> insert into audit_trail (ts,data) values ( to_timestamp('11-sep-2007','dd-mon-yyyy'), 'xx' );

1 row created.

ops$tkyte%ORA11GR1> select partition_name, tablespace_name, high_value
  2    from user_tab_partitions
  3   where table_name = 'AUDIT_TRAIL'
  4   order by partition_position;

PARTITION_ TABLESPACE HIGH_VALUE
---------- ---------- -----------------------------------
P0         USERS      TIMESTAMP' 2007-02-23 00:00:00'
SYS_P204   USERS      TIMESTAMP' 2007-06-03 00:00:00'
SYS_P206   EXAMPLE    TIMESTAMP' 2007-09-12 00:00:00'
SYS_P205   USERS      TIMESTAMP' 2007-09-17 00:00:00'

So, now the demo works as expected and no (evil) implicit conversions happen...