RENAME

The RENAME statement renames a table, view, or index. The name and/or the system object name of the table, view, or index can be changed.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • If the name of the object is changed:
      • The system authority of *OBJMGT on the table, view, or index to be renamed
      • The USAGE privilege on the schema containing the table, view, or index to be renamed
    • If the system name of the object is changed:
      • The system authority of *OBJMGT on the table, view, or index to be renamed
      • The USAGE privilege and the system authority of *UPD on the schema containing the table, view, or index to be renamed
  • Database administrator authority

Syntax

Read syntax diagramSkip visual syntax diagramRENAME TABLEtable-nameview-nameINDEXindex-name TO target-identifierFOR SYSTEM NAMEsystem-object-identifierSYSTEM NAMEsystem-object-identifer

Description

TABLE table-name or view-name
Identifies the table or view that will be renamed. The table-name or view-name must identify a table or view that exists at the current server, but must not identify a catalog table or a declared temporary table. The specified name can be an alias name. The specified table or view is renamed to the new name. All privileges, constraints, indexes, triggers, views, and logical files on the table or view are preserved.

Any access plans that reference the table or view are implicitly prepared again when a program that uses the access plan is next run. Since the program refers to a table or view with the original name, if a table or view with the original name does not exist at that time, an error is returned.

INDEX index-name
Identifies the index that will be renamed. The index-name must identify an index that exists at the current server. The specified index is renamed to the new name.

Any access plans that reference the index are not affected by rename.

target-identifier
Identifies the new table-name, view-name, or index-name of the table, view, or index, respectively. target-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The target-identifier must be an unqualified SQL identifier.
SYSTEM NAME system-object-identifier
Identifies the new system-object-identifier of the table, view, or index, respectively. system-object-identifier must not be the same as a table, view, alias, or index that already exists at the current server. The system-object-identifier must be an unqualified system identifier.

If the name of the object and the system name of the object are the same and target-identifier is not specified, specifying system-object-identifier will be the new name and system object name. Otherwise, specifying system-object-identifier will only affect the system name of the object and not affect the name of the object.

If both target-identifier and system-object-identifier are specified, they cannot both be valid system object names.

Notes

Effects of the statement: The specified table, view, or index is renamed to the new name. For a renamed table, all privileges and indexes on the table are preserved. For a renamed index, all privileges are preserved.

Invalidation of packages and access plans: Any access plans that refer to that table are invalidated. For more information see Packages and access plans.

Considerations for aliases: If an alias name is specified for table-name, the table must exist at the current server, and the table that is identified by the alias is renamed. The name of the alias is not changed and continues to refer to the old table name after the rename.

There is no support for changing the name of an alias with the RENAME statement. To change the name to which the alias refers, the alias must be dropped and recreated.

Rename rules: The rename operation performed depends on the new name specified.

  • If the new name is a valid system identifier,
    • the alternative name (if any) is removed, and
    • the system object name is changed to the new name.
  • If the new name is not a valid system identifier,
    • the alternative name is added or changed to the new name, and
    • a new system object name is generated if the system object name (of the table or view) was specified as the table, view, or index to rename. For more information about generated table name rules, see Rules for Table Name Generation.

If an alias name is specified for table-name, the alias must exist at the current server, and the table that is identified by the alias is renamed. The name of the alias is not changed and continues to refer to the old table after the rename. There is no support for changing the name of an alias.

Examples

Example 1: Change the name of the EMPLOYEE table to CURRENT_EMPLOYEES:

   RENAME TABLE EMPLOYEE
   TO CURRENT_EMPLOYEES

Example 2: Change the name of the unique index using EMPNO, called XEMP1, to UXEMPNO:

   RENAME INDEX XEMP1
     TO UXEMPNO

Example 3: Rename a table named MY_IN_TRAY to MY_IN_TRAY_94. The system object name will remain unchanged (MY_IN_TRAY). 

   RENAME TABLE MY_IN_TRAY TO MY_IN_TRAY_94
     FOR SYSTEM NAME MY_IN_TRAY