Re: rename table - insufficient privilege (ORA-01031)

"Alexandr Molochnikov" <NOBODY_at_NO_SPAM.com> wrote in message news:<KEr4c.162525$A12.49680_at_edtnps84>...
> Hello group,
>
> I am very new to Oracle, and just run into something that I cannot
> understand: I can create and drop a table, but not rename it.
>
> Here is what my program does:
>
> 1. create a new user with DBA privileges, and log in as this user:
>
> create user alex identified by *****
> grant connect, dba to alex
>
> 2. under "alex", create another user:
>
> create user olympia identified by dummy
>
> (I do this for the sake of having another schema; the user does not have to
> be able to log in - hence no connect privilege for "olympia").
>
> 3. still in "alex" login session, change the current schema to olympia:
>
> alter session set current_schema = olympia
>
> 4. create and populate a table:
>
> create table names(fname varchar(20), lname varchar(20), sf_id int)
> insert into names (fname,lname,sf_id) values ('Alex', 'Molochnikov', 1)
>
> The table goes into the schema named "olympia", but is accessible to "alex".
>
> 5. try renaming the table:
>
> rename names to names1
>
> At this point, Oracle responds:
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> What baffles me is that I can drop the table, or rename a column in it,
> without a hitch. I gave ALTER ALL TABLES privilege to "alex", but it did not
> help. Search on Google found nothing.
>
> Just before posting this, I tried logging as SYSTEM (SYSDBA) - still cannot
> rename the table.
>
> Any ideas?
>
> TIA
>
> Alex Molochnikov
> Gestalt Corporation
> www.gestalt.com

Alex, from the 9.2 Admin manual "To rename an object, it must be in your schema." I do not think just switching your effective schema is good enough. If would appear you have to be the owner. There is an alter table rename to clause that you should be able to use to accomplish the same task.

HTH -- Mark D Powell -- Received on Sun Mar 14 2004 - 16:44:14 CET