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.
1 – 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
(B)0[m[1;4mRENAME[m[1m qqwqqqqqqqqqqqqqqqqqwqq> oldname qq> [1;4mTO[m[1m qq> newname[m
[1mtqq> [1;4mCONSTRAINT[m [1mqu[m
[1mtqq>[m [1;4mDOMAIN[m [1mqqqqqu[m
[1mtqq>[m [1;4mFUNCTION[m [1mqqqu[m
[1mtqq>[m [1;4mMODULE[m [1mqqqqqu[m
[1mtqq>[m [1;4mOUTLINE[m [1mqqqqqu[m
[1mtqq>[m [1;4mPROCEDURE[m [1mqqu[m
[1mtqq>[m [1;4mPROFILE[m [1mqqqqu[m
[1mtqq>[m [1;4mROLE[m [1mqqqqqqqu[m
[1mtqq>[m [1;4mSEQUENCE[m [1mqqqqu[m
[1mtqq>[m [1;4mTABLE[m [1mqqqqqqqu[m
[1mtqq>[m [1;4mTRIGGER[m [1mqqqqu[m
[1mtqq>[m [1;4mUSER[m [1mqqqqqqqqu[m
[1mmqq> [1;4mVIEW[m [1mqqqqqqqqj[m
3 – Arguments
3.1 – 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.2 – 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.
4 – 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