question

thecoop avatar image
thecoop asked

How do you do table rebuilds?

There are some table modifications that can't be done using an ALTER (eg changing a datatype), and have to be done using a table rebuild. From what I know, there are two ways of doing it:

  1. creating a new table, doing INSERT INTO <newtable> SELECT <conversion functions> FROM <oldtable>, DROP <oldtable>, then RENAME <newtable> TO <oldtable>
  2. Using DBMS_REDEFINITION

are there any other methods, and which method should I use?

rebuildredefinition
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
HillbillyToad avatar image
HillbillyToad answered

Is it OK if your table/data is offline? DBMS_REDEF can do online table rebuilds, so it's definitely worth a look-see.

Nice blog here talking about one person's experience with it http://toinevanbeckhoven.wordpress.com/2009/09/23/127/

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.