partition in oracle

Question

CREATE TABLE temp_stud as select * from STUD_MAST

PARTITION BY RANGE(ADM_DT)

(

PARTITION temp_stud1 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),

PARTITION temp_stud2 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),

PARTITION temp_stud3 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),

PARTITION temp_stud4 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

);

I am getting a missing left parenthesis error for above table creation can anyone tell me what is the issue in above creation

Note: ADM_DT is a date column with data type char(8) and storing format YYMMDD

Sachin Padha · Accepted Answer · 2022-08-06 07:46:22Z

0

Please use below SQL. The Creation of Partition has be part of Create table.

CREATE TABLE temp_stud   
    PARTITION BY RANGE(ADM_DT)

    (

    PARTITION temp_stud1 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),

    PARTITION temp_stud2 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),

    PARTITION temp_stud3 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),

    PARTITION temp_stud4 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

    )
    as select * from STUD_MAST;

answered Aug 6, 2022 at 7:46

Sachin Padha

2211 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Robert Over a year ago

i am getting error code ORA-14036 partition bound value too large for column

Robert Over a year ago

ADM_DT data type is char(8) and i cannot change it

William Robertson Over a year ago

@Robert In your question you said "Note: ADM_DT is a date". Maybe you should correct that.

Alex Poole Over a year ago

"Note: ADM_DT is a date" - now you say it's a string. Which is is? If it is a string (why!?) what format - if YYYYMMDD then you can partition on a string too, instead of on a date?

Robert Over a year ago

hello guys ADM_DT is a date but its data type in char(8)

|

Collectives™ on Stack Overflow

partition in oracle

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related