Re: Rename table transaction
- From: Jared Still <jkstill@xxxxxxxxx>
- To: DVANKEMPEN@xxxxxxxxxxxx
- Date: Thu, 17 Mar 2005 07:41:38 -0800
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@ts71 SQL> create table tab_1 ( c1 number );
Table created.
07:38:21 hudson - jkstill@ts71 SQL> create table tab_2 ( c1 number );
Table created.
07:38:21 hudson - jkstill@ts71 SQL> create table tab_3 ( c1 number );
Table created.
07:38:21 hudson - jkstill@ts71 SQL> create table tab_4 ( c1 number );
Table created.
07:38:21 hudson - jkstill@ts71 SQL>
07:38:21 hudson - jkstill@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@ts71 SQL> desc tab_1
Name Null? Type
----------------------------------------------------- --------
------------------------------------
C1 NUMBER
07:39:01 hudson - jkstill@ts71 SQL> @r
07:39:09 hudson - jkstill@ts71 SQL>
07:39:09 hudson - jkstill@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@ts71 SQL>
07:39:09 hudson - jkstill@ts71 SQL>
07:39:09 hudson - jkstill@ts71 SQL> desc tab_1
ERROR:
ORA-04043: object tab_1 does not exist
07:39:14 hudson - jkstill@ts71 SQL> desc tab_1_new
Name Null? Type
----------------------------------------------------- --------
------------------------------------
C1 NUMBER
07:39:18 hudson - jkstill@ts71 SQL>
07:39:56 hudson - jkstill@ts71 SQL> drop table tab_1_new;
Table dropped.
07:39:56 hudson - jkstill@ts71 SQL> drop table tab_2_new;
Table dropped.
07:39:56 hudson - jkstill@ts71 SQL> drop table tab_3_new;
Table dropped.
07:39:56 hudson - jkstill@ts71 SQL> drop table tab_4_new;
Table dropped.
07:39:57 hudson - jkstill@ts71 SQL> drop table tab_rename;
Table dropped.
07:39:57 hudson - jkstill@ts71 SQL>
07:39:57 hudson - jkstill@ts71 SQL> create table tab_1 ( c1 number );
Table created.
07:39:57 hudson - jkstill@ts71 SQL> create table tab_2 ( c1 number );
Table created.
07:39:57 hudson - jkstill@ts71 SQL> create table tab_3 ( c1 number );
Table created.
07:39:57 hudson - jkstill@ts71 SQL> create table tab_4 ( c1 number );
Table created.
07:39:57 hudson - jkstill@ts71 SQL>
07:39:57 hudson - jkstill@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@ts71 SQL>
07:40:18 hudson - jkstill@ts71 SQL> drop table tab_3
07:40:31 2 /
Table dropped.
07:40:35 hudson - jkstill@ts71 SQL> @r
07:40:37 hudson - jkstill@ts71 SQL>
07:40:37 hudson - jkstill@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@ts71 SQL>
07:40:37 hudson - jkstill@ts71 SQL>
07:40:37 hudson - jkstill@ts71 SQL> desc tab_1
Name Null? Type
----------------------------------------------------- --------
------------------------------------
C1 NUMBER
07:40:41 hudson - jkstill@ts71 SQL>
On Tue, 15 Mar 2005 11:05:46 +0100, Denys VAN KEMPEN
<DVANKEMPEN@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx
>
> ***********************************************************************
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
