Home » SQL & PL/SQL » SQL & PL/SQL » how to import/copy the tables of one user to another user.
how to import/copy the tables of one user to another user. [message #189740] Sat, 26 August 2006 03:02 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi pals

i have created two users using system/manager.

one is " senthil/kumar "
and another one is " raj/vinoth ".

i have created some tables at senthil/kumar. now i want to copy them to raj/vinoth.

how to do this.


i am using personal oracle for win95

thanks a lot in advance.


Re: how to import/copy the tables of one user to another user. [message #189749 is a reply to message #189740] Sat, 26 August 2006 07:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please Search before posting.
Start here
http://orafaq.com/faq/import_export

There are many many examples in the forum.
Please search.

[Updated on: Sat, 26 August 2006 07:18]

Report message to a moderator

Re: how to import/copy the tables of one user to another user. [message #189756 is a reply to message #189740] Sat, 26 August 2006 09:17 Go to previous messageGo to next message
Prince
Messages: 32
Registered: December 2001
Member
Simply login as raj/vinoth then use the following select statement:

CREATE TABLE TABLE_1 AS
SELECT * FROM SENTHIL.TABLE_1;


and run same query for all relevant tables or you could opt to use PL/SQL if you are familiar with it: Here is how you do that:
BEGIN
CREATE TABLE TABLE_1 AS
SELECT * FROM SENTHIL.TABLE_1;
CREATE TABLE TABLE_2 AS
SELECT * FROM SENTHIL.TABLE_2;
CREATE TABLE TABLE_3 AS
SELECT * FROM SENTHIL.TABLE_3;
CREATE TABLE TABLE_4 AS
SELECT * FROM SENTHIL.TABLE_4;
CREATE TABLE TABLE_5 AS
SELECT * FROM SENTHIL.TABLE_5;
-- AND SO ON
COMMIT;
END;

That should take care of that the easy way.
Goodluck!
Re: how to import/copy the tables of one user to another user. [message #189917 is a reply to message #189756] Mon, 28 August 2006 09:16 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
If you opt for this method, you will have to have GRANTed SELECT privilege on senthil's tables to either raj or PUBLIC.
Re: how to import/copy the tables of one user to another user. [message #190002 is a reply to message #189740] Tue, 29 August 2006 00:26 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi prince

it shows the follwoing error msg
SQL> create table naran as select * from senthil.naran;
create table naran as select * from senthil.naran
                                          *
ERROR at line 1:
ORA-00942: table or view does not exist


i have granted only the following rights to both users "senthil and raj"

1, create table
2, create session
3, resource

but i am able to import that table "naran" from user "senthil" to user "system/manager"

how to solve this problem.
Re: how to import/copy the tables of one user to another user. [message #190017 is a reply to message #189917] Tue, 29 August 2006 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Mon, 28 August 2006 16:16

If you opt for this method, you will have to have GRANTed SELECT privilege on senthil's tables to either raj or PUBLIC.


Ah well, after all it WAS all the way down the list of replies, so who would read it?
Re: how to import/copy the tables of one user to another user. [message #190254 is a reply to message #189740] Tue, 29 August 2006 21:48 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi pals

i don't know how to grant select privilege to users senthil and raj. but i tried as of my knowledge. but it show error.

SQL> connect system/manager;
Connected.

SQL> grant select privilege to senthil;
grant select privilege to senthil
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant select privilege to raj;
grant select privilege to senthil
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege



how to grant "select privileges" to users.

reply fast.
Re: how to import/copy the tables of one user to another user. [message #190283 is a reply to message #190254] Wed, 30 August 2006 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
youthsen wrote on Wed, 30 August 2006 04:48

reply fast.

fast.



If you really want an answer fast, maybe you should put some more effort in it.
I know that Google returns 525000 hits, but hey, you don't have to read them all!

Remember that we are all volunteers, anwering questions in our spare time. Adding things like 'urgent', 'reply fast' (even without the magic word!!) tend to give you LESS chance of an answer.
Re: how to import/copy the tables of one user to another user. [message #190413 is a reply to message #189740] Wed, 30 August 2006 10:10 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi frank

i am very sorry for this. here after i will not do like this.

hi pals. how to solve this problem.

thanks a lot in advance for your valuable answers.
Re: how to import/copy the tables of one user to another user. [message #190432 is a reply to message #190413] Wed, 30 August 2006 13:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Follow the link I provided (the Google-link). It contains plenty of sites explaining how to grant the privilege you want.
Re: how to import/copy the tables of one user to another user. [message #190454 is a reply to message #190432] Wed, 30 August 2006 18:25 Go to previous message
Prince
Messages: 32
Registered: December 2001
Member
Hello there ...

It is true that we are all at different levels in our careers and experience in oracle but I agree with Frank completely: As much as we desire to help each other, it is very encouraging when we find that individual make an effort also to research the problems they encounter. Try this simple code:

grant all on table_name to public;


It should give access to all the tables you created by that user to your other users. If you are running personal oracle on a local machine, it should not be a "security" concern.
Previous Topic: How to find the common values among groups of values
Next Topic: ora_sql_txt
Goto Forum:
  


Current Time: Thu Dec 08 18:34:57 CST 2016

Total time taken to generate the page: 0.07774 seconds