asked on

ORA-03001: unimplemented feature

Hi,

I have a table abc in scott user. I need to rename it to xyz.

When I execute the below command in scott user,
RENAME abc TO xyz.
it got successfully executed.
But when I execute in SYS user as below
ALTER SESSION SET CURRENT_SCHEMA=FIA;
RENAME abc TO xyz.

I am getting the below error.
ORA-03001: unimplemented feature

Can you please let me know how can overcome this?

Because I need to give this script to DBA who executes as sys user.

Thanks.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GouthamAnand

ASKER

I am getting the error when I specify the owner name before the table name as below
ORA-01765: specifying owner's name of the table is not allowed

johnsone

I get that when I use:

RENAME FIA.ABC TO XYZ;

Not when I used the ALTER TABLE syntax that I posted.

What version are you running (full 4 numbers please)?

GouthamAnand

ASKER

I could achieve as
create table xyz as select * from abc;
and creating all the constraints and dependents on abc to xyz manually and then
drop table abc;

johnsone

You could also do it as:

create procedure fia.rename_table
begin
execute immediate 'rename abc to xyz';
end;
/
exec fia.rename_table;
drop procedure fia.rename;

DBAs shouldn't be connecting as the SYS use to do these kinds of things anyway. Definitely an auditing failure. Everywhere I have been DBAs would connect as the schema owner to make any kind of structural changes. They should be the only ones that know the password anyway.

GouthamAnand

ASKER

Sorry. I used wrongly.

When I use with alter I did not get the issue.

Thank you very much for the help.