How to Change the Owner of Tables

The following statement is wrong if you are trying to change the owner of a table via rename clause:
SQL> alter table hr.employees rename to sh.employees;
alter table hr.employees rename to sh.employees
                                     *
ERROR at line 1:
ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

The error told us that the rename clause must be within the current schema.

Although you can use move clause to migrate a table from one tablespace to another, you can't use move clause to actually move a table from one schema to another. There is no short-cut statement to change the owner of a table.

In fact, to change the owner of a table is a process of recreating table, you can use any of the following ways to do it:

1. Use legacy imp to import table from one user to another.
$ imp "/ as sysdba" file=/dev/rmt/8m tables=EMPLOYEES fromuser=HR touser=SH
In this case, the dump file is coming from a tape.


2. Use Data Pump to remap the schema and do a self-import in network mode.
First, you must have a database link to point to the database itself.
$ sqlplus system/password
...
SQL> create database link primdb_self connect to system identified by password using 'primdb1';
Then, import the table with the data pump utility impdp.
$ impdp system/password remap_schema=HR:SH tables=HR.EMPLOYEES network_link=primdb_self
You may refer to my post for more details:
How to Self-Import by Data Pump in Network Mode


3. Use CTAS to recreate a table.
SQL> create table_name sh.employees as select * from hr.employees;
This approach is easy to understand by developers. But the drawback is that you have to rebuild the constraints (indexes) by yourself.