Oracle Database Discussions

Hello all,

I've got a situation coming up possibly, that I'm looking at where I may need to rename a table that has about 3 TB of data in it.

I'd looked at doing the oracle RENAME command, and it mentions that some restrictions that would error out would be:

views, synonyms, and stored procedures and functions that reference the table.

I'd been looking around on different sites researching more, and found some saying that tables with foreign keys, check constraints would also error out on this....but in the Oracle docs I see it saying:

"Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object."


Were the other non-Oracle site references maybe to older versions? I'm using 11Gr2.



The other part of this is that the app designers really want to recreate the original table. Essentially this is doing

TABLENAME1 -> TABLENAME1_OLD

Then recreating TABLENAME1 anew empty for new use.

My original thoughts were to do select * from TABLENAME1 into TABLENAME1_OLD, and then truncate TABLENAME1....but with the size of the data (and this will eventually be done in PROD), I"m trying to say that doing the RENAME and recreating the empty table would be less resource intensive.

Would the rename and recreate table be the way to go?

If so, then I need to make sure I know all the restrictions and pitfalls on rename would have....

Thanks in advance!!

cayenne