Tuesday, July 28, 2020

RENAME Table or Column or Index in Oracle

RENAME Table
You can rename an existing table in any schema except the schema SYS. To rename a table you must be either DB owner or Table owner.

Use RENAME TABLE to rename a table.

Syntax: RENAME TABLE table-Name TO new-Table-Name
Ex:- RENAME TABLE EMPLOYEE TO EMPLOYEE_ACT

If you have a view or foreign key that references the table, attempts to rename it will generate an error. Also if there are any check constraints or triggers on the table, attempts to rename it will also generate an error.

RENAME Column: Use the RENAME COLUMN to rename a column in a table.
ex: RENAME COLUMN EMPLOYEE.Employee_ID TO EMP_ID;

You can use ALTER TABLE and RENAME COLUMN to modify Column data type
ALTER TABLE EMPLOYEE ADD COLUMN EMP_ID NEWTYPE
UPDATE EMPLOYEE SET EMP_ID = Employee_ID
ALTER TABLE EMPLOYEE DROP COLUMN Employee_ID
RENAME COLUMN t. Employee_ID TO EMP_ID

If a view, trigger, check constraint, foreign key constraint then attempt to rename it will generate an error. Also RENAME COLUMN is not allowed if you have any open cursors that reference the column that is being altered.

If there is an index defined on the column then you can still rename. The index will update automatically to refer by its new name

RENAME Index: Use the RENAME Index to rename an index, you cannot rename indexes in SYS schema

Ex:- RENAME INDEX EMPLOYEE_ID_INDEX TO EMP_ID_INDEX

In case if there are any open cursors that reference the index being renamed then RENAME INDEX is not allowed .

Thanks
Satishbabu G, Oracle ACE

5 comments:

  1. Cognex is the AWS Training in Chennai. Cognex is the best training insitute in chennai providing online and offline courses. AWS is the best selection to grow cloud skills

    ReplyDelete
  2. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔ Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai


    ReplyDelete
  3. One of the best wines which I have every seen before we will be getting any type of bottles here too old alcohol even I'm seeing this Bagga Wines Since 20 years.At the same place in Koti Hyderabad
    Nita Wines in Churchgate, Mumbai
    Privacy Policy
    Star Wines in Powai, Mumbai
    Disclaimer
    G Town Wines L1
    Variety Wines Merchant in Juhu, Mumbai
    Bagga Wines in Putlibowli Koti

    ReplyDelete
  4. The properties in Kharkhoda industrial area are perfectly suited for emporiums, exhibitions, factories, agriculture, and warehouses, among other things.
    real estate kharkhoda sonipat hr

    ReplyDelete