CODEWITHSUNDEEP
oracle-databaserenamealter
Ghifari Dwiki Ramadhan
Ghifari Dwiki Ramadhan

Reputation: 40

Rename Oracle Table in Other Schema

How to rename the table which is under other schema? For example, my user is A, I want to rename table_1 to table_1_temp which both supposed to be under the same schema, let's say schema B. Is that possible?

I tried this:

RENAME B.table_1 TO B.table_1_temp

and

RENAME B.table_1 TO table_1_temp

but got this error:

ORA-01765: specifying owner's name of the table is not allowed

ALTER statement is also doesn't work:

ALTER TABLE B.table_1 RENAME TO table_1_temp

got this error:

ORA-01031: insufficient privileges

and this

ALTER TABLE B.table_1 RENAME TO B.table_1_temp

ORA-14047 ALTER TABLE|INDEX RENAME may not be combined with other operations

Upvotes: 1

Views: 4404

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

"Insufficient privilege" is the keyword here. You can't modify other users' objects unless you're allowed to; of course you can't - how would it look like if anyone messes up with your schema?

Owner itself can't grant that privilege; it is a strong one and user - who is granted such a privilege - must be trustworthy as it can alter any other user's tables.

Have a look at the following example. There are two users in my database: mike (who owns a table) and scott (who should rename mike's table).

Mike and his table:

SQL> show user
USER is "MIKE"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BEDIENSTETER                   TABLE

Connect as scott and try to rename mike's table (rename won't work here; it is supposed to be used in your own schema):

SQL> connect scott/tiger
Connected.
SQL> alter table mike.bediensteter rename to test;
alter table mike.bediensteter rename to test
*
ERROR at line 1:
ORA-01031: insufficient privileges

Right; insufficient privileges. What privilege is it? ALTER ANY TABLE. So let's grant it, connected as a privileged user (such as SYS in my XE database):

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> grant alter any table to scott;

Grant succeeded.

OK; back to scott, repeat the action:

SQL> connect scott/tiger
Connected.
SQL> alter table mike.bediensteter rename to test;

Table altered.

Succeeded! Let's see what we've done:

SQL> connect mike/lion
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

SQL>

Right; table is now renamed.

Upvotes: 1

ThomasT
ThomasT

Reputation: 390

Rename Oracle Table or View as you can see on this post based on Pop's answer, the RENAME statement only works for table in the same schema.

The ALTER TABLE B.table_1 RENAME TO table_1_temp looks to be the best solution, have you tried giving your user account more privileges ?

Upvotes: 1

Related Questions