Home » SQL & PL/SQL » SQL & PL/SQL » Oracle: How to move a table to another schema?
Oracle: How to move a table to another schema? [message #298379] Wed, 06 February 2008 00:01 Go to next message
someswar1
Messages: 50
Registered: January 2008
Member
A client asked me, “How can I move a table to another schema in Oracle?” can any one tell me how it is possible.

Regards:
someswar Bhattacharjee
someswar11@gmail.com
Re: Oracle: How to move a table to another schema? [message #298384 is a reply to message #298379] Wed, 06 February 2008 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 23155
Registered: January 2009
Senior Member
exp/imp
Re: Oracle: How to move a table to another schema? [message #298388 is a reply to message #298384] Wed, 06 February 2008 00:28 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
without exp/imp.The client says without exp/imp.As per searching the oracle documentation i found one package dbms_schema_copy.clone to copy entire schema to another user. But i am not able to implemented. can u guide me what is the procedure or steps to using this package.

regards:
someswar
Re: Oracle: How to move a table to another schema? [message #298397 is a reply to message #298388] Wed, 06 February 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i am not able to implemented

What does this mean?
Do you have an error?
Don't you know how to call it?
...?

There is another way: create table ... as select ... from otherschema.table

Regards
Michel

[Updated on: Wed, 06 February 2008 00:53]

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298400 is a reply to message #298397] Wed, 06 February 2008 00:58 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
I have to user in same database(someswar). One is scott and another is some. This is the code I want to copy entire scott schema or particular table of scott user to some user.

BEGIN
dbms_schema_copy.clone('scott', 'some', 2, FALSE);
dbms_schema_copy.clean_up('scott', 'some');
END;


When i execute the code as sys user the code be executede but no schema will be copied in some user. How i copy . Please Help;

Regards:
Someswar
Re: Oracle: How to move a table to another schema? [message #298425 is a reply to message #298400] Wed, 06 February 2008 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What about CTAS Michel suggested?
SQL> connect scott/tiger@ora10
Connected.
SQL> grant select on test to mike;

Grant succeeded.

SQL> connect mike/lion@ora10
Connected.
SQL> create table test as select * from scott.test;

Table created.

SQL>
Re: Oracle: How to move a table to another schema? [message #298431 is a reply to message #298379] Wed, 06 February 2008 01:57 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NOTE : In case of CTAS Method only NotNull Constraints will be re-created . But it doesn't matter if you are interested only in Data.

Thumbs Up
Rajuvan.

[Updated on: Wed, 06 February 2008 01:58]

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298457 is a reply to message #298425] Wed, 06 February 2008 02:41 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
No i Dont want any command line reference(imp/exp) or grant. I want to know only is there is any built in package in oracle 10g to copy entire schema from one user to another user.
Re: Oracle: How to move a table to another schema? [message #298460 is a reply to message #298457] Wed, 06 February 2008 02:47 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
So what you are saying is that you have a customer, who has asked that you copy a table from one schema to another. This customer has told you how it is NOT to be done i.e. they have said "You are not going to be allowed to use methods x, y or z".
Have they given you a reason for this? Or is this one of these ridiculous interview questions? Or a homework assignment maybe? Or you run your own 'forum' someone has asked you the question, with the above mentioned restrictions and you have come here to try to get an answer for him/her? (All other suggestions welcome Smile
Re: Oracle: How to move a table to another schema? [message #298461 is a reply to message #298460] Wed, 06 February 2008 02:51 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
mY PROJECT mANAGER FROM MY OFFICE ASSIGNED THE PROBLEM
Re: Oracle: How to move a table to another schema? [message #298463 is a reply to message #298461] Wed, 06 February 2008 02:54 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
I'm going to assume that you didn't mean to shout there.
If he has supplied this as a test to check your knowledge, I'd suggest some use of the dbms_metadata package (we'll let you look up the syntax and uses of it from the documentation) If it is an actual client request, I'd be asking why they don't want to use exp/imp (or any of the other potential implementations.
Re: Oracle: How to move a table to another schema? [message #298466 is a reply to message #298379] Wed, 06 February 2008 03:03 Go to previous messageGo to next message
mshrkshl
Messages: 242
Registered: September 2006
Location: New Delhi
Senior Member
yes.try it.

conn / as sysdba

 CREATE USER some
 IDENTIFIED BY some
 DEFAULT TABLESPACE tablespace_name
 TEMPORARY TABLESPACE temp
 QUOTA UNLIMITED ON tablespace_name;



grant create session, resource TO some;



BEGIN
 dbms_schema_copy.clone(’scott’, ‘some’);
 dbms_schema_copy.clean_up(’scott’, ‘some’);
END;





regards,
Re: Oracle: How to move a table to another schema? [message #298491 is a reply to message #298466] Wed, 06 February 2008 04:12 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
THIS PACKAGE CLONE ONLY PACKAGE,FUNCTION.. ETC. NOT BACKUP ANY TABLE . hOW I BACKUP ANY TABLE USING ANY BUILT IN PACKAGE
Re: Oracle: How to move a table to another schema? [message #298492 is a reply to message #298491] Wed, 06 February 2008 04:13 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
Please switch your caps lock off. Typing in capitals is considered shouting (and rude).
Re: Oracle: How to move a table to another schema? [message #298496 is a reply to message #298491] Wed, 06 February 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And if there is no built-in package, how can we answer your question?

Regards
Michel
Re: Oracle: How to move a table to another schema? [message #298498 is a reply to message #298379] Wed, 06 February 2008 04:21 Go to previous messageGo to next message
mshrkshl
Messages: 242
Registered: September 2006
Location: New Delhi
Senior Member
your first question was


Quote:
“How can I move a table to another schema in Oracle?”


now your question is

Quote:
hOW I BACKUP ANY TABLE USING ANY BUILT IN PACKAGE


please stick to your first question.
move and backup are not similar.

regards,
Re: Oracle: How to move a table to another schema? [message #298500 is a reply to message #298498] Wed, 06 February 2008 04:24 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
select lower('MY FIRST QUESTION IS How can I move a table to another schema in Oracle?” AND SECOND QUESTION IS SIMILAR. MY GOAL IS HOW I MOVE THE TABLE IN ANOTHER SCHEMA. DSMS_SCHEMA_COPY.CLONE PACKAGE ONLY CLONE PROCEDURES,FUNCTIONS,ETC NOT TABLE.') from dual;

[mod-edit] lazy man way to remove CAPS.

[Updated on: Wed, 06 February 2008 08:08] by Moderator

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298502 is a reply to message #298500] Wed, 06 February 2008 04:27 Go to previous messageGo to next message
pablolee
Messages: 2658
Registered: May 2007
Location: Scotland
Senior Member
STOP SHOUTING. IT IS RUDE. Switch off your caps lock key.
You ask a second question. Was the first question answered for you? Did you think to thank mshrkshl (I'm assuming that you used his demo to help you) Your second question may be similar, but it is a [different question and therefore should be a new thread.
Re: Oracle: How to move a table to another schema? [message #298511 is a reply to message #298379] Wed, 06 February 2008 04:42 Go to previous messageGo to next message
mshrkshl
Messages: 242
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
DSMS_SCHEMA_COPY.CLONE PACKAGE ONLY CLONE PROCEDURES,FUNCTIONS,ETC NOT TABLE.


it is not true.it moves tables also.

regards,
Re: Oracle: How to move a table to another schema? [message #298515 is a reply to message #298511] Wed, 06 February 2008 04:47 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
But i am using this package follows

CREATE USER some
IDENTIFIED BY some
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON tablespace_name;



grant create session, resource TO some;



BEGIN
dbms_schema_copy.clone(’scott’, ‘some’);
dbms_schema_copy.clean_up(’scott’, ‘some’);
END;


but using this procedure the scott schema table is not moved to some user. How i do it
Re: Oracle: How to move a table to another schema? [message #298517 is a reply to message #298515] Wed, 06 February 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Oracle: How to move a table to another schema? [message #298522 is a reply to message #298379] Wed, 06 February 2008 04:54 Go to previous messageGo to next message
mshrkshl
Messages: 242
Registered: September 2006
Location: New Delhi
Senior Member
use another username replacing scott.
also check your package procedures.

regards,
Re: Oracle: How to move a table to another schema? [message #298524 is a reply to message #298522] Wed, 06 February 2008 04:56 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
I am checking it in another user in place of scott but except table all r copied in another user
Re: Oracle: How to move a table to another schema? [message #298527 is a reply to message #298379] Wed, 06 February 2008 05:08 Go to previous messageGo to next message
mshrkshl
Messages: 242
Registered: September 2006
Location: New Delhi
Senior Member
Please post your Oracle version (4 decimals).

regards,


Re: Oracle: How to move a table to another schema? [message #298532 is a reply to message #298527] Wed, 06 February 2008 05:27 Go to previous messageGo to next message
someswar1
Messages: 50
Registered: January 2008
Member
10.2.0.1.0
Re: Oracle: How to move a table to another schema? [message #503465 is a reply to message #298379] Fri, 15 April 2011 23:03 Go to previous messageGo to next message
junsaito
Messages: 1
Registered: April 2011
Location: Malaysia
Junior Member

if i do understood what you are trying to said, the simplest way to transfer one table from one database to another is this, just extract the DDL for the table, then copy the coding and then open your target database and user, just paste the coding and run it then commit. sorry if this is not what you are looking for.
Re: Oracle: How to move a table to another schema? [message #503475 is a reply to message #503465] Sat, 16 April 2011 03:01 Go to previous messageGo to next message
Littlefoot
Messages: 19890
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If all you copy-pasted was CREATE TABLE statement, there's no need to explicitly commit (as DDL does it anyway). If you also meant to create INSERT INTO statements for all records in the original table, note that there might be millions of records in a table which makes your approach rather infeasible. Though, you were right about committing - INSERT would require it.

Anyway: this 3 years old topic is most probably long time dead, so ... perhaps we should pay attention to more recent ones.
Re: Oracle: How to move a table to another schema? [message #503624 is a reply to message #503465] Mon, 18 April 2011 07:53 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
junsaito wrote on Sat, 16 April 2011 00:03
if i do understood what you are trying to said, the simplest way to transfer one table from one database to another is this


And the question was to transfer a table from one SCHEMA to another, not one DATABASE to another.
Re: Oracle: How to move a table to another schema? [message #514156 is a reply to message #503624] Fri, 01 July 2011 22:56 Go to previous messageGo to next message
NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman
Junior Member
As my understood that you want to copy table to another database try this

/*The following command copies the entire X table to a table named XX in another database. Note that the tables are located in two different databases. If XX already exists, SQL*Plus replaces the table and its contents. The columns in XX have the same names as the columns in the source table, X.*/

* you should have sys privileges and write this script in User1

COPY FROM User1/PasswordUser1@SchemaDB1 TO User2/PasswordUser2@SchemaDB2
REPLACE XX
USING SELECT * FROM X;


good luck
Re: Oracle: How to move a table to another schema? [message #514167 is a reply to message #514156] Sat, 02 July 2011 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note Oracle has said during the last 10 years that SQL*Plus COPY command is obsolete.

Regards
Michel
Re: Oracle: How to move a table to another schema? [message #514173 is a reply to message #514167] Sat, 02 July 2011 02:10 Go to previous messageGo to next message
NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman
Junior Member

i try this command in Oracle 9i it working fine
i think in Oracle 10 g and above they stopped this command and apply new one
can any one tell ?
Re: Oracle: How to move a table to another schema? [message #514198 is a reply to message #514173] Sat, 02 July 2011 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 60008
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't say it does not work I said that since 8i Oracle tells that teh COPY command is obsolete that just means that if you have any problem with it you are alone.

Regards
Michel
Re: Oracle: How to move a table to another schema? [message #514251 is a reply to message #514198] Sat, 02 July 2011 23:44 Go to previous messageGo to next message
NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman
Junior Member
haha i am always alone it will not make any different
Re: Oracle: How to move a table to another schema? [message #514580 is a reply to message #514156] Tue, 05 July 2011 08:12 Go to previous message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
NaeemAlsaadi wrote on Fri, 01 July 2011 23:56
As my understood that you want to copy table to another database try this


another person who does not know how to read or comprehend what is written. Where does it say from one database to another. As has been pointed out at least once before, one SCHEMA to another.
Previous Topic: help required : adding a new partition to existing partition table
Next Topic: working Days
Goto Forum:
  


Current Time: Sat Dec 20 19:57:46 CST 2014

Total time taken to generate the page: 0.11228 seconds