Re: Split Table partition

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Mon, 05 Mar 2001 00:12:32 +0100
Message-ID: <3AA2CBE0.94EE8BE4@0800-einwahl.de>

The "NLS_CALENDAR='GREGORIAN'" is the default. It tells Oracle how the date should be interpreted. Directly from the Oracle 8.1.6 doc you find

"Many different calendar systems are in use throughout the world. NLS_CALENDAR specifies which calendar system Oracle uses.

NLS_CALENDAR can have one of the following values:

     Arabic Hijrah

     English Hijrah

     Gregorian

     Japanese Imperial

     Persian

     ROC Official (Republic of China)

     Thai Buddha

For example, if NLS_CALENDAR is set to "Japanese Imperial", the date format is "E YY-MM-DD", and the date is May 15, 1997, then the SYSDATE is displayed as follows:

SELECT SYSDATE FROM DUAL;
SYSDATE

---

H 09-05-15"

Martin

Lynux wrote:
>
> 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 - 17:12:32 CST