| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to rename a column name?
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 exportedExport 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
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 Thu Sep 16 1999 - 06:40:33 CDT
 |
 |