Oracle: How to move a table to another schema?

Jul 21, 2006 / By Christo Kutrovsky

Tags: , ,

A client asked me, “How can I move a table to another schema in Oracle?” The quick answer I gave him is, “not possible”. You have to rebuild it via “create table as select”. You might ask, justifiably, why would you want to do that anyway? His problem was that the application has been split into 2 parts, and he wanted to have separate schemas for each part, to ensure that there is no cross-schema table access.

The way this should work is like this:

SQL> rename t1 to kutrovsky.t1;

ORA-01765: specifying table's owner name is not allowed

Oops! How could you do that without rebuilding the segments, I was wondering. And here’s what came up.

It’s not exactly rename t1 to kutrovsky.t1;, but it gets pretty close.

Let’s assume that t1 is the table we want to move. For demonstration purposes, allow me to create a simple table t1:

create table t1 ( column1 ) as select rownum from user_tables where rownum <=10;

The first step in our process is to create a range partitioned table, in the following example named t1_temp, based on the structure of our table. The name is of no importance, it is only temporary. We use any existing number, date or varchar2 column of our table for the partition key. The ranges also do not matter, since we’re not validating them.

create table t1_temp partition by range (column1)
(partition dummy values less than (-1),partition t1 values less than (MAXVALUE))
as select * from t1 where rownum <=0;

As the second step, we create the new table, which will hold the data. Note that we’re only creating the layout, no data.

create table kutrovsky.t1 as select * from t1 where rownum <=0;

And now here comes the magical third step:

alter table t1_temp exchange partition dummy with table t1 including indexes without validation;
alter table t1_temp exchange partition dummy with table kutrovsky.t1 including indexes without validation;

The first command “assigns” the data segment to the t1_temp table. The second command “assigns” the data segment to the t1 table in the new owner.

Magic? I don’t think so. Here’s how and why it works.

When you create a normal table, Oracle creates two items. The logical object (object_id) and the data segment (data_object_id). When you create a partitioned table, Oracle creates 1 logical object (the table) and multiple data segments (each partition). In a partitioned table, the logical object (the table) has no physical segment, only it’s partitions have them.

Oracle has a command to re-assign physical objects (data segments) to compatible logical objects, but it only works between a partition of a table and a non-partitioned table. When you use that command in the two step process as shown above, you essentially re-assign between two tables, by using a partitioned table to work around the limitation of the exchange command.

I took three snapshots of our objects of interest.

  1. Before executing any exchanges
  2. After 1st exchange
  3. After 2nd exchange

Notice how the data_object_id travels “down” and gets re-assigned.

OWNER OBJECT_NAME SUBOBJ OBJECT_ID DATA_OBJECT_ID
Before any exchange operations
BIG_SCHEMA T1 673309 673309 <–
BIG_SCHEMA T1_TEMP T1 673312 673312
KUTROVSKY T1 673313 673313
alter table t1_temp exchange partition dummy with table t1 including indexes without validation;
BIG_SCHEMA T1 673309 673312
BIG_SCHEMA T1_TEMP T1 673312 673309 <–
KUTROVSKY T1 673313 673313
alter table t1_temp exchange partition dummy with table kutrovsky.t1 including indexes without validation;
BIG_SCHEMA T1 673309 673312
BIG_SCHEMA T1_TEMP T1 673312 673313
KUTROVSKY T1 673313 673309 <–

This approach also works when the table has indexes. The little detail is that you have to create the same indexes on both the temp partitioned table (as local indexes!) and the final destination table.

Those of you who have been following along attentively will know that you can now drop the t1_temp table.

Happy moving!

