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.

Hans Deragon
  • 185
  • 1
  • 3
  • 9

2 Answers2

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.

Joe Obbish
  • 32,165
  • 4
  • 71
  • 151
Philᵀᴹ
  • 31,762
  • 10
  • 83
  • 107
  • 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 05 '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 05 '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.

Larry Symms
  • 11
  • 1