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.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
.-TABLE-. >>-RENAME--+-+-------+--+-table-name-+-+------------------------> | '-view-name--' | '-INDEX--index-name---------' >--TO--+-target-identifier--+-------------------------------------------+-+->< | '-FOR SYSTEM NAME--system-object-identifier-' | '-SYSTEM NAME--system-object-identifer-----------------------------'
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.
Any access plans that reference the index are not affected by rename.
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.
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 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.
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