Re: Alter name of a table

According to the Oracle Certified Professional DBA Certification Exam Guide, Oracle Press, Pg
162:

"To change object names in Oracle is accomplished using the rename command.
This command allows the DBA to change the name of one table to another by
taking data from one table and automatically moves it to another that is called something else. Use of rename can take a long time, depending on the
size of the table involved in the operation. The effect here can be duplicated through the use of synonyms. A synonym gives users an alternate
name with which they can use to refer to the existing table. No actual data
movement takes place, as in the rename command, which physically moves data
in the Oracle database to an object with the new name. Renaming tables can
be detrimental to performance, especially if the object being renamed is large."

Is this book in error? Or is this just for version 7?

Thanks,
Brian

Thomas Kyte wrote:
>
> 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 Fri Sep 24 1999 - 07:49:50 CDT