+1 401 783 6172 (International callers)
gary@skillbuilders.com
Request Info
Register Login

Login/Register to Access all of Skillbuilders' Content

Connect with:

Login with Facebook Login with Google Login with LinkedIn

Login with your site account

Connect with:

Login with Facebook Login with Google Login with LinkedIn


Lost your password?

Not a member yet? Register now

SkillBuildersSkillBuilders
  • Services
    • Oracle APEX Hosting on AWS
    • Oracle Database Admin/Remote DBA
    • Oracle APEX App Development
    • APEX Administration
  • Products
    • Oracle Database Manager (free)
    • Deprecated APEX Plugins
  • Training
    • Course Calendar
    • Oracle DBA
    • Oracle Developer
    • Oracle APEX
    • Web Development
    • Free Webinars
    • Mentoring
    • Free Oracle Database Tutorials
      • Free Oracle Database Administration Tutorials
      • Free Oracle Developer Tutorials
      • Free Oracle APEX Tutorials
      • Free Solaris 11, Oracle VM and ZFS Tutorials
      • Free Oracle Database Retired Tutorials
      • Free Groovy and Grails Tutorials
  • Blog
  • About
    • About Us
    • Our Team
    • Events
    • Case Studies
      • Oracle Database Administration
      • Oracle APEX Development
      • Oracle APEX Administration
      • Oracle and AWS Cloud
    • Client Testimonials
    • Contact
    • Services
      • Oracle APEX Hosting on AWS
      • Oracle Database Admin/Remote DBA
      • Oracle APEX App Development
      • APEX Administration
    • Products
      • Oracle Database Manager (free)
      • Deprecated APEX Plugins
    • Training
      • Course Calendar
      • Oracle DBA
      • Oracle Developer
      • Oracle APEX
      • Web Development
      • Free Webinars
      • Mentoring
      • Free Oracle Database Tutorials
        • Free Oracle Database Administration Tutorials
        • Free Oracle Developer Tutorials
        • Free Oracle APEX Tutorials
        • Free Solaris 11, Oracle VM and ZFS Tutorials
        • Free Oracle Database Retired Tutorials
        • Free Groovy and Grails Tutorials
    • Blog
    • About
      • About Us
      • Our Team
      • Events
      • Case Studies
        • Oracle Database Administration
        • Oracle APEX Development
        • Oracle APEX Administration
        • Oracle and AWS Cloud
      • Client Testimonials
      • Contact

    Blog

    • Home
    • Blog
    • How to move a table from one schema to another

    How to move a table from one schema to another

    • Posted by John Watson
    • Categories Blog, RDBMS Server
    • Date December 24, 2019

    Many times I’ve seen the question on forums “How can I move a table from one schema to another?” and the answer is always that you can’t. You have to copy it. Or export/import it. Well, here’s a way. It assumes that you are on release 12.x and have the partitioning option.

    My schemas are jack and jill. Create the table and segment in jack:

    orclz> create table jack.t1(c1) as select 1 from dual;
    
    Table created.
    
    orclz>

    Convert it to a partitioned table, and see what you’ve got:

    orclz> alter table jack.t1 modify partition by hash (c1) partitions 1;
    
    Table altered.
    
    orclz> select segment_name,segment_type,partition_name,header_file,header_block from dba_Segments where owner='JACK';
    
    SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                 HEADER_FILE HEADER_BLOCK
    ------------------------------ ------------------ ------------------------------ ----------- ------------
    T1                             TABLE PARTITION    SYS_P5443                               12           66
    
    orclz>

    Create an empty table (by default, no segment) for jill:

    orclz> create table jill.t1 as select * from jack.t1 where 1=2;
    
    Table created.
    
    orclz>

    And now move the segment from jack to jill:

    orclz> alter table jack.t1 exchange partition sys_p5443 with table jill.t1;
    
    Table altered.
    
    orclz>

    and now (woo-hoo!) see what we have:

    orclz> select segment_name,segment_type,partition_name,header_file,header_block from dba_Segments where owner='JILL';
    
    SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                 HEADER_FILE HEADER_BLOCK
    ------------------------------ ------------------ ------------------------------ ----------- ------------
    T1                             TABLE                                                      12           66
    
    orclz>

    It isn’t only Father Christmas who can do impossible things 🙂

    —
    John Watson
    Oracle Certified Master DBA
    https://skillbuilders.com

    • Share:
    John Watson
    https://skillbuilders.com/our_team/john-watson/

    Previous post

    19c Standard Edition permits 3 PDBs per CDB
    December 24, 2019

    Next post

    Database 20c docs
    22 February, 2020

    Copyright © Skillbuilders.com

    Interested in joining our team?

    View details
    Managed APEX Hosting on AWS

    Sorry. This form is no longer accepting new submissions.