3

How to do I swap two tables in an atomic fashion in Oracle?

I would like to perform the equivalent of:

ALTER TABLE foo RENAME foo_tmp;
ALTER TABLE foo_new RENAME foo;

but what happens if a query needs table foo in between those two lines when there is no table foo? Some lock would be required.

P.S. For a MySQL version of the question, see this question.

4

Create a public synonym that points to the new table.

CREATE PUBLIC SYNONYM foo FOR foo_new;
RENAME foo TO foo_tmp;

-- At this point in time, any DMLs for foo will operate on foo_new, via the synonym 

RENAME foo_new TO foo;
DROP PUBLIC SYNONYM foo;

Things will get a bit more complicated if you're accessing the tables involved with a user other than the schema owner, but this works if you're only accessing the table with the schema owner.

| improve this answer | |
  • What happens between RENAME foo_new TO foo; DROP PUBLIC SYNONYM foo;? Does the synonym readjust itself atomically with the rename, or is there a gap between the two DDLs where if a query would come in between the two, the synonym would point to a non existing table? – Hans Deragon Jul 5 '17 at 17:11
  • It depends on the logged in user. For it to work correctly the logged in user has to own the table – Philᵀᴹ Jul 5 '17 at 17:36
1

I don't think you can create synonyms on tables that already exist. You should get this error: ORA-00955. While a public synonym will only work when you don't specify schemas when addressing objects e.g. select * from foo rather than select * from larry.foo. Furthermore public synonyms are inherently a security risk. Instead you can switch to green/blue deployments.

Set up your object for green/blue

-- rename your table/mv/view this will make your object unavailable one last time
alter table foo rename to foo_green;
-- create your synonym
create SYNONYM foo for foo_green;
-- make a new version of foo
create table foo_blue as select * from foo;
-- update your synonym.  This will swap the tables 
create or replace SYNONYM foo for foo_blue;

It should be fairly straightforward to script out swapping tables in an out by determining which table the synonym is currently point to.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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