Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rename table transaction

Re: Rename table transaction

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 17 Mar 2005 07:41:38 -0800
Message-ID: <bf4638050317074110304ebe@mail.gmail.com> 





You have shown why this can't be done in one transaction,
DDL does an implicit commit.



So, you'll have to fake it.



Here's one way that may work for you.



Keep track of the tables that are renamed, and rename them
back to the original name if there is a failure.




07:38:21 hudson - jkstill_at_ts71 SQL> create table tab_1 ( c1 number );
 


Table created.
 


07:38:21 hudson - jkstill_at_ts71 SQL> create table tab_2 ( c1 number );
 


Table created.
 


07:38:21 hudson - jkstill_at_ts71 SQL> create table tab_3 ( c1 number );
 


Table created.
 


07:38:21 hudson - jkstill_at_ts71 SQL> create table tab_4 ( c1 number );
 


Table created.
 

07:38:21 hudson - jkstill_at_ts71 SQL>
07:38:21 hudson - jkstill_at_ts71 SQL> create table tab_rename (
07:38:21   2          old_table_name varchar2(30),
07:38:21   3          new_table_name varchar2(30)
07:38:21   4  );
 




Table created.



07:38:21 hudson - jkstill_at_ts71 SQL> desc tab_1

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 C1                                                             NUMBER
 
07:39:01 hudson - jkstill_at_ts71 SQL> @r
07:39:09 hudson - jkstill_at_ts71 SQL>
07:39:09 hudson - jkstill_at_ts71 SQL> declare
07:39:09   2
07:39:09   3          type tab_name_type is table of varchar2(30)
index by binary_integer;
07:39:09   4          tab_names tab_name_type;
07:39:09   5
07:39:09   6          new_table_name varchar2(30);
07:39:09   7
07:39:09   8  begin
07:39:09   9
07:39:09  10          for i in 1..4
07:39:09  11          loop
07:39:09  12                  tab_names(i) := 'TAB_' || to_char(i);
07:39:09  13          end loop;
07:39:09  14
07:39:09  15          for i in 1..4
07:39:09  16          loop
07:39:09  17                  begin
07:39:09  18                          new_table_name := tab_names(i) || '_NEW';
07:39:09  19                          insert into tab_rename
values(tab_names(i), new_table_name);
07:39:09  20                          execute immediate 'rename ' ||
tab_names(i) || ' to ' || new_table_name;
07:39:09  21                  exception
07:39:09  22                  when others then
07:39:09  23                          for trec in (select * from tab_rename)
07:39:09  24                          loop
07:39:09  25                                  execute immediate
'rename ' || trec.new_table_name || ' to ' || trec.old_table_name;
07:39:09  26                          end loop;
07:39:09  27                  end;
07:39:09  28          end loop;
07:39:09  29




07:39:09  30  end;


07:39:09  31  /
 


PL/SQL procedure successfully completed.
 

07:39:09 hudson - jkstill_at_ts71 SQL>
07:39:09 hudson - jkstill_at_ts71 SQL>
07:39:09 hudson - jkstill_at_ts71 SQL> desc tab_1


ERROR:



ORA-04043: object tab_1 does not exist
 
 


07:39:14 hudson - jkstill_at_ts71 SQL> desc tab_1_new

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 C1                                                             NUMBER
 




07:39:18 hudson - jkstill_at_ts71 SQL>


07:39:56 hudson - jkstill_at_ts71 SQL> drop table tab_1_new;
 


Table dropped.
 


07:39:56 hudson - jkstill_at_ts71 SQL> drop table tab_2_new;
 


Table dropped.
 


07:39:56 hudson - jkstill_at_ts71 SQL> drop table tab_3_new;
 


Table dropped.
 


07:39:56 hudson - jkstill_at_ts71 SQL> drop table tab_4_new;
 


Table dropped.
 


07:39:57 hudson - jkstill_at_ts71 SQL> drop table tab_rename;
 


Table dropped.
 


07:39:57 hudson - jkstill_at_ts71 SQL>


07:39:57 hudson - jkstill_at_ts71 SQL> create table tab_1 ( c1 number );
 


Table created.
 


07:39:57 hudson - jkstill_at_ts71 SQL> create table tab_2 ( c1 number );
 


Table created.
 


07:39:57 hudson - jkstill_at_ts71 SQL> create table tab_3 ( c1 number );
 


Table created.
 


07:39:57 hudson - jkstill_at_ts71 SQL> create table tab_4 ( c1 number );
 


Table created.
 

07:39:57 hudson - jkstill_at_ts71 SQL>
07:39:57 hudson - jkstill_at_ts71 SQL> create table tab_rename (
07:39:57   2          old_table_name varchar2(30),
07:39:57   3          new_table_name varchar2(30)
07:39:57   4  );
 




Table created.
 

07:39:57 hudson - jkstill_at_ts71 SQL>
07:40:18 hudson - jkstill_at_ts71 SQL> drop table tab_3
07:40:31   2  /
 




Table dropped.
 

07:40:35 hudson - jkstill_at_ts71 SQL> @r
07:40:37 hudson - jkstill_at_ts71 SQL>
07:40:37 hudson - jkstill_at_ts71 SQL> declare
07:40:37   2
07:40:37   3          type tab_name_type is table of varchar2(30)
index by binary_integer;
07:40:37   4          tab_names tab_name_type;
07:40:37   5
07:40:37   6          new_table_name varchar2(30);
07:40:37   7
07:40:37   8  begin
07:40:37   9
07:40:37  10          for i in 1..4
07:40:37  11          loop
07:40:37  12                  tab_names(i) := 'TAB_' || to_char(i);
07:40:37  13          end loop;
07:40:37  14
07:40:37  15          for i in 1..4
07:40:37  16          loop
07:40:37  17                  begin
07:40:37  18                          new_table_name := tab_names(i) || '_NEW';
07:40:37  19                          insert into tab_rename
values(tab_names(i), new_table_name);
07:40:37  20                          execute immediate 'rename ' ||
tab_names(i) || ' to ' || new_table_name;
07:40:37  21                  exception
07:40:37  22                  when others then
07:40:37  23                          for trec in (select * from tab_rename)
07:40:37  24                          loop
07:40:37  25                                  execute immediate
'rename ' || trec.new_table_name || ' to ' || trec.old_table_name;
07:40:37  26                          end loop;
07:40:37  27                  end;
07:40:37  28          end loop;
07:40:37  29




07:40:37  30  end;


07:40:37  31  /


declare


*


ERROR at line 1:

ORA-04043: object TAB_3_NEW does not exist
ORA-06512: at line 25
ORA-04043: object TAB_3 does not exist
 
 
07:40:37 hudson - jkstill_at_ts71 SQL>




07:40:37 hudson - jkstill_at_ts71 SQL>


07:40:37 hudson - jkstill_at_ts71 SQL> desc tab_1

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 C1                                                             NUMBER
 




07:40:41 hudson - jkstill_at_ts71 SQL>





On Tue, 15 Mar 2005 11:05:46 +0100, Denys VAN KEMPEN
<DVANKEMPEN_at_cartesis.com> wrote:


> Could anybody inform me how to wrap a number of rename table operations into

> a transaction?

> 

> You can do this on SQL Server with

> 

> BEGIN TRANSACTION


> 

> EXECUTE sp_rename 'tableA, 'tableX'

> 

> EXECUTE sp_rename 'tableB, 'tableA'

> 

> EXECUTE sp_rename 'tableX, 'tableB'

> 

> GO

> 

> COMMIT


> 

> On Oracle rename table is DDL so rollback or commit is meaningless. However,

> we need to guarantee that all commands either succeed or fail.

> 

> Thanks

> 

> Denys van Kempen

> 

> ***********************************************************************

> 

> CARTESIS http://www.cartesis.com

> Great performances start with confidence (TM)

> 

> The information transmitted is intended  only for the  person or entity

> to which it is addressed and may contain confidential and/or privileged

> material. Any review, retransmission, dissemination or other use of, or

> taking of any action in reliance upon, this information by  persons  or

> entities other  than  the  intended  recipient  is  prohibited.  If you

> received this in error,  please  contact  the  sender  and  delete  the

> material from any computer.

> 

> Vous recevez ce message car vous avez communique votre adresse email au

> moins une fois a  Cartesis.  Conformement  a  l'article  34  de  la loi

> Informatique et Libertes du 6 janvier 1978, vous  disposez  d'un  droit

> d'opposition, d'acces et de rectification des donnees  vous  concernant

> soit par courrier a l'adresse: Cartesis - Legal Department,  23-25  rue

> de Berri, 75008 Paris soit par email: legal_at_cartesis.com.

> 

> ***********************************************************************

> 

> --

> http://www.freelists.org/webpage/oracle-l

> 




-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l


Received on Thu Mar 17 2005 - 10:45:10 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US