Renaming Object

As the title states, it just re-name from old object name to a new object name. Not all Oracle objects can be re-named easily. Oracle Table, View, Sequence, Synonym can be renamed using Oracle RENAME statement.
RENAME old_object_name TO new_object_name;
When renaming database object, Oracle automatically transfers Constraints, Indexes, and Grants from Old to New object. It does not resolve INVALID objects caused by RENAME. Functions, Procedures, View, Synonym become INVALID therefore, the issue should be resolved by renaming the underlying objects.

Not all objects can be renamed using RENAME statement, I don't know why either, I am curious to know if anyone has answers to why? How do we re-name those objects which can't be renamed? The only way (which is not the best) is to drop and re-create it which I am not a fan of. This is a little risky process therefore, you should have a backup copy of SQL script of original objects. Along with the SQL backup script, get DDL for all the GRANTS on the objects. Dropping objects will drop GRANTS therefore, generate DDL script for GRANTS before dropping an object.

How do you generate GRANT's DDL? I will teach you the Brute force techniques which is what I prefer when I don't feel like the system packages.
  ||'  '
  ||' ON '
  ||' TO '
  ||';' table_name
FROM dba_tab_privs
WHERE table_name ='TBL_NAME';
There are Oracle DBMS packages for generating DDL like dbms_metadata.get_ddl, .get_grants_ddl etc. These packages are recommenced if you would like to know more  on DDL. Use RENAME where possible, if not drop and re-create using new name. Always save GRANTS and OBJECT DDL before dropping.
Interested in working with me? I can be reached at pbaniya04[at] for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!


  1. Thanks for sharing this informative content.,
    Leanpitch provides online training in Scrum Master Certification , everyone can use it wisely.
    Join Leanpitch 2 Days CSM Certification Workshop in different cities.

    Scrum master certification online
    csm certification online


Powered by Blogger.