Rename Oracle Table or View

Question

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

@skaffman: lmgtfy.com/ is just the thing you need :) – OMG Ponies Sep 09 '09 at 20:27 — OMG Ponies, Sep 09 '09 at 20:27

score 94 · Accepted Answer · answered May 07 '09 at 15:39

94

ALTER TABLE mytable RENAME TO othertable

In Oracle 10g also:

RENAME mytable TO othertable

answered May 07 '09 at 15:39

Quassnoi

413,100
91
616
614

1

In oracle versions older than 10g, views can no longer be renamed. Drop/recreate is the only possible way to do this now. Check separate answer for an example. – Wouter Sep 08 '15 at 14:14

Jeffrey Kemp · Answer 2 · 2015-09-09T10:35:56.130

30

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)

edited Sep 09 '15 at 10:35

answered May 08 '09 at 03:36

Jeffrey Kemp

59,135
14
106
158

1

in 11g, the initial example you have there didn't work for me, but the second did... – rogerdpack Apr 24 '13 at 16:33
Note that RENAME still works in 11g and 12g - refer http://docs.oracle.com/database/121/SQLRF/statements_9020.htm#SQLRF01608 – Jeffrey Kemp Sep 09 '15 at 10:39

score 12 · Answer 3 · answered May 07 '09 at 16:42

12

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.

answered May 07 '09 at 16:42

Pop

3,932
1
17
12

score 1 · Answer 4 · edited Aug 29 '12 at 12:37

1

One can rename indexes the same way:

alter index owner.index_name rename to new_name;

edited Aug 29 '12 at 12:37

user272735

10,473
9
65
96

answered Nov 12 '09 at 13:23

Maurício

19
1

1

Would have been more suitable as a comment, not an answer. – Wouter Sep 08 '15 at 14:27

score 1 · Answer 5 · answered Sep 08 '15 at 14:21

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;

Rename Oracle Table or View

5 Answers5

Linked

Related