Re: Timestamp and timestamp with local timezone question

On 9 Dec 2005 05:36:16 -0800, "Krispyqube" <kris.plakkat_at_gmail.com> wrote:

>Hi
>

Here is some insight :

1. You don't give version and platform. The most basic sin in here.
2. You give invalid syntax in your statements. That means we can't recreate your errors without a lot of guessing for us.
3. You use a NLS-dependent thing in your partitioning definition, which is 'MON'. In Denmark, for instance, the 10th month is named 'OKT', so with a danish NLS setting, your 'OCT'-containing statement becomes invalid. Use 'MM' instead, most countries number the months equally.
4. Your partitioning definition contains no MAXVALUE partitioning.
5. You rely on Oracle's implicit conversion facilities when specifying *date* datetypes when defining *timestamp* partition borders. Date and timestamp are 2 different datatypes, and while developers may confuse them till the end of time, don't carry that confusion into your basic data structures (DDL's).
6. You don't seem to do the basic research yourself (laziness is another sin here). You have an insert-statetement which fails. The error message helps you by suggesting that the select-part of it fails. Now try running the select part alone to see if that statement fails with the same ORA-. If it does, you can almost certainly ignore the APPEND and PARALLEL things in your further debugging.
   • Kenneth Koenraadt

>
>Table 1 - source - UNPARTITIONED Very big table > 200M
>----------
>
>col1 timestamp with local time zone,
>col2 number,
>col3 varchar2(10),
>col4 timestamp
>
>
>Table 2 - Destination - PARTITIONED - structure same as source
>above but no time zone
>==============================
>currently empty structure range partioned based on col1 ( timestamp)
>
>
>col1 timestamp,
>col2 number,
>col3 varchar2(10),
>col4 timestamp
>
>PARTITION BY RANGE (col1)
>(PARTITION PART_2003 VALUES LESS THAN
>(TO_DATE('01-JAN-2004','DD-MON-YYYY')),
>PARTITION PART_Q1_2004 VALUES LESS THAN
>(TO_DATE('01-APR-2004','DD-MON-YYYY')),
>PARTITION PART_Q2_2004 VALUES LESS THAN
>(TO_DATE('01-JUL-2004','DD-MON-YYYY')),
>PARTITION PART_Q1_2005 VALUES LESS THAN
>(TO_DATE('01-OCT-2005','DD-MON-YYYY')),
>PARTITION PART_Q4_2005 VALUES LESS THAN
>(TO_DATE('01-JAN-2006','DD-MON-YYYY'))
>
>
>Now I want to pick a subset and populate a small portion of my partiton
>say PART_Q4_2005
>
>i am using the
>
>insert /*+ APPEND */ into table2
>select (/*+ parallel (table1, 4) * from table1 where col1 >
>to_date('30-sep-2005', 'dd-mon-yyyy') ;
>
>
>Insert /*+ APPEND */ into TABLE2
>*
>ERROR at line 1:
>ORA-12801: error signaled in parallel query server P002
>ORA-01843: not a valid month
>
>
>is this a problem with conversion from timestamp LTZ to timestamp or do
>i nead to do some kind of cast or to_timestamp functions . I tried them
>but the error seem to pop up on the insert . if you run the latter part
>just the selects they work fine
>
>I tried specifying the columns but still get the error
>
>thanks for any help or insight
>
>regds
>Kris
>
Received on Sat Dec 10 2005 - 11:45:55 CST