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 ; |
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; |
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; |
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; |
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; |
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); |
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; |
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; |
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
