Split Table partition

We are using Oracle 8.0.6.1.0 under Solaris 2.6 environment. We have a table partitioned by date range. i.e.,

       create table ... (
         timestamp             date   not null
       )
       partition by range (timestamp)
       (
         partition P200102 values less than
           ( to_char('2001-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') )
             ....

However, when we try to split the partition into 2 and issuing the following syntax:

....

     split partition P200102 into (
       partition P20010215 values less than
         ( to_char('2001-02-16 00:00:00','SYYYY-MM-DD HH24:MI:SS') ),


     ....

A strange thing occurred. Actually, the creation procedure is success and table are split without any problem. However, when we try to select the partition value through dba_tab_partitions. The syntax for the 'less than' value change into

 ( to_char('2001-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS',    NLS_CALENDAR_CHARACTER='GEORDIAN') ) <I am not exactly remember the displayed value, but it should be something like that a NLS CALENDAR is automatically appended after the to_char function>

Do anyone know why it occurred and how can i removed it? In fact, we haven't seen this 'character set' when splitting table partition in 8.0.4.4.0 version.

Thanks in Advanced!

Moreover, it seems that 8.0.6 is still not stable coz we have some experience that a simple join on the date field will push the optimizer to do a full table scan instead of partition scan if we have applied an alter session set nls_date_format into the SQLPLUS session.

That was strange! We use the plan_table and saw that the SQL was optimized to use partition scan on a single partition. However, when executing the SQL in original NLS_DATE_FORMAT and new one set as follows:   alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' The optimizer seemed running on absolutely difference dataset. It can easy be seemed through V$SESSION_LONGOPS 's sofar, and total columns.

Another strange problem is: The SQL execution return to normal after we have restarted the database. We found that the database was caching messy and messy after it started. And the only thing we can do was to restart it. Similar problem also appearred in PLSQL blocks!

If that can be simulated in a regular pattern, I think I will report it to Oracle Support in no time. However, things can't be expected.

Do anyone have the same experience on this and do you have anythings to share? Received on Sun Mar 04 2001 - 12:36:44 CST