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