CODEWITHSUNDEEP
oracle-database
Sean Nguyen
Sean Nguyen

Reputation: 13128

How to swap table name in oracle

I have 2 oracle tables that are identical in definition just different in partition definition. I want to test one table vs another table design. Is there a way to swap the table name? I don't want to drop a table because they are big and takes a long time to load data into them.

Upvotes: 3

Views: 6750

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

A third method would be to use a view. Let's say your "real" table names are TABLE_A and TABLE_B. Create a view, MY_DATA_VIEW (or whatever), and have it point to whichever table you want it to point to:

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TABLE_A;

or

CREATE OR REPLACE VIEW TEST_VIEW AS SELECT * FROM TABLE_B;

Share and enjoy.

Upvotes: 2

Melioratus
Melioratus

Reputation: 295

Use a synonym that points to the real tables.

For example,

CREATE OR REPLACE SYNONYM partition_test FOR partition_table1;

Test partition_table1, e.g. select pt.* from partition_test pt; 

CREATE OR REPLACE SYNONYM partition_test FOR partition_table2;

Test partition_table2, e.g. select pt.* from partition_test pt;

Notice the test code is then same each time.

When you're done testing, drop the synonym.

DROP SYNONYM partition_test;

Upvotes: 10

nolt2232
nolt2232

Reputation: 2644

Just rename them. For example, if you have TABLE_A, rename it to TABLE_A_TEMP. Then rename TABLE_B to TABLE_A. Then rename TABLE_A_TEMP to TABLE_B.

To rename, you'll have to issue

alter table table_name rename to new_table_name;

Upvotes: 3

Related Questions