RENAME

Purpose

Use the RENAME statement to rename a table, view, sequence, or private synonym for a table, view, or sequence.

• Oracle automatically transfers integrity constraints, indexes, and grants on the old object to the new object.

• Oracle invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

Do not use this statement to rename public synonyms. Instead, drop the public synonym and then create another public synonym with the new name.

See Also: CREATE SYNONYM and DROP SYNONYM

Prerequisites

The object must be in your own schema.

Syntax

rename::=

Text description of rename

Keywords and Parameters

old

Specify the name of an existing table, view, sequence, or private synonym.

new

Specify the new name to be given to the existing object. The new name must not already be used by another schema object in the same namespace and must follow the rules for naming schema objects.

See Also: "Schema Object Naming Rules"

Example

Rename a Database Object Example

To change the name of table departments to emp_departments, issue the following statement:

RENAME departments TO emp_departments;

You cannot use this statement directly to rename columns. However, you can rename a column using this statement together with the CREATE TABLE statement with AS subquery. The following statements re-create the table static, renaming a column from oldname to newname:

CREATE TABLE temporary 
   (employee_id, start_date, end_date, job_id, dept_id) 
AS SELECT 
     employee_id, start_date, end_date, job_id, department_id
FROM job_history; 

DROP TABLE job_history; 

RENAME temporary TO job_history;