Re: Alter name of a table

A copy of this was sent to Brian Peasland <peasland_at_edcmail.cr.usgs.gov> (if that email address didn't require changing) On Thu, 23 Sep 1999 12:49:17 GMT, you wrote:

>I haven't heard of this one. It might be worth investigating.
>
>If you use the "RENAME table1 TO table2;" command, then Oracle actually
>moves the data. It doesn't just change the name. This can cause

rename table1 to table2 JUST UPDATES THE DICTIONARY!! it does not move the data. The ability to actually move a table was introduced in Oracle8i, release 8.1 -- prior to that -- nothing moves a table short of copying it and dropping it.

try this on a big table in 8i:

tkyte_at_8i> rename t to t_new;
Table renamed.
Elapsed: 00:00:00.05

tkyte_at_8i> alter table t_new move tablespace users; Table altered.
Elapsed: 00:00:02.16

rename is fast and in place.

>performance problems, especially if the table is very big. You will also
>have to verify that your constraints and indexes aren't dropped. I'm not
>sure if they get "renamed" too.
>

they don't get renamed themselves but the base table they point to is 'renamed' (and since that name is stored once in the DD and all triggers/constraints really point to object numbers -- this works fine) consider:

  1 create trigger t_trigg
  2 before insert on t_new
  3 for each row
  4 begin
  5 null;
  6* end;
tkyte_at_8i> /

Trigger created.

Elapsed: 00:00:00.15

tkyte_at_8i> select trigger_name, table_name from user_triggers;

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
T_TRIGG                        T_NEW

tkyte_at_8i> rename t_new to t;

Table renamed.

Elapsed: 00:00:00.05
tkyte_at_8i> select trigger_name, table_name from user_triggers;

TRIGGER_NAME                   TABLE_NAME
------------------------------ ------------------------------
T_TRIGG                        T

Elapsed: 00:00:00.00
tkyte_at_8i>

so they get carried along as well.

>But everyone missed the easiest solution: SYNONYMS! Why not just create
>a synonym for the table? Quick and easy.
>

thats true. that'll do it as well.

but don't be afraid of rename -- it works as you would expect, it updates the DD and thats about it.

(and it is fully documented)

>HTH,
>Brian
>
>
>Chetan Wagle wrote:
>>
>> Hi niall,
>>
>> I have heard of an undocumented command called 'alter table <name> rename
>> <newname>'
>>
>> Check it out. The other solutions won't give you your triggers and
>> referential constraints.
>>
>> HTH,
>>
>> Chetan
>> Bangalore, India
>>
>> Niall Litchfield wrote in message <7saos6$b9l$1_at_soap.pipex.net>...
>> >or if you want to create a backup of the existing one as well, for when
>> that
>> >app breaks..
>> >
>> >create table table2 as select * from table1
>> >
>> >Niall
>> >Oracle DBA Audit Commission UK
>> ><owais_anjum_at_my-deja.com> wrote in message
>> >news:7rq6uv$sb2$1_at_nnrp1.deja.com...
>> >> In article <7rpcf7$hfg$1_at_perki.connect.com.au>,
>> >> "Tom Zamani" <tom_at_taten.com.au> wrote:
>> >> > Is there any way to change the name of a table?
>> >> > Like alter table .....
>> >> > Please help me out.
>> >> > Tom
>> >> >
>> >> >
>> >>
>> >> rename table1 to table2;
>> >>
>> >> pretty straight forward hmmm :)
>> >>
>> >>
>> >> Sent via Deja.com http://www.deja.com/
>> >> Share what you know. Learn what you don't.
>> >
>> >

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 23 1999 - 08:12:36 CDT