Home » SQL & PL/SQL » SQL & PL/SQL » how to copy tables from one user to another
how to copy tables from one user to another [message #263571] Thu, 30 August 2007 07:27 Go to next message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member
hi all!
i have created a user with name abc
now i want to have all emp,dept,salgrade,...etc tables which are exist in scott in my abc user.

can anyone tell me how to copy all database tables from scott to abc user.


thanks & regards
srilaxmi
Re: how to copy tables from one user to another [message #263573 is a reply to message #263571] Thu, 30 August 2007 07:41 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Just use CTAS (create table as select ...)
Re: how to copy tables from one user to another [message #263574 is a reply to message #263573] Thu, 30 August 2007 07:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Before that grant privileges.

By
Vamsi
Re: how to copy tables from one user to another [message #263576 is a reply to message #263574] Thu, 30 August 2007 07:54 Go to previous messageGo to next message
srilaxmi
Messages: 16
Registered: June 2007
Location: hyderabad
Junior Member
is there any way to copy all database tables from scott to another user at a time.
Re: how to copy tables from one user to another [message #263578 is a reply to message #263576] Thu, 30 August 2007 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
exp owner/imp fromuser/touser

Regards
Michel
Re: how to copy tables from one user to another [message #263669 is a reply to message #263571] Thu, 30 August 2007 12:53 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
CTAS do not copy index or other object.
Re: how to copy tables from one user to another [message #263670 is a reply to message #263669] Thu, 30 August 2007 12:54 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I was just going by the literal question of wanting the tables.
Re: how to copy tables from one user to another [message #263901 is a reply to message #263571] Fri, 31 August 2007 04:27 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi

For all the basic tables(emp,dept,..) you have demobld.sql file.
this file should run by either start/run/@ command along with
its path.
SQL> start d:\oracle\..\demobld.sql.
path differs from version to version. so please find it through
windows search.

Other tables need to be done manually by

SQL> create table <name> as
2) select * from scott.<table name>;
(OR)

take the help of exp, imp command

Regards
Re: how to copy tables from one user to another [message #264132 is a reply to message #263571] Sat, 01 September 2007 00:35 Go to previous messageGo to next message
lenin_babu55
Messages: 12
Registered: August 2007
Junior Member
hi

create table <tablename> as select * from <username.tablename>
eg:-
create table emp as select * from scott.emp
Re: how to copy tables from one user to another [message #264137 is a reply to message #264132] Sat, 01 September 2007 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell me what is in your post that is not the previous posts pointing to CTAS solution?

Regards
Michel
Re: how to copy tables from one user to another [message #264229 is a reply to message #264137] Sun, 02 September 2007 03:31 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
i guess, some people dont read other people's answer's and just the OP's question then answers it, without knowing that it might have already been answered. (sorry for my english)
Previous Topic: Last N rows of a table (renamed + merged)
Next Topic: Decode function with AND & OR condition
Goto Forum:
  


Current Time: Sat Dec 03 18:19:12 CST 2016

Total time taken to generate the page: 0.08963 seconds