Renaming Schema Objects

To rename an object, it must be in your schema. You can rename schema objects in either of the following ways:

  • Drop and re-create the object

  • Rename the object using the RENAME statement

  • Rename the object using the ALTER ... RENAME statement (for indexes and triggers)

If you drop and re-create an object, all privileges granted for that object are lost. Privileges must be regranted when the object is re-created.

A table, view, sequence, or a private synonym of a table, view, or sequence can be renamed using the RENAME statement. When using the RENAME statement, integrity constraints, indexes, and grants made for the object are carried forward for the new name. For example, the following statement renames the sales_staff view:

RENAME sales_staff TO dept_30;  

Note:

You cannot use RENAME for a stored PL/SQL program unit, public synonym, or cluster. To rename such an object, you must drop and re-create it.

Before renaming a schema object, consider the following effects:

  • All views and PL/SQL program units dependent on a renamed object become invalid, and must be recompiled before next use.

  • All synonyms for a renamed object return an error when used.

    See Also:

    Oracle Database SQL Language Reference for syntax of the RENAME statement