Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Partitioning in Oracle

    Hi all,

    I wanted to create a partition on a table which has data in it already.
    Can I use "alter partition add partition..."? Or is it only for tables which is already partitioned and you want to add a partition?

    If I want to add a partition for a table which has data and no partitions, how can I do it?
    I read online where I have to create the table and create a partition and then drop the original table and rename this newly created table to the one I want. I felt this was a rather tedious process. Is there any better way for this. Any help is greatly appreciated.

    Thank you very much.

  2. #2
    Join Date
    Jul 2005
    Posts
    276
    Can I do this way-Partitioning by range for both year(fiscal_year) and month(accounting_period). Can I create sub partitions just for two partitions as I have huge data only there-for year '0' and '2006'.

    CREATE TABLE PROJECT
    (
    ...
    ..
    )
    PARTITION BY RANGE (FISCAL_YEAR)
    SUBPARTITION BY RANGE(ACCOUNTING_PERIOD)
    ( PARTITION YY_0 VALUES LESS THAN (TO_DATE('1-JAN-2003','DD-MON-YYYY'))
    SUBPARTITION P1 VALUES LESS THAN (1),
    SUBPARTITION P2 VALUES LESS THAN (2)),
    ( PARTITION YY_03 VALUES LESS THAN (TO_DATE('1-JAN-2004','DD-MON-YYYY'))
    ( PARTITION YY_04 VALUES LESS THAN (TO_DATE('1-JAN-2006','DD-MON-YYYY'))
    ( PARTITION YY_06 VALUES LESS THAN (TO_DATE('1-JAN-2007','DD-MON-YYYY'))
    SUBPARTITION P1 VALUES LESS THAN (1),
    SUBPARTITION P2 VALUES LESS THAN (2),
    SUBPARTITION P3 VALUES LESS THAN (3),
    SUBPARTITION P4 VALUES LESS THAN (4),
    SUBPARTITION P5 VALUES LESS THAN (5),
    SUBPARTITION P6 VALUES LESS THAN (6),
    SUBPARTITION P7 VALUES LESS THAN (7),
    SUBPARTITION P8 VALUES LESS THAN (8),
    SUBPARTITION P9 VALUES LESS THAN (9),
    SUBPARTITION P10 VALUES LESS THAN (10),
    SUBPARTITION P11 VALUES LESS THAN (11),
    SUBPARTITION P12 VALUES LESS THAN (12));

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    nandinir, did you test this yourself ? (that's what i would have done for you if I had the time right now).

    About the options: If you're on 9i and above, you have dbms_redefinition to redefine your table online.

  4. #4
    Join Date
    Jul 2005
    Posts
    276
    I got it finally...range partition on year and list partition on month....Perhaps I cant partition on the same type.
    Last edited by nandinir; 02-15-07 at 18:02.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Can I use exchange partition for Composite partitions?

Posting Permissions

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