Monday 9 June 2014

ORA-00903, ORA-03001 and ORA-01765 When Renaming a Table

Using Oracle 11.2.0.3.0, I was trying to rename a table. Not something I do everyday.

On searching Google for the syntax I found Oracle documentation for "RENAME TABLE table-Name TO new-Table-Name" which gave me the error "ORA-00903: invalid table name".
This turns out to be a mistake in the documentation and the command should not have the TABLE keyword in it. IE:  "RENAME table-Name TO new-Table-Name"

This now gave me error  "ORA-03001: unimplemented feature". Now I am not logged on as the table owner but do have the owner set as my current schema. "ALTER SESSION SET CURRENT_SCHEMA=USERNAME;"

Next logical step was to include the username in the command. "RENAME owner.table-Name TO new-Table-Name", which gave "ORA-01765: specifying owner's name of the table is not allowed"

A bit more searching and I found the following syntax which did work.

ALTER TABLE owner.table-Name RENAME TO new-Table-Name;

Table altered.... Hurray.