OraFAQ Forum: SQL & PL/SQL » Oracle: How to move a table to another schema?

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle: How to move a table to another schema?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle: How to move a table to another schema? [message #298379]

Wed, 06 February 2008 00:01

someswar1
Messages: 53
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298384 is a reply to message #298379]

Wed, 06 February 2008 00:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

exp/imp

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298388 is a reply to message #298384]

Wed, 06 February 2008 00:28

someswar1
Messages: 53
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298397 is a reply to message #298388]

Wed, 06 February 2008 00:53

Michel Cadot
Messages: 68648
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

someswar1
Messages: 53
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298425 is a reply to message #298400]

Wed, 06 February 2008 01:47

Littlefoot
Messages: 21809
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>

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298431 is a reply to message #298379]

Wed, 06 February 2008 01:57

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

someswar1
Messages: 53
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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298460 is a reply to message #298457]

Wed, 06 February 2008 02:47

pablolee
Messages: 2882
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298461 is a reply to message #298460]

Wed, 06 February 2008 02:51

someswar1
Messages: 53
Registered: January 2008

Member

mY PROJECT mANAGER FROM MY OFFICE ASSIGNED THE PROBLEM

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298463 is a reply to message #298461]

Wed, 06 February 2008 02:54

pablolee
Messages: 2882
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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298466 is a reply to message #298379]

Wed, 06 February 2008 03:03

mshrkshl
Messages: 247
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,

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298491 is a reply to message #298466]

Wed, 06 February 2008 04:12

someswar1
Messages: 53
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298492 is a reply to message #298491]

Wed, 06 February 2008 04:13

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Please switch your caps lock off. Typing in capitals is considered shouting (and rude).

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298496 is a reply to message #298491]

Wed, 06 February 2008 04:19

Michel Cadot
Messages: 68648
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298498 is a reply to message #298379]

Wed, 06 February 2008 04:21

mshrkshl
Messages: 247
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,

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298500 is a reply to message #298498]

Wed, 06 February 2008 04:24

someswar1
Messages: 53
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

pablolee
Messages: 2882
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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298511 is a reply to message #298379]

Wed, 06 February 2008 04:42

mshrkshl
Messages: 247
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,

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298515 is a reply to message #298511]

Wed, 06 February 2008 04:47

someswar1
Messages: 53
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298517 is a reply to message #298515]

Wed, 06 February 2008 04:48

Michel Cadot
Messages: 68648
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298522 is a reply to message #298379]

Wed, 06 February 2008 04:54

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

use another username replacing scott.
also check your package procedures.

regards,

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298524 is a reply to message #298522]

Wed, 06 February 2008 04:56

someswar1
Messages: 53
Registered: January 2008

Member

I am checking it in another user in place of scott but except table all r copied in another user

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298527 is a reply to message #298379]

Wed, 06 February 2008 05:08

mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi

Senior Member

Please post your Oracle version (4 decimals).

regards,

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #298532 is a reply to message #298527]

Wed, 06 February 2008 05:27

someswar1
Messages: 53
Registered: January 2008

Member

10.2.0.1.0

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #503465 is a reply to message #298379]

Fri, 15 April 2011 23:03

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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #503475 is a reply to message #503465]

Sat, 16 April 2011 03:01

Littlefoot
Messages: 21809
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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #503624 is a reply to message #503465]

Mon, 18 April 2011 07:53

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514156 is a reply to message #503624]

Fri, 01 July 2011 22:56

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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514167 is a reply to message #514156]

Sat, 02 July 2011 00:57

Michel Cadot
Messages: 68648
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514173 is a reply to message #514167]

Sat, 02 July 2011 02:10

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 ?

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514198 is a reply to message #514173]

Sat, 02 July 2011 10:45

Michel Cadot
Messages: 68648
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

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514251 is a reply to message #514198]

Sat, 02 July 2011 23:44

NaeemAlsaadi
Messages: 20
Registered: November 2010
Location: Oman

Junior Member

haha i am always alone it will not make any different

Report message to a moderator

Re: Oracle: How to move a table to another schema? [message #514580 is a reply to message #514156]

Tue, 05 July 2011 08:12

joy_division
Messages: 4963
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.

Report message to a moderator

Previous Topic:	help required : adding a new partition to existing partition table
Next Topic:	working Days

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 23:34:18 CDT 2024