How do I swap tables atomically in Oracle?

Question

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.

Answer 1

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.

Answer 2

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.