Tag Archives: ORA-14047

Can you move a table between schemas ?

Many reasons can raise the need to change a table owner or to move a table from one user (schema) to another . regardless of reason, one would expect that Oracle will provide an easy way to move a table to a different schema.

well, the short answer is NO . there is no direct method to change the table owner.

If you try something like

RENAME table_name to new_schema.table_name ;

Oracle Will return ORA-01765: specifying table’s owner name is not allowed
While you can workaround this error by omitting the schema name. However, in our case, this is exactly what we want to do so it is still a problem.

Similarly, the ALTER TABLE command will not do the work as well.

ALTER TABLE source_schema.table_name RENAME TO new_schema.table_name;

While a different error message (ORA-14047: ALTER TABLE|INDEX RENAME May Not Be Combined With Other Operations. ) is returned when trying to use the alter table command to move the table to a different schema, the result is the same.

Another option to move a table to a different user is to simply copy the table using

create table new_schema.table_name as select  * from  source_schema.table_name;

but this will generate a lot of I/O , redo logs and might take a lot of time so it can be a solution only for small tables.
Likewise, Using export and import will require downtime.

While there is no direct method to move a table to a different schema, you can use
One of the less known advantages of partitioning to do the task. the ability to exchange partitions between tables across schema can help us achieve the goal.
Following is the process to move a table from one schema to another:
1) Create a partitioned copy of the table – you do this by using

select dbms_metadata.get_ddl('TABLE','TABLE2MOVE') from dual; 

if you want just the plain DDL without all the storage definition etc. you can run

     begin
       dbms_metadata.set_transform_param dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
      end;  

remember to add a partition clause at the end of the ddl

 CREATE TABLE "NEW_SCHEMA"."TABLE2MOVE" 
   (	"ID" NUMBER, 
	"TEXT" VARCHAR2(100)
   ) 
   partition by hash (id) (partition p1);

2) Exchange the partition between the source and the target table

alter table NEW_SCHEMA.table2move exchange partition p1 with table SOURCE_SCHEMA.table2move;

3) That’s it! verify it by selecting from the source and target tables

         select * from NEW_SCHEMA.table2move;
         select * from  SOURCE_SCHEMA.table2move;
         

One thing to remember is that partitioning is an extra cost option on top of the enterprise edition license. so, if you get ORA-00439: feature not enabled: Partitioning. the option is not enabled. verify it with

select * from v$option;