Home » SQL & PL/SQL » SQL & PL/SQL » RENAME COLUMN NAME
RENAME COLUMN NAME [message #126131] Fri, 01 July 2005 03:07 Go to next message
yarlagadda_01
Messages: 13
Registered: June 2005
Location: india
Junior Member
Is it possible to rename column name in the existing table?
if yes then what happens to the index created on that column?

[Updated on: Fri, 01 July 2005 03:18]

Report message to a moderator

Re: RENAME COLUMN NAME [message #126135 is a reply to message #126131] Fri, 01 July 2005 03:29 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sorry,

It is not possible to change the column name of an existing table (directly) but dtatatype.

I don't know the case with 10g.

Rajuvan
Re: RENAME COLUMN NAME [message #126141 is a reply to message #126135] Fri, 01 July 2005 04:06 Go to previous messageGo to next message
yarlagadda_01
Messages: 13
Registered: June 2005
Location: india
Junior Member
Thank You for your reply,
But it is possible to rename the column in 9i also.
ALTER TABLE table_name RENAME COLUMN old_name to new_name.
But i need to know abt the index on that column.
Re: RENAME COLUMN NAME [message #126157 is a reply to message #126141] Fri, 01 July 2005 05:35 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Testing would seem to show that the index is maintained on the renamed column.:

Create table t1(C1 Number, c2 varchar2(30));
create unique index idx1 on t1 (c1);
ALTER TABLE T1 RENAME COLUMN C1 to C3;

insert into t1 values(1,'a');
insert into t1 values(2,'a');
insert into t1 values(3,'a');
insert into t1 values(4,'a');
insert into t1 values(5,'a');
insert into t1 values(6,'a');
insert into t1 values(7,'a');


select c3 from t1 where c3 = 5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13) 	 
1 INDEX (UNIQUE SCAN) OF 'IDX1' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=13)

Also, here is the documentation's take on it:

Quote:

rename_column_clause
Use the rename_column_clause to rename a column of table. The new column
name must not be the same as any other column name in table.
When you rename a column, Oracle Database handles dependent objects as follows:
 Function-based indexes and check constraints that depend on the renamed
column remain valid.
 Dependent views, triggers, domain indexes, functions, procedures, and
packages are invalidated. Oracle Database attempts to revalidate them when
they are next accessed, but you may need to alter these objects with the new
column name if revalidation fails.
Restrictions on Renaming Columns
 You cannot combine this clause with any of the other column_clauses in the
same statement.
 You cannot rename a column that is used to define a join index. Instead you
must drop the index, rename the column, and re-create the index.

HTH
Jim

[Updated on: Fri, 01 July 2005 05:46]

Report message to a moderator

help [message #126202 is a reply to message #126131] Fri, 01 July 2005 09:01 Go to previous messageGo to next message
kavitharaman
Messages: 8
Registered: June 2005
Junior Member
thanks for the reply.where can i get problems on subqueries?for me to solve.any links??
Re: help [message #126207 is a reply to message #126202] Fri, 01 July 2005 09:23 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi
Download links for documentation can be found here http://tahiti.oracle.com/
You'll need to sign up, but it's free.
Jim
Previous Topic: Fetching BLOB columb .
Next Topic: Can we extract data from two different distant oracle databases in the same procedure
Goto Forum:
  


Current Time: Fri Oct 24 08:53:32 CDT 2014

Total time taken to generate the page: 0.11588 seconds