• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » alter table rename column in oracle database

alter table rename column in oracle database

September 14, 2020 by techgoeasy Leave a Comment


Oracle allows you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column i,e alter table rename column in oracle

Syntax

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Lets see this example

SQL> CREATE TABLE DEPT_MASTER (
dept_nr NUMBER UNIQUE,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date
); 
Table created.

SQL> Desc DEPT_MASTER
alter table rename column in oracle database

Lets rename the column now

SQL> ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NR to DEPT_NR_NEW;
Table altered.

SQL>Desc DEPT_MASTER
alter table rename column in oracle database

Limitation and What is Allowed

(1) You cannot multiple rename in the single statement

Lets see with the example.

SQL> ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NR_NEW to DEPT_NR, DEPT_NAME to DEPT_N;
2 ALTER TABLE DEPT_MASTER
*
ERROR at line 1:
ORA-23290: This operation may not be combined with any other operation

SQL> ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NR_NEW to DEPT_NR,COLUMN DEPT_NAME to DEPT_N;
2 ALTER TABLE DEPT_MASTER
*
ERROR at line 1:
ORA-23290: This operation may not be combined with any other operation

So you get the ORA error when to try to include two column in it. You have execute separate statement to change the multiple columns.

ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NR_NEW to DEPT_NR;

ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NAME to DEPT_N;

(2) You can rename the column even we have primary key, constraint present on it .

It will automatically start pointing to new column name

SQL> CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2) check(SAL > 1000),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
);
Table created.

SQL> alter table emp rename column EMPNO to EMP_NO;
Table altered.

SQL> desc emp
Name Null? Type
---  ----   ---
EMP_NO 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)

SQL> alter table emp rename column sal to salary;
Table altered

SQL> desc emp
Name Null? Type
--- -----  ----
EMP_NO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SALARY NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> set long 2000
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMP_NO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SALARY" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CHECK ("SALARY">1000) ENABLE,
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMP_NO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TOOLS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING

You can see from the previous example, check constraint get changed to new column name and also primary key also get changed to new column

Lets take another example with foreign key constraints also

SQL> CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
) 
Table created.

SQL> CREATE TABLE "EMP"
 ( "EMPNO" NUMBER(4,0),
 "ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
 "MGR" NUMBER(4,0),
 "HIREDATE" DATE,
 "SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
 REFERENCES "DEPT" ("DEPTNO") ENABLE
);
Table created.

SQL> insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
SQL> insert into emp values( 7698, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
SQL> insert into emp values( 7782, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
SQL>

SQL> commit;
Commit complete.
SQL> ALTER TABLE DEPT
RENAME COLUMN DEPTNO to DEPTNO_NEW;
Table altered.

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO_NEW") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TOOLS"

We can see from previous example, the foreign key constraint start point to new column name automatically.

(3) You can rename the column , if you have procedure, views on that table and column.

But after the rename, these object will become invalid and you have to modify the view and package to compile it.Lets see this with example

SQL> CREATE TABLE DEPT_MASTER (
dept_nr NUMBER UNIQUE,
dept_name varchar2(100) NOT NULL,
dept_status NUMBER(1,0) NOT NULL,
created_at date
);
Table created.

SQL> create view dept_vw as select dept_nr,dept_name from DEPT_MASTER;
View created.

SQL> ALTER TABLE DEPT_MASTER
RENAME COLUMN DEPT_NR to DEPT_NR_NEW; 
Table altered.

SQL> select status from user_objects where object_name='DEPT_VW';
STATUS
------
INVALID

SQL> alter view DEPT_VW compile;
Warning: View altered with compilation errors.

SQL> create or replace view dept_vw as select dept_nr_new,dept_name from DEPT_MASTER;
View created.

SQL> select status from user_objects where object_name='DEPT_VW';
STATUS
------
VALID

Hope you like post on alter table rename column in oracle. Please do provide feedback

Also Reads
alter table in oracle : Alter table in oracle is used to modify column , drop and add constraints ,change datatype of the table column , change the table storage parameters
alter table add column oracle : Useful insight into How to alter table add column oracle. Details about fast add column feature introduced in oracle 11g also given
oracle create table : Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key
alter table drop column in oracle : Check out how to drop column using alter table drop column oracle, alter table set unused column oracle and alter table drop unused columns
how to check all constraints on a table in oracle : how to check all constraints on a table in oracle using dba_constraints and dba_cons_columns data dictionary views in Oracle database
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm


Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us