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.
- Before executing any exchanges
- After 1st exchange
- 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!
40 Comments. Leave new
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.
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.
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
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?!
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.
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.
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?
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
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”.
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.
Dan,
Does this move tables too ? Does it move them by re-assigning IDs, or it physically moves the data?
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
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
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?
How to import tables from one owner abc/xyz to another owner kee/nee with all constraints.
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.
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.
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
Marco,
You forgot “including indexes”. You must have a primary key. Look at my example move in the post.
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
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.
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 ?
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 […]
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.
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?
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;
Excellent Tip for partition exchange ! Thanks Christo .
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?
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.
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 […]
[…] 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 […]
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.
Quiet Handy. Thanks