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.
GouthamAnandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
The documentation for RENAME states that the object being renamed must be in your own schema.  It seems a little vague, but I am guessing based on the message you received is that it has to be in the schema you are connected to.  Changing current schema doesn't seem to work on that.  The alternate syntax that should work as SYS (at least it did when I tried it) is:

ALTER TABLE FIA.ABC RENAME TO XYZ;

NOTE:  That does not change the owner of the table, just the table name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GouthamAnandAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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)?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

GouthamAnandAuthor Commented:
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;
johnsoneSenior Oracle DBACommented:
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.
GouthamAnandAuthor Commented:
Sorry. I used wrongly.

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

Thank you very much for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.