Geek Answers Handbook

Rename Oracle 11g. Guaranteed to be atomic?

I have several (5) rename statements in a PLSQL script

drop table new; rename old to new;

"old" tables contain very valuable information.

As I see it, if the rename command is atomic, I will have one problem.

Is it atomic? If not, is there a way to do a “safe” rename?

Thanks in advance

+4
source share
5 answers

RENAME is the DDL team. Thus, this is a single discrete transaction, if that is what you mean by atom in this context. Therefore, it is about as safe as everyone. I cannot imagine how renaming will result in the loss of your data. But if you feel paranoid, just remember why Nature gave us a backup and restore.

change

How to make sure you don’t lose data if DROP succeeds and RENAME is to deploy RENAME twice:

 SQL> rename old_table to something_else; SQL> rename new_table to old_table; SQL> drop table something_else;

Thus, you have data on the Internet. It also minimizes downtime.

+7
source

Given your comments “His daily process” and “Yes, I worry about the paws between the transition and rename statements”

How much money do you have (or, more specifically, do you have a split option)? If yes, see the exchange section.

You have a permanent table consisting of one section. At the end of the day, you replace this section with a table (as a single atomic operator). Without dropping / renaming the main table, you should not invalidate any packages, etc. (Although this may depend on the version of the database).

Failed to use the view and make CREATE or REPLACE VIEW the main AS SELECT * FROM table_a, and every night you replace the view with a new one in another table. Perhaps this will lead to invalid packets.

+3
source

Rename will be atomic, so you should be fine. As APC noted,

I cannot imagine how renaming will result in the loss of your data.

The only thing I can see in your script is the time after the drop and before renaming, there is no new table, so some SQL may potentially fail. However, this time will be quite short and something more complicated (like Insert From Select ) will be even more problematic.

+1
source

I assume you are worried that the simultaneous DML (insert / update / delete) on the old table might be skipped during renaming? In this case, do not worry - RENAME is a DDL, and it locks the table for a while.

+1
source

If you are worried about the time between the transition and the renaming, here's another idea: use a view that points to the “correct base table”.

You start with

 CREATE VIEW someName as Select * From OldTable;

Then you can customize your new table. When you are ready, just

 CREATE OR REPLACE View someName as Select * From NewTable;

Then you can delete your OldTable. The next time you get new data, create another NewTable_2 (or reuse OldTable .. then it's probably best to use Table1 and Table2) and override the view again.

The view is as simple as it is, so it should be updatable without any problems. The only difficult task is to always create a new table (or switch between two tables), but it should not be too difficult to set up and, perhaps, easier than completely avoiding any problems that may arise with your initial proposal.

+1
source

More articles:


All Articles