{"id":283,"date":"2015-08-17T08:09:11","date_gmt":"2015-08-17T12:09:11","guid":{"rendered":"http:\/\/dbtricks.com\/?p=283"},"modified":"2015-08-17T08:10:10","modified_gmt":"2015-08-17T12:10:10","slug":"can-you-move-a-table-between-schemas","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=283","title":{"rendered":"Can you move a table between schemas ?"},"content":{"rendered":"<p>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.<\/p>\n<p>well,  the short answer is NO . there is no direct method to change the table owner.<\/p>\n<p>If you try something like <\/p>\n<pre lang=\"sql\">RENAME table_name to new_schema.table_name ;<\/pre>\n<p>Oracle Will return <strong>ORA-01765: specifying table&#8217;s owner name is not allowed<\/strong><br \/>\nWhile 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.<\/p>\n<p>Similarly, the ALTER TABLE command will not do the work as well. <\/p>\n<pre lang=\"sql\">ALTER TABLE source_schema.table_name RENAME TO new_schema.table_name;<\/pre>\n<p>While a different error message (<strong>ORA-14047: ALTER TABLE|INDEX RENAME May Not Be Combined With Other Operations.<\/strong>  )  is returned when trying to use the alter table command to move the table to a different schema, the result is the same.<\/p>\n<p>Another option to move a table to a different user is to simply copy the table using<\/p>\n<pre lang=\"sql\">create table new_schema.table_name as select  * from  source_schema.table_name;<\/pre>\n<p>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.<br \/>\nLikewise, Using export and import will require downtime.<\/p>\n<p>While there is no direct method to move a table to a different schema, you can use<br \/>\nOne of the less known advantages of partitioning  to do the task.  the ability to <strong>exchange partitions between tables across schema<\/strong> can help us achieve the goal.<br \/>\nFollowing is the process to move a table from one schema to another:<br \/>\n1)\t<strong>Create a partitioned  copy of the table <\/strong>\u2013 you do this by using <\/p>\n<pre lang=\"sql\">select dbms_metadata.get_ddl('TABLE','TABLE2MOVE') from dual; <\/pre>\n<p>         if you want just the plain DDL without all the storage definition etc. you can run<\/p>\n<pre lang=\"sql\">     begin\r\n       dbms_metadata.set_transform_param dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);\r\n      end;  <\/pre>\n<p> remember to add a partition clause at the end of the ddl <\/p>\n<pre lang=\"sql\"> CREATE TABLE \"NEW_SCHEMA\".\"TABLE2MOVE\" \r\n   (\t\"ID\" NUMBER, \r\n\t\"TEXT\" VARCHAR2(100)\r\n   ) \r\n   partition by hash (id) (partition p1);<\/pre>\n<p>2)\t<strong>Exchange the partition between the source and the target table<\/strong><\/p>\n<pre lang=\"sql\">alter table NEW_SCHEMA.table2move exchange partition p1 with table SOURCE_SCHEMA.table2move;<\/pre>\n<p>3)\t<strong>That\u2019s it! <\/strong> verify it by selecting from the source and target tables<\/p>\n<pre lang=\"sql\">\r\n         select * from NEW_SCHEMA.table2move;\r\n         select * from  SOURCE_SCHEMA.table2move;\r\n         <\/pre>\n<p>One thing to remember is that partitioning is an extra cost <strong>option on top of the enterprise edition license. <\/strong> so, if you get ORA-00439: feature not enabled: Partitioning. the option is not enabled. verify it with <\/p>\n<pre lang=\"sql\">\nselect * from v$option;<br \/>\n<\/PRE><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[88,87],"class_list":["post-283","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-ora-01765","tag-ora-14047"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/283","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=283"}],"version-history":[{"count":12,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/283\/revisions"}],"predecessor-version":[{"id":295,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/283\/revisions\/295"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}