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

11 comments:

  1. في مكة المكرمة نمتلك افضل شركة تقدم خدمات منزلية جيدة مثل خدمات نقل العفش تجدنا ارخص شركة نقل عفش بمكة لخدمات نقل الاثاث باحترافية وتوجد في مكه افضل شركه مكافحه حشرات بمكه تقدم خدمات رخيصة وتقدم خدمات جيدة وتمتلك عمالة مدربة ومبيدات حشرية آمنة ومضمونة وفعالة وقد تحتاج الى افضل شركة تنظيف كنب بمكة المكرمة المكرمة وهي تقوم بتعقيم منزلك بعد اعمال مكافحة الحشرات داخل المنزل وايضا تحتاج الى افضل شركة تنظيف منازل بمكة المكرمة المتميزة للقيام بأعمال تنظيف المنزل سواء كان مفروشا او جديدا وقبل هذا تحتاج الى تنظيف خزان مياهك بالتواصل مع افضل شركة تنظيف خزانات بمكة التي تهتم وتحافظ على خزانك من التلوث وبالتالي تستخدم مياه نظيفة

    ReplyDelete
  2. اقوى الخدمات الخاصة بأعمال نقل العفش مع الفك والتركيب في الرياض يقدمها موقع خدماتي الذي يضم افضل شركة نقل عفش بالرياض التي تقدم خدماتها بمنتهى الاحترافية والدقة معتمدين على فريق عمل مدرب جيدا ونجارين متخصصين لتقديم خدمات شركات نقل الاثاث بالرياض باحترافية عالية وجودة غير مسبوقة

    ReplyDelete
  3. Packers And Movers Bangalore Local Household Shifting Service, Get Free Best Price Quotes Local Packers and Movers in Bangalore List, Compare Charges, Save Money And Time at
    Local Packers And Movers Bangalore

    ReplyDelete
  4. Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ???Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @
    Packers And Movers Gurgaon

    ReplyDelete
  5. Packers and Movers Ahmedabad - We Provide ***Best Service Providers, Safe, Reliable, Affordable, Trusted ###Movers and Packers in Ahmedabad List, Household Shifting, Office Relocation: Choose Top Verified Packers and Movers Ahmedabad Compare ???Shifting Service Chrages, Price Quotation, Rate List Charts and Save Money and Time @ Packers and Movers Ahmedabad

    ReplyDelete
  6. Get Shifting/Relocation Quotation from ###Packers and Movers Delhi. Packers and Movers Delhi 100% Affordable and Reliable ***Household Shifting Services. Compare Transportation Charges and Save Time, Verified and Trusted Packers and Movers in Delhi, Cheap and Safe Local, Domestic House Shifting @ Packers And Movers Delhi

    ReplyDelete
  7. We Provide Local Packers And Movers Pune List for Get Best Price Quotes, Compare Charges, Save Money And Time, Top Household Shifting Services @ Packers And Movers Pune

    ReplyDelete
  8. 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
  9. Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔✔✔Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @ Packers And Movers Jaipur

    ReplyDelete