Re: Renaming a column

WARNING: renaming a table by dropping and recreating has some major pitfalls.

Better think twice before you issue a DROP TABLE because Oracle does a lot of hidden things behind your back.

When you drop a table, you also drop:
  indexes
  constraints
  triggers
  grants
  comments (table and column)
  snapshot logs
  auditing settings on that object

The following may be invalidated:
  views
  synonyms
  stored procedures referencing table

Most people remember that constraints and indexes disappear with the table but forget about the rest of the list.

Whether you use
  "CREATE TABLE X; INSERT INTO X"
or
  "CREATE TABLE X AS SELECT"
or
  SQL*Plus "COPY FROM "
... none of these will handle the issues mentioned above.

Think you're smarter than the average bear by using table level export and import? That method will preserve your constraints and indexes but not your snapshot logs or auditing settings.

I tried to remember all the side effects but I'm sure I left out something. It's tricker than it looks.

In article <01bdce96$39d1d0f0$3e8480c3_at_danil>,   "Danil Krasnov" <danil_at_maginfo.net> wrote:
> Hi!
>
> example:
> Assume, we have table TABLE1 (A1 varchar2, A2 number) and wish to rename
> column A1 to B1.
> So, we do:
>
> create table TABLE2(B1 varchar2, A2 number);
> insert into TABLE2(B1 varchar2, A2 number) select A1, A2 from TABLE1;
> drop table TABLE1;
> rename TABLE2 to TABLE1;
>
> That's all.
>
> May be someone will suggest shorter way? Will be thankful.
>
> Danil Krasnov
> danil_at_maginfo.net
>
> Lak Nadella <lak_at_more.net> wrote in article <35DF8E3C.89CCE6B2_at_more.net>...
> > Could someone give me pointers as to how I can rename a column in a
> > table without dropping the table and recreating it?
> > TIA
> > Lak
> >
> >
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Aug 25 1998 - 10:22:55 CDT