Rename table transaction

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Rename table transaction

Rename table transaction

2005-03-17 - By Jared Still

Reply: 1 2 3 4 5 6 7

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@(protected) SQL> create table tab_1 ( c1 number );

Table created.

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

Table created.

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

Table created.

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

Table created.

07:38:21 hudson - jkstill@(protected) SQL>
07:38:21 hudson - jkstill@(protected) 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@(protected) SQL> desc tab_1
Name Null? Type
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -- -----
-- ---- ---- ---- ---- ---- ---- ---
C1 NUMBER

07:39:01 hudson - jkstill@(protected) SQL> @(protected)
07:39:09 hudson - jkstill@(protected) SQL>
07:39:09 hudson - jkstill@(protected) 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@(protected) SQL>
07:39:09 hudson - jkstill@(protected) SQL>
07:39:09 hudson - jkstill@(protected) SQL> desc tab_1
ERROR:
ORA-04043 (See ORA-04043.ora-code.com): object tab_1 does not exist

07:39:14 hudson - jkstill@(protected) SQL> desc tab_1_new
Name Null? Type
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -- -----
-- ---- ---- ---- ---- ---- ---- ---
C1 NUMBER

07:39:18 hudson - jkstill@(protected) SQL>
07:39:56 hudson - jkstill@(protected) SQL> drop table tab_1_new;

Table dropped.

07:39:56 hudson - jkstill@(protected) SQL> drop table tab_2_new;

Table dropped.

07:39:56 hudson - jkstill@(protected) SQL> drop table tab_3_new;

Table dropped.

07:39:56 hudson - jkstill@(protected) SQL> drop table tab_4_new;

Table dropped.

07:39:57 hudson - jkstill@(protected) SQL> drop table tab_rename;

Table dropped.

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

Table created.

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

Table created.

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

Table created.

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

Table created.

07:39:57 hudson - jkstill@(protected) SQL>
07:39:57 hudson - jkstill@(protected) 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@(protected) SQL>
07:40:18 hudson - jkstill@(protected) SQL> drop table tab_3
07:40:31 2 /

Table dropped.

07:40:35 hudson - jkstill@(protected) SQL> @(protected)
07:40:37 hudson - jkstill@(protected) SQL>
07:40:37 hudson - jkstill@(protected) 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 (See ORA-04043.ora-code.com): object TAB_3_NEW does not exist
ORA-06512 (See ORA-06512.ora-code.com): at line 25
ORA-04043 (See ORA-04043.ora-code.com): object TAB_3 does not exist

07:40:37 hudson - jkstill@(protected) SQL>
07:40:37 hudson - jkstill@(protected) SQL>
07:40:37 hudson - jkstill@(protected) SQL> desc tab_1
Name Null? Type
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- -- -----
-- ---- ---- ---- ---- ---- ---- ---
C1 NUMBER

07:40:41 hudson - jkstill@(protected) SQL>

On Tue, 15 Mar 2005 11:05:46 +0100, Denys VAN KEMPEN
<DVANKEMPEN@(protected)> 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@(protected)
>
> ***********************************************************************
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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