Oracle7 Server SQL Reference

RENAME

Purpose

To rename a table, view, sequence, or private synonym.

Prerequisites

The object must be in your own schema.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the object's creation label or you must satisfy one of the following criteria:

If the object's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the object's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the object's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

old

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

new

is the new name to be given to the existing object.

Usage Notes

This command changes the name of a table, view, sequence, or private synonym for a table, view, or sequence. 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 defined in the section "Object Naming Rules"

Integrity constraints, indexes, and grants on the old object are automatically transferred to the new object. Oracle7 invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

You cannot use this command to rename public synonyms. To rename a public synonym, you must first drop it with the DROP SYNONYM command and then create another public synonym with the new name using the CREATE SYNONYM command.

You cannot use this command to rename columns. You can rename a column using the CREATE TABLE command with the AS clause. This example recreates the table STATIC, renaming a column from OLDNAME to NEWNAME:

CREATE TABLE temporary (newname, col2, col3)

	AS SELECT oldname, col2, col3 FROM static

DROP TABLE static

RENAME temporary TO static

Example

To change the name of table DEPT to EMP_DEPT: