OraFAQ Forum: Server Utilities » oracle table name

Home » RDBMS Server » Server Utilities » oracle table name

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle table name [message #71536]

Wed, 20 November 2002 23:40

jaswinder
Messages: 2
Registered: May 2002

Junior Member

How Can we change the column name of a table in SQL*?

Report message to a moderator

Re: oracle table name [message #71537 is a reply to message #71536]

Thu, 21 November 2002 01:13

Barry Dogger
Messages: 4
Registered: September 2002

Junior Member

I do not think that is possible.
If you want to rename a column, create a back-up table.
Then delete the original table. Then recreate the original table with the name column name from the backup table.

Example.

create table My_data
( name varchar2(20)
, address varchar2(20)
, citie varchar2(20)
)

SQL> desc my_data
Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
CITIE VARCHAR2(20)

create table my_data_bck as select * from my_data;

SQL> desc my_data_bck
Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
CITIE VARCHAR2(20)

drop table my_data;

create table my_data
( name
, address
, city
)
as select * from my_data_bck

SQL> desc my_data
Name Null? Type
------------------------------- -------- ----
NAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
CITY VARCHAR2(20)

CAUTION:
I do not know what consequences this may have for constraints etc. I advise you to experiment with it a little.

Hope this helps.

Regards,

elnbado

Report message to a moderator

Re: oracle table name [message #71540 is a reply to message #71536]

Thu, 21 November 2002 06:19

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

as elnbado said, u cannto directly do this ( in 9i there are some options).
another method to do is

SQL> desc dept
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOC                                                            VARCHAR2(13)

SQL> alter table dept add (location varchar2(13));

Table altered.

SQL> update dept set location=dname;

6 rows updated.

SQL> alter table dept drop column loc;

Table altered.

SQL> desc dept;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPTNO                                                         NUMBER(2)
 DNAME                                                          VARCHAR2(14)
 LOCATION                                                       VARCHAR2(13)

SQL> select * from dept;

    DEPTNO DNAME          LOCATION
---------- -------------- -------------
        10 ACCOUNTING     ACCOUNTING
        20 RESEARCH       RESEARCH
        30 SALES          SALES
        40 OPERATIONS     OPERATIONS
        79 IS             IS
        33 MIS            MIS

6 rows selected.

SQL>

Report message to a moderator

Previous Topic:	maxentents in a tablespace
Next Topic:	Error 6 initializing SQL*Plus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 14 06:44:13 CDT 2024