|Oracle® Database SQL Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
Caution:You cannot roll back a
Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object.
Oracle Database invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.
The object must be in your own schema.
Specify the name of an existing table, view, sequence, or private synonym.
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.
You cannot rename a public synonym. Instead, drop the public synonym and then re-create the public synonym with the new name.
You cannot rename a type synonym that has any dependent tables or dependent valid user-defined object types.
See Also:"Schema Object Naming Rules"
Renaming a Database Object: Example The following example uses a copy of the sample table
hr.departments. To change the name of table
emp_departments, issue the following statement:
RENAME departments_new TO emp_departments;
You cannot use this statement directly to rename columns. However, you can rename a column using the
Another way to rename a column is to use the
RENAME statement together with the
TABLE statement with
subquery. This method is useful if you are changing the structure of a table rather than only renaming a column. The following statements re-create the sample table
hr.job_history, renaming a column from
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;
Any integrity constraints defined on table
job_history will be lost in the preceding example. You will have to redefine them on the new
job_history table using an