Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    11

    Unanswered: partition by range

    can some one mention the syntax of partition by range
    I want to add range partition



    ALTER TABLE yearlydatils
    ADD PARTITION BY RANGE (y_YEARNUMBER, m_MONTHNUMBER)
    (
    PARTITION oct2003 VALUES LESS THAN (2003, 10)
    TABLESPACE tsp1
    )
    ;

    the above syntax is giving error .

    Thanks,
    san

  2. #2
    Join Date
    Nov 2002
    Location
    Hyderabad, AP, INDIA
    Posts
    2

    Re: partition by range

    Hi

    Are you sure you can add partition to an exisiting table just by adding it. I believe cannnot. I suggest you recreate the table with the partition option and move the data to the newly created table.

    Below is a example of Range partition.

    If you have oracle documentation, refer to a76956 (Oracle Administrator Guide Ver. 8i) document Page No. 15-8.

    create table emp
    (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
    )
    partition by range (sal)
    (partition emp3part1 values less than (2000),
    partition emp3part2 values less than (maxvalue)
    );


    Good luck.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: partition by range

    Originally posted by sarath75
    Hi

    Are you sure you can add partition to an exisiting table just by adding it. I believe cannnot. I suggest you recreate the table with the partition option and move the data to the newly created table.

    Below is a example of Range partition.

    If you have oracle documentation, refer to a76956 (Oracle Administrator Guide Ver. 8i) document Page No. 15-8.

    create table emp
    (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
    )
    partition by range (sal)
    (partition emp3part1 values less than (2000),
    partition emp3part2 values less than (maxvalue)
    );


    Good luck.
    Yes, you can add a partition to a table. However, I believe the correct syntax for this example to be:

    ALTER TABLE yearlydatils
    ADD PARTITION oct2003 VALUES LESS THAN (2003, 10)
    TABLESPACE tsp1
    );

    This requires that:
    1) the table was CREATED using PARTITION BY RANGE(y_YEARNUMBER, m_MONTHNUMBER)
    2) there is not already a partitition for VALUES LESS THAN (MAXVALUE, MAXVALUE) - if there use, you have to split the partition instead of adding one
    3) (2003, 10) is higher than the previous high partition.

Posting Permissions

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