Re: How to rename a column name?

In Enterprise Manager Change Management Pack, I'm pretty sure there is a rename column function. This was about a year ago, but if memory serves me right I seem to recall it takes about no time to execute.

Tom, can you verify? I don't have the CMP anymore.

Thanks,

Yosi

Thomas Kyte wrote:

> A copy of this was sent to AleX <korrozia_at_my-deja.com>
> (if that email address didn't require changing)
> On Thu, 16 Sep 1999 02:15:55 GMT, you wrote:
>
> >In article <37de951e.458746_at_news.siol.net>,
> > jmodic_at_src.si (Jurij Modic) wrote:
> >> On Mon, 13 Sep 1999 17:18:24 -0500, Tapan Trivedi
> >> <tapan.trivedi_at_abbnm.com> wrote:
> >>
> >> >You can export the table. Create a table of the required structure
> >that
> >> >you like and import the data into the new table.
> >> > Tapan
> >>
> >> This will result in "ORA-904: invalid column name". You can't import
> >> into existing table if the table structure (column names, data types)
> >> is not exactly the same as the one in the dmp file.
> >>
> >> Regards,
> >>
> >> Jurij Modic <jmodic_at_src.si>
> >
> >
> >Not true. You can export/import as long as the starget table has all the
> >columns that the source table has.
>
> TRUE -- you cannot use imp/exp to rename a column. The target table must have
> all of the columns -- including their names. Consider:
>
> tkyte_at_8i> desc emp
> Name Null? Type
> ----------------------------------------------------- --------
> EMPNO NOT NULL NUMBER(4)
> ENAME VARCHAR2(10)
> JOB VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL NUMBER(7,2)
> COMM NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> tkyte_at_8i> exit
> Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> $ exp userid=tkyte/tkyte tables=emp
>
> Export: Release 8.1.5.0.0 - Production on Thu Sep 16 07:37:42 1999
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
> Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
> Export done in US7ASCII character set and US7ASCII NCHAR character set
>
> About to export specified tables via Conventional Path ...
> . . exporting table EMP 14 rows exported
> Export terminated successfully without warnings.
>
> $ sqlplus tkyte/tkyte
>
> SQL*Plus: Release 8.1.5.0.0 - Production on Thu Sep 16 07:37:49 1999
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> tkyte_at_8i> alter table emp drop column deptno;
> Table altered.
>
> tkyte_at_8i> alter table emp add deptno_new number(2);
> Table altered.
>
> tkyte_at_8i> exit
> Disconnected from Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> $ imp userid=tkyte/tkyte full=y ignore=y
>
> Import: Release 8.1.5.0.0 - Production on Thu Sep 16 07:38:23 1999
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
> Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> Export file created by EXPORT:V08.01.05 via conventional path
> import done in US7ASCII character set and US7ASCII NCHAR character set
> . importing TKYTE's objects into TKYTE
> . . importing table "EMP"
> IMP-00058: ORACLE error 904 encountered
> ORA-00904: invalid column name
> Import terminated successfully with warnings.
>
> ORA-904 -- trying to rename a column.
>
> the way to rename a column is to rename the table, and then create a view with
> the 'correct' name. That is the only way to rename a column.
>
> You can do the 'drop and reload' as well but thats not really renaming the
> column....
>
> the view works 100% of the time, performs as well as 'the real table' would.
>
> --
> 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 17 1999 - 11:28:47 CDT