7 Responses to “Using DBMS_REDEFINITION with DBMS_METADATA to Redefine a Table”

  1. Nick W Says:

    Interesting approach Andy. I’ve done similar things in the past and run into problem with extremely high usage of UNDO/REDO space and TEMP space during online redefinition. Have you run into impacts like that or has Oracle sorted that out in more recent versions?

    • dbakerber Says:

      I have not run into those issues, but neither have I tracked them. The SP does only one table at a time, so I would not expect the space usage to be too terrible. Obviously we need room to make a duplicate of the current table, so if that is extremely huge that could be a problem. Our current system is also over configured for space, so there definitely could be a problem I simply have not noticed.

  2. PL/SQL package to move tables to new tablespace | Steve Harville's Blog Says:

    […] https://dbakerber.wordpress.com/2015/04/09/using-dbms_redefinition-with-dbms_metadata-to-redefine-a-… […]

  3. RemcoD Says:

    On the indexes: what if I need to rename a column that is indexed? In that case, the COPY_TABLE_DEPENDENTS will return an error. I think that is the advantage of creating separate indexes and using DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT 🙂

    Nice generic setup though, I like it a lot, and will use it to create my own version of a generic stored procedure to redefine tables. So thanks for saving me a lot of work!

    If you like, I can send you my version when it’s finished, if you are interested.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: