Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

everything is in the question.

I have a table named tableA on which we have indexes and foreign keys and constraints.

I have to modify content in a column called column1. This being a production system, I want to

1) create a copy of tableA called tableA_temp
2) Perform the data change (some concatenation and regexp_replace)
3) Obtain validation that the change is correct
4) rename tableA to tableA_old
4) Rename tableA_temp to tableA.

Once renamed to tableA, would the constraints and indexes apply to the new tableA?

My guess is yes but I would need a little validation.

share|improve this question
Unless the column you're changing is vital to the indexes or something similar, I'd think about adding a new column, setting/updating that from the old one, and when you're happy swap them over with a couple of alter table ... rename column ... commands, and at some point drop the original (now renamed) column. Seems slightly less messy than doing it at table level. – Alex Poole Sep 11 '12 at 17:57
up vote 1 down vote accepted

You can use the DBMS_REDEFINITION package to "re-organise" a table like this. It provides functionality for you to create a temporary table with modified contents of an existing table as you describe. It also allows you to copy over the grants, triggers, indexes etc to the new table, then switch the tables over so the temporary table becomes the live table.

See this article for a worked demo.

share|improve this answer
+1 Although this doesn't answer the question, it is pertinent/useful information addressing the underlying concern. – Leigh Riffel Jan 29 '13 at 14:20


Ok, it depends on if you create them on the copy.

share|improve this answer
OK. Thanks for the answer :) I'm adding constraint and index creation to the copy. – Nicolas de Fontenay Sep 11 '12 at 14:41
+1 No is the correct answer. Even if the constraints are created on the copy, they apply to the copy and aren't applied from a dropped table. – Leigh Riffel Jan 29 '13 at 14:19

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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