Creating partitioned table gets error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Creating partitioned table gets error

  1. #1
    Join Date
    Apr 2004
    Location
    Columbia MD
    Posts
    3

    Creating partitioned table gets error

    When I submit the following script to create a partitioned tables I get an error message. Script:

    Create table fact_logp (
    VCH_ORIGIN VARCHAR2(255),
    VCH_SOURCE VARCHAR2(100),
    VCH_CATEGORY VARCHAR2(100),
    N_EVENT NUMBER,
    N_ERROR NUMBER,
    VCH_USER VARCHAR2(255),
    VCH_NODE VARCHAR2(128),
    VCH_SERVER VARCHAR2(50),
    DTDATE DATE,
    LOGDATE DATE,
    UTCDATE DATE,
    N_HOURS NUMBER,
    N_MINUTES NUMBER,
    N_SECONDS NUMBER,
    VCH_OBJECT1 VARCHAR2(255),
    VCH_CLASS1 VARCHAR2(255),
    VCH_ATTRIBUTE1 VARCHAR2(255),
    VCH_VALUE1 VARCHAR2(255),
    VCH_OBJECT2 VARCHAR2(255),
    VCH_CLASS2 VARCHAR2(255),
    VCH_ATTRIBUTE2 VARCHAR2(255),
    N_GWREF NUMBER,
    VCH_DESCRIPTION VARCHAR2(1000),
    N_INSERTDATETIME DATE)
    partition by range(DTDATE)
    (partition JAN04 values less than (to_date('31-JAN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition FEB04 values less than (to_date('29-FEB-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition MAR04 values less than (to_date('31-MAR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition APR04 values less than (to_date('30-APR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition MAY04 values less than (to_date('31-MAY-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition JUN04 values less than (to_date('30-JUN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition JUL04 values less than (to_date('31-JUL-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition AUG04 values less than (to_date('31-AUG-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition SEP04 values less than (to_date('31-SEP-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition OCT04 values less than (to_date('31-OCT-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition NOV04 values less than (to_date('30-NOV-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS,
    partition DEC04 values less than (to_date('31-DEC-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
    tablespace USERS
    )
    ;

    error message:ERROR at line 29:
    ORA-14027: only one PARTITION clause may be specified

    This is a 9i database, and examples in the Oracle DBA Handbook were used as models.

    Thanks for any help.

    Mike C
    Chewning Mike

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The three parentheses at the end of each partition statement ought to be two ... ie .
    Code:
    partition by range(DTDATE)
    (partition JAN04 values less than (to_date('31-JAN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
    tablespace USERS,
    partition FEB04 values less than (to_date('29-FEB-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
    tablespace USERS,
    partition MAR04 values less than (to_date('31-MAR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
    tablespace USERS,
    ...
    Also, that partition naming convention is going to come back and bite you later . I'd go for partition names like 'Y2004_M01', 'Y2004_M02', ... 'Y2004_M12' so that they sort correctly if you wanted to do something like ...
    Code:
    select
       partition_name,
       num_rows
    from
       user_tab_partitions
    where
       table_name = 'FACT_LOGP'
    order by 1
    /
    Also, the limits on the partitions are exclusive, so to be strict you ought to have ...
    Code:
    ...
    Partition Y2004_M01 Values Less Than (To_Date('01-02-2004','DD-MM-YYYY')),
    Partition Y2004_M02 Values Less Than (To_Date('01-03-2004','DD-MM-YYYY')),
    ...
    That would also avoid errors like thinking there are 31 days in September

    Lastly, if your partitions are all going in the same tablespace then just specify it once, like ...
    Code:
     ...
       VCH_DESCRIPTION VARCHAR2(1000),
       N_INSERTDATETIME DATE)
    tablespace USERS
    partition by range(DTDATE)
    (partition ...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Another point ... what's the story with the column naming convention? It's your own business, but I wouldn't be using those data type prefixes.

    I'd also come up with a convention for abbreviating those column names, like DESC = DESCRIPTION, CTGRY or CAT = CATEGORY, OBJ = OBJECT etc.. Your finger joints will thank you one day.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Apr 2004
    Location
    Columbia MD
    Posts
    3

    Thanks

    Thank everyone so much for their input and help. I too am not an OCA or an OCP, but I hope to be one. I too am learning from the school of hard knock, I am a former IBM Senior Systems Programmer. I did RTFM
    over and over for about two days before I posted the question. I guess I just missed a couple of the finer points.

    Again thank everyone that responded.

    Mike C

    Chewning Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width