CODEWITHSUNDEEP
oracle-databasetable-rename
Steven
Steven

Reputation: 13769

Rename Oracle Table or View

What is the syntax to rename a table or view in Oracle?

Upvotes: 69

Views: 206877

Answers (5)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

To rename a table you can use:

RENAME mytable TO othertable;

or

ALTER TABLE mytable RENAME TO othertable;

or, if owned by another schema:

ALTER TABLE owner.mytable RENAME TO othertable;

Interestingly, ALTER VIEW does not support renaming a view. You can, however:

RENAME myview TO otherview;

The RENAME command works for tables, views, sequences and private synonyms, for your own schema only.

If the view is not in your schema, you can recompile the view with the new name and then drop the old view.

(tested in Oracle 10g)

Upvotes: 31

Wouter
Wouter

Reputation: 1987

Past 10g the current answer no longer works for renaming views. The only method that still works is dropping and recreating the view. The best way I can think of to do this would be:

SELECT TEXT FROM ALL_VIEWS WHERE owner='some_schema' and VIEW_NAME='some_view';

Add this in front of the SQL returned

Create or replace view some_schema.new_view_name as ...

Drop the old view

Drop view some_schema.some_view;

Upvotes: 1

Maurício
Maurício

Reputation: 19

One can rename indexes the same way:

alter index owner.index_name rename to new_name;

Upvotes: 1

Pop
Pop

Reputation: 4022

In order to rename a table in a different schema, try:

ALTER TABLE owner.mytable RENAME TO othertable;

The rename command (as in "rename mytable to othertable") only supports renaming a table in the same schema.

Upvotes: 12

Quassnoi
Quassnoi

Reputation: 425371

ALTER TABLE mytable RENAME TO othertable

In Oracle 10g also:

RENAME mytable TO othertable

Upvotes: 102

Related Questions