1  RENAME
   Allows the database administrator to change the name of a
   database object. This new name is then available for reference
   in other data definition statements, as well as from queries and
   routines.

                                  NOTE

      The RENAME statement may require that synonyms are enabled
      for the database. Reference the SYNONYMS ARE ENABLED clause
      of the ALTER, CREATE and IMPORT DATABASE statements.
 

2  Environment
   You can use the RENAME statement:

   o  In interactive SQL

   o  Embedded in host language programs

   o  As part of a procedure in an SQL module or other compound
      statement

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 RENAME  --+-----------------+--> oldname --> TO --> newname
           +--> CONSTRAINT  -+
           +--> DOMAIN  -----+
           +--> FUNCTION  ---+
           +--> MODULE  -----+
           +--> OUTLINE -----+
           +--> PROCEDURE  --+
           +--> PROFILE  ----+
           +--> ROLE  -------+
           +--> SEQUENCE ----+
           +--> TABLE -------+
           +--> TRIGGER  ----+
           +--> USER --------+
           +--> VIEW --------+

 

2  Arguments
 

3  newname

   The new name for this object. This name must not already exist in
   the database for this object type, nor be the name of a synonym.
   The one exception is when the synonym references the oldname
   object.

   If this is a RENAME TABLE, RENAME VIEW or RENAME SEQUENCE then
   the newname cannot be the name of an existing table, sequence or
   view.
 

3  oldname

   The name of an existing object in the database. If the object
   type keyword is specified then an object must exist of that type.
   The name may also be a synonym for an object of the specified
   type.
 

2  Examples

   Example 1: Preparing a database for RENAME statement

   The RENAME statement for most objects requires that synonyms be
   enabled. This example shows the reported error if a RENAME is
   attempted for an object that requires synonyms.

   SQL> attach 'filename personnel_sql';
   SQL> show table
   User tables in database with filename personnel_sql
        CANDIDATES
        COLLEGES
        CURRENT_INFO                    A view.
        CURRENT_JOB                     A view.
        CURRENT_SALARY                  A view.
        DEGREES
        DEPARTMENTS
        EMPLOYEES
        JOBS
        JOB_HISTORY
        RESUMES
        SALARY_HISTORY
        WORK_STATUS
   SQL> rename table EMPLOYEES to COMPANY_STAFF;
   %RDB-E-NO_META_UPDATE, metadata update failed
   -RDMS-E-UNSSYNONYM, this database does not have synonyms enabled
   SQL> disconnect all;
   SQL> alter database filename personnel_sql synonyms are enabled;

   Example 2: Renaming a table in the PERSONNEL database

   This example renames the EMPLOYEES table. The SHOW TABLE
   statement lists the new name as well as synonym with the old
   name of the table.

   SQL> attach 'filename personnel_sql';
   SQL> rename table EMPLOYEES to COMPANY_STAFF;
   SQL> show table
   User tables in database with filename personnel_sql
        CANDIDATES
        COLLEGES
        COMPANY_STAFF
        CURRENT_INFO                    A view.
        CURRENT_JOB                     A view.
        CURRENT_SALARY                  A view.
        DEGREES
        DEPARTMENTS
        JOBS
        JOB_HISTORY
        RESUMES
        SALARY_HISTORY
        WORK_STATUS
        EMPLOYEES                       A synonym for table COMPANY_STAFF
   SQL> select last_name from COMPANY_STAFF where employee_id = '00164';
    LAST_NAME
    Toliver
   1 row selected
   SQL>

   Example 3: Renaming back to the original name

   This example shows that the rename back to the original name will
   create a new synonym and remove the old synonym which had the
   same name as the tables new name.

   SQL> rename table COMPANY_STAFF to EMPLOYEES;
   SQL> show table
   User tables in database with filename personnel_sql
        CANDIDATES
        COLLEGES
        CURRENT_INFO                    A view.
        CURRENT_JOB                     A view.
        CURRENT_SALARY                  A view.
        DEGREES
        DEPARTMENTS
        EMPLOYEES
        JOBS
        JOB_HISTORY
        RESUMES
        SALARY_HISTORY
        WORK_STATUS
        COMPANY_STAFF                   A synonym for table EMPLOYEES
   SQL>
 

   Example 4: Can not rename to a name used by the same object class
   or a synonym

   The RENAME command does not allow the new name to be in use by
   the same class of objects, or by a synonym. In particular tables,
   views and sequences share the same name space.

   SQL> rename view CURRENT_INFO to CURRENT_SALARY;
   %SQL-F-REL_EXISTS, Table CURRENT_SALARY already exists in this database or
   schema
   SQL> create sequence CURRENT_INFORMATION;
   SQL> rename view CURRENT_INFO to CURRENT_INFORMATION;
   %RDB-E-NO_META_UPDATE, metadata update failed
   -RDMS-E-SEQEXTS, there is another sequence named "CURRENT_INFORMATION" in
   this database
   -RDMS-F-RELNOTCHG, relation CURRENT_INFO has not been changed