Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: convert table to partitioned

    I forsee an issue with a large table that we're adding to our PROD server, and I'd like to be able to react quickly if we need to partition the table. We haven't purchased the Partitioning license, so I can't set the table up that way from the start.

    That being the case, can you ALTER a TABLE and add partitions after the fact, or do you have to create a new table and then load it with the old table's data?

    I tried the following and I got an error, although this is an early attempt at learning the syntax

    Code:
    create table no_partition (tk number primary key, create_date date);
    
    declare 
      a_date date := sysdate;
    begin
      for i in 1 .. 100
      loop
        insert into no_partition values  (i, a_date + (i * 10));
      end loop;
    end;
    
    
    alter table no_partition add partition YY2006 values less than to_char('1/1/2007','MM/DD/YYYY');
    the error on the ALTER TABLE is "ORA-00906: missing left parenthesis"

    -Chuck

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use dbms_redefinition providing you have the disk space for two copies of data. It lets you redefine your table without affecting your live users or losing any data. We have used it on numerous occasions without any problems in a live production environment.

    Alan

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This article deals with all ways of converting a non-partitioned table into partitioned one. Maybe you'll find it interesting.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by AlanP
    Use dbms_redefinition providing you have the disk space for two copies of data. It lets you redefine your table without affecting your live users or losing any data. We have used it on numerous occasions without any problems in a live production environment.

    Alan
    Alan, you have done this seamlessly online?
    What about dependencies? I would imagine at some point you would need
    a maintenance window on production to switch over to the partitioned table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    No maintenance window required, even on a moderately busy system where inserts, updates and deletes were going on! It seems to keep track of any changes made (materialized view log), which it will apply when you sync or finish and then it looks like it takes out a library cache lock while it renames the tables, but since this takes a fraction of a second it doesnt seem to have any impact. The only thing to note is that there are some restrictions and if you do the redefinition using rowid it will add a hidden column to your table so its usually better to do it using the primary key.

    Alan

Posting Permissions

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