Is there an option to use Oracles online table redefinition feature but only copy a subset of the data from the source table?

I mean - the source table has 2M records and I want to only copy those that have ID > something. The problem is that START_REDEF_TABLE seems to sync all 2M records. Is there a way how to specify whics records should be synced over?

  • 1
    From the documentation it looks like this is not in the scope - the aim is to change how the table is stored or its structure. Why cannot you simply delete the unnecessary data before redefining the table? Jun 24, 2015 at 10:54
  • Yeah you see that is the problem :-) The data is so big that undo tablespace dies... I need to truncate... and I dont want to truncate all. The thought was to redefine and have some data in the redefined table and truncate the rest. I know the answer is partitioning but that is something I tried to avoid for obvious reasons :)
    – kosta5
    Jun 24, 2015 at 11:15
  • 2M rows usually do not require partitioning - these days it's not even considered as a big amount of data (unless the table is very wide). You possibly want to change the relevant settings :) On the other hand, it is also possible to remove the rows in small batches. Jun 24, 2015 at 12:46
  • How big is your undo tablespace? Jun 24, 2015 at 15:55
  • Not big enough :) we get batch loads that sometimes generate 20 Gb is data in 5 hours... If your purge data script runs every 7 days your undo will never be big enough. And running the script more often wont help since there is too much data generated in short time. Plus running the script slows down the whole db server
    – kosta5
    Jun 24, 2015 at 16:23

2 Answers 2


1) online redefinition uses the methods provided by advanced replication. You can try to create a solution with these methods. You have to create a materialized view that contains only the data you need. Then you have a short period of time where you rename the table and its indexes, grant the permissions on the new objects etc.

2) The simpler way is to delete not all data at once but only a fix number of rows in each step. So program a loop and in this loop delete a number of rows

This sql script deletes the rows in batches of 100. After the execution of the scritps all rows with id<=somethingsare deleted.

  deleted number;
    from mytable 
    where id<=something  and rownum<=100 
    returning count(*) into deleted;
  if deleted=0  then
    end if;
  end loop;

If I understood you don't need to do it online.

In this case, how about a CREATE TABLE AS SELECT using a select which is focusing on the subset of data?

Then you just do a quick rename, recreate the indexes and done.

  • We need to do it online. It is a logging table (log details are stored here by Oracle Middleware crap).
    – kosta5
    Jun 24, 2015 at 16:24
  • Still you could do the renaming method and then insert the data from the original table which was written during create table as select period
    – BLT
    Jun 25, 2015 at 12:11
  • We can not use rename. With rename there is one point where the table does not exist for a short period of time. We cant do that with online log table...
    – kosta5
    Jun 25, 2015 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.