39 Responses to “Oracle: How to move a table to another schema?”

  • Babette says:

    Very interesting concept and approach Christo. One question, it is not obvious. Why did you have to do the “alter table exchange partition” twice in a row.

  • Christo Kutrovsky says:

    The exchange command only works with 1 non-partitioned object and 1 partitioned object.
    The first time you take the data segment and put it in a partitioned table.
    The second time you take it from the partitioned table and put it again in a normal table, but in a different schema.

    If you directly put it into a partitioned table in a new schema, then you will end up with a partitioned table.

  • Pete_s says:

    Another use of this technique is to convert a materialized view into a conventional table – droping a mview would normally drop its content unless you used a pre-built table in the view definition. – It’s a little messy though for a partitioned mview to a partitioned table

  • Hey Christo! Very nice creative aproach to implement cross-schema rename! I can only add that “without validation” option can actually take more time. Jonathan wrote very nice one here.
    Thanks, Alex

  • Howard J. Rogers says:

    Sorry… did I miss the bit where you discussed export/import’s “FROM_USER” and “TO_USER” capabilities (going back to about version 6), or Export Data Pump’s “REMAP_SCHEMA” capability, which in 10g will do precisely what you are asking, and transform the user of the tables being exported/imported?

    I’m all for innovative ways to achieve something, but let’s not forget the basics first, eh?!

  • Christo Kutrovsky says:

    Howard,

    Using this method, you can avoid rebuilding the data (and index) segments. Thus “moving” a 50 gb table will take 3 seconds, as opposed to 3 hours.

    There is no data moved, there is no redo generated, there is nothing. A simple, very fast operation.

  • Christo Kutrovsky says:

    Alex,

    Thanks for the link. I must use without validation, otherwise my rename becomes a full table scan. I guess I did a little bit like Oracle. Show a simple test case, but when you try to use it for real, you may hit a few problems. I think it’s still better then exporting/importing, with datapump or not.

  • Christo Kutrovsky says:

    Pete,

    I wonder when you would need to convert a mview to a normal table (which was not initially build as a pre-build table). I am curious what’s your case? Something to do with saving history?

  • Pete_s says:

    Hi Christo – in our case we have some nested mviews and for some there is a choice of two possible parents, and we took the wrong first choice. So what we want do is to change the mview query text but to do this we need to drop and create the mview but for nested views (three tiers deep) we have drop all of the children before we can alter a parent. Which for us is a major piece of down time

  • Howard J. Rogers says:

    Christo, I am aware of what partition exchanging does and does not do. That wasn’t my point. Your second sentence in this piece reads “The quick answer I gave him is, “not possible”. You have to rebuild it via “create table as select”…. and my point is, that statement is not correct and you’ve missed out the basic answers which would be more correct (“You can rebuild it via CTAS, you can export/import it, you can data pump it…”)

    If **your** point was “How to rename a table without incurring the usual costs associated with doing so via conventional means”, then you should perhaps have made that clearer -and you should perhaps also subtitle your piece “And don’t bother trying this technique unless you have the Enterprise Edition with the extra Partitioning Option on top”.

    Or, as you might have said, “A simple, very fast operation… that costs a lot of cash”.

  • Dan Morgan says:

    The suggestions, above, are all good for one table. But if you want to move a schema try this:

    SQL> conn / as sysdba

    SQL> CREATE USER xyz
    2 IDENTIFIED BY xyz
    3 DEFAULT TABLESPACE users
    4 TEMPORARY TABLESPACE temp
    5 QUOTA UNLIMITED ON users;

    User created.

    SQL> grant create session, resource TO xyz;

    Grant succeeded.

    SQL> BEGIN
    2 dbms_schema_copy.clone(‘UWCLASS’, ‘XYZ’);
    3 dbms_schema_copy.clean_up(‘UWCLASS’, ‘XYZ’);
    4 END;
    5 /

    PL/SQL procedure successfully completed.

    SQL> conn xyz/xyz
    Connected.
    SQL> col object_name format a30
    SQL> SELECT object_name, object_type
    2 FROM user_objects;

    OBJECT_NAME OBJECT_TYPE
    —————————— ——————-
    SOLVE PROCEDURE
    GET_BLOCK_STRING FUNCTION
    GET_COL_STRING FUNCTION
    GET_ROW_STRING FUNCTION
    SUDOKU_SOLVE FUNCTION
    FEDS_PKG PACKAGE
    FEDS_PKG PACKAGE BODY
    WHOAMI FUNCTION
    CHILD PROCEDURE
    PARENT_P PROCEDURE
    PARENT_F FUNCTION

    11 rows selected.

    SQL>

    More on the DBMS_SCHEMA_COPY built-in can be found in Morgan’s
    Library at http://www.psoug.org.

    Including how to clean up a failed cloning.

  • Christo Kutrovsky says:

    Dan,

    Does this move tables too ? Does it move them by re-assigning IDs, or it physically moves the data?

  • Dan Morgan says:

    It moves everything. Tables, views, procedures.

    The package contains a number of procedures …
    CLEAN_FAILED_CLONE
    CLEAN_TARGET
    CLEAN_UP
    CLONE (the one that copies a schema as above)
    CLONE_RECOVERY
    SWAP (swaps objects between schemas)
    SYNC_CODE
    VALIDATION_CHECK

    Demos of most of the functionality are in my library.

  • Dan,
    I am not familiar with internals of DBMS_SCHEMA_COPY. How does it move the objects with data segments (namely tables and indexes)?
    If it does similar trick to what Christo posted – cool. Otherwise, it’s nice but comes as offline and long reorganization process.
    Regards,
    Alex

  • joytao says:

    Dan,
    when run this procedures,display infomatin folllow,can you help ?
    BEGIN
    dbms_schema_copy.clone(‘SAPDEV’, ‘SAPTST’,2,FALSE);
    dbms_schema_copy.clean_up(‘SAPDEV’, ‘SAPTST’);
    END;
    /

    ERROR at line 1:
    ORA-00001: unique constraint (SYS.I_OBJ2) violated
    ORA-06512: at “SYS.DBMS_UPGRADE_INTERNAL”, line 2481
    ORA-06512: at “SYS.DBMS_UPGRADE_INTERNAL”, line 3909
    ORA-06512: at “SYS.DBMS_UPGRADE_INTERNAL”, line 4150
    ORA-06512: at “SYS.DBMS_UPGRADE_INTERNAL”, line 2168
    ORA-06512: at “SYS.DBMS_UPGRADE_INTERNAL”, line 2236
    ORA-06512: at “SYS.DBMS_SCHEMA_COPY”, line 628
    ORA-06512: at “SYS.DBMS_SCHEMA_COPY”, line 743
    ORA-06512: at line 2

  • fielding says:

    joytao,
    SYS.I_OBJ2 is the index on object owner/name, so it looks like the clone is erroring out due to a duplicate destination object name. Might there have already been objects in SAPTST prior to the clone?

  • Tahir says:

    How to import tables from one owner abc/xyz to another owner kee/nee with all constraints.

  • Misha says:

    Hi,

    I have tested another method :

    update obj$ set OWNER#=x where name=’y';
    commit;
    alter system flush shared_pool;

    this works !

  • Misha, you should probably be aware that it’s a big no-no to hack the data dictionary with your bare hands?

    Never ever do like that, unless you are planing to use that database for practicing your restore and recovery skills.

  • Misha says:

    Yes, this method is unsupported. But a bit alike thing is about outlines : exchanging them is done by directly updating data dictionary tables that is not documented in official Oracle documentation, but has a metalink note.

  • Marco says:

    Hi, is great to found what a look specifically, but when run this, sqlplus give the next error message:

    SQL> alter table prodinsumo_temp exchange partition dummy with table dbasit.prodinsumo without validation;
    alter table prodinsumo_temp exchange partition dummy with table dbasit.prodinsumo without validation
    *
    ERROR at line 1:
    ORA-28653: tables must both be index-organized

  • Christo Kutrovsky says:

    Marco,

    You forgot “including indexes”. You must have a primary key. Look at my example move in the post.

  • Matt Tordoff says:

    Hi Christo,

    I followed your article with some interest, thankyou for providing this ingenious approach. I have attempted to mimic the situation shown here, but with a slightly more complicated example. I am trying to move a table from one schema to another, and at the stage where i issue the following command:

    > alter table t1_temp exchange partition dummy with table t1 including indexes without validation;

    I receive the following error from Oracle:

    > ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

    The table I am trying to move has both primary key and foreign key constraints. I have created these constraints for the temporary table t1_temp, however, am still getting the above complaint. Is the only solution to drop all constraints, move the table, and then recreate the constraints once moved?

    Any help would be greatly appreciated.

    Matt

  • Christo Kutrovsky says:

    Hi Matt,

    Based on your error I think the indexes are not the same. Probably not on the same columns, not the same compression. Check that you have the exact same indexes on both tables.

  • Shervin says:

    Just read that blog and the following came to my mind :

    – Have you ever tried rdbms redef for this move ?
    – What if online users run transaction on the source table at time of move ?

  • Ajay Kapur says:

    thanks spot on ..please write more …AJ

  • […] 1) Yes, can do it in essentially the same way as before (h/t Pythian Blog): […]

  • […] à moitié Bulgare, qui joue peut être encore avec mon drum set et qui s’intitule “How to move a table to another schema?“. Dans cet article, Christo va un pas plus loin et utilise cette technique pour déplacer une […]

  • Sathish says:

    Its very useful Christo, am having one question, can u please explain how can i move a table to another schema, if that particular table has more than one columns. Below is the table
    Name Null? Type
    ———– ——– ————-

    ROLLNO NOT NULL NUMBER(8)
    PAYDATE NOT NULL DATE
    AMOUNT NOT NULL NUMBER(8,2)
    CHEQUENO VARCHAR2(10)
    BANKNAME VARCHAR2(30)
    REMARKS VARCHAR2(100)

    Thanks in advance.

  • kasper says:

    A more general approach would be to create 1 hash partition on any attribute: – as for Satish:

    CREATE TABLE S_TEMP (
    ROLLNO NOT NULL NUMBER(8)
    PAYDATE NOT NULL DATE
    AMOUNT NOT NULL NUMBER(8,2)
    CHEQUENO VARCHAR2(10)
    BANKNAME VARCHAR2(30)
    REMARKS VARCHAR2(100)
    ) PARTITION BY HASH(ROLLNO);

    unfortunately we need 1 extra step – to get the partition name:

    SELECT PARTITION_NAME
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME=’S_TEMP';

    then call ALTER TABLE EXCHANGE PARTITION…

    There is also an another interesting fact about partition exchange:
    Assuming that the tables reside on same tablespace the partition exchange will occur even when the tablespace is in READ ONLY mode (tested on 10gR2 v10.2.0.3)! Well, this feature should be reported as a bug, what is your opinion on this?

  • kasper says:

    oops, forgot the PARTITIONS cluase:

    CREATE TABLE S_TEMP (
    ROLLNO NOT NULL NUMBER(8)
    PAYDATE NOT NULL DATE
    AMOUNT NOT NULL NUMBER(8,2)
    CHEQUENO VARCHAR2(10)
    BANKNAME VARCHAR2(30)
    REMARKS VARCHAR2(100)
    ) PARTITION BY HASH(ROLLNO) PARTITIONS 1;

  • Souradeep says:

    Excellent Tip for partition exchange ! Thanks Christo .

  • Tito says:

    could anybody tell me how would convert tables,views,triggers from one database name to another in oracle 10g with different schema name and previliges.

    1. one dbname prod, another mid
    2. one schema/user name prodprod, another midmid.

    I want to transfer everything from mid to prod.

    Please can anyone help me with this?

  • mandm says:

    Can this method be used for creating a hash partiioned table and moving data from a regular table to it?

  • mandm, the idea of hash partitioning is to split data into somewhat random, but balanced buckets.

    You can’t use a simple switch segments to re-distribute/defrag data.

  • Arun.D says:

    Very useful article. It is bit frustrating as there is not direct method to copy/rename/move table from one schema to another. Thanks.

  • […] why can’t this happen on a CREATE TABLE AS SELECT command? Most if not all of the statistics gathering steps (high/low value, number of distinct […]

  • Oracle: How to move a table to a different schema? Drija says:

    […] to do what you want to do – you’re going to have to move data. I did come across this interesting method. November 11, 2009 4:24 am Justin Cave I would be shocked if there was a faster solution […]

  • sherlin says:

    Hi Christo,
    I tried your appraoch and successfully executed first step, but when i try to do the second step it throws the following error. (Im new to Oracle)

    ERROR at line 1:
    ORA-01950: no privileges on tablespace ‘USERS’

    Also i wud apprieciate if you could tel me how to use “Set unused()” on sys schema coz it dsnt allow on sys schema.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>