Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

export/import user from cleint on server

Posted on 2014-08-13
54
Medium Priority
?
335 Views
Last Modified: 2014-09-30
hi
as a cleint , i want to export one user from one database on server , then import it to another user on another database on another server
i'm connection to the both servers
could i do it through toad ? or there is another way ?
0
Comment
Question by:NiceMan331
  • 22
  • 14
  • 7
  • +3
53 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 400 total points
ID: 40257948
I believe that TOAD is capable of doing it, but it generates insert statements.

I would look into installing exp/imp or expdp/impdp on the client machine.  It is not part of the default client install (at least it wasn't the last time I did one).  If you do an administrator install it will get it, or you should be able to pick it out from a custom install.  The documentation for those utilities can be found here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm
0
 
LVL 5

Assisted Solution

by:Sanjeev Labh
Sanjeev Labh earned 400 total points
ID: 40263016
This is very easy to perform using sql developer which is a free tool from Oracle. You can do it as creating dump by export and importing the dump as well as connecting both database and directly performing the export/import.

You will find export/import in tools menu of the sql developer.

You can do this in toad also. From Database menu using Export sub-menu. All screenshot is as attached.Toad export screentoad-exp2.JPG
sql-dev-exp.JPG
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40263030
However, you need to have export import previliges or you will be allowed to do that for your own schema only.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 35

Expert Comment

by:johnsone
ID: 40263033
As per the documentation here -> http://docs.oracle.com/cd/E35137_01/appdev.32/e35117/intro.htm#sthref131

SQL Developer does not do an export.  It generates flat files in one of many formats that need to be loaded by another tool.  Not sure how well it will handle BLOBs if you have them.

I believe that TOAD export works the same way.

I would prefer to work with the expdp/impdp or exp/imp tools.  They will handle data transfers between Oracle databases better.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40263068
Yes Toad generates a script (or multiple scripts)

Database -> Export -> Generate Schema Script

In addition to the user and any of that user's objects, the script can also include ddl to create tablespaces, roles and profile for the user.

In order to execute the script you'll need administrative privileges on the target database.  This is true of any of the methods or tools.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40263074
If you see the screenshot toad-exp2.jpg it shows as data pump. You have the provision to do Data pump as well as the normal export utility.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40263100
Attached is the screenshot of sql developer help which clearly states that you can do data pump.
data-pump-sql-dev.JPG
0
 

Author Comment

by:NiceMan331
ID: 40263524
Hi all.                                                                I performed successful export through toad , it generate dmp and log files ,   But when I tried to do import it fail , I don't know why
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40267987
Probably will have to check the log files for more details of the error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40267993
Datapump creates a DMP file on the database server.  Did you manually move the file from server1 to server2 before trying to import?
0
 

Author Comment

by:NiceMan331
ID: 40268200
No slightw , I didn't , I'm still doing all from my machine
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40268208
Again, datapump creates a file on the database server.  It does not, and cannot, create a file on the client machine.

As johnsone posted in the very first post:  Classic exp/imp will create a file on the remote client machine.

I would go that route and forget all about GUIs.  Some times the command line is the way to go.
0
 

Author Comment

by:NiceMan331
ID: 40268329
Actually toad creates dmp file on my machine , but if you said to import it should transfer the file manually to the other server , in this case , I understood , but also through command cannot import it , I will quit
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40268339
I'm not a Toad user but from what was posted above, the only 'export' it can create is by using datapump.

From what others posted, the script that is local is just that, a script, not an export DMP file.

If the file is local, there should be no need to transfer it to the server.

then it goes back to you letting us know what errors you are getting.

Not being a Toad user, I'll let others comment.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 40268892
>>> , the only 'export' it can create is by using datapump.

No, Toad can do several types of exports

exp  - this might work as long as you're not using 11g features that aren't supported by exp, you also have to make sure you use the right oracle client versions if your source and target differ

expdp - this should work but only if you have server access.

schema - this should work anywhere because it's just a bunch of sql statements, but will likely be the slowest since you can't take advantage of any direct unloads or loads, or bulk processing.

and some others that don't apply here (table flat file, apex, subsets, etc)
0
 

Author Comment

by:NiceMan331
ID: 40269495
ok , now as a result , expport done by toad from my machine and the file is on my local drive
what is the final design for imp , it could be possible in any way or not without moving the file to the server ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40282484
depends on the type of export you did.

did you do datapump?  If so, then no, you'll have to move it to the server

if you did old "classic" export then you should be able to use Toad's import wizard.   It will be dependent on versions though as noted previously.

if you did a schema export, you should be able to connect to the new database and run the script from within the editor window


if you used one of the other types then there will be additional steps
0
 

Author Comment

by:NiceMan331
ID: 40282567
i used : export utility wizard , it creates the dmp on my machine
then when i tried to use : import utility wizard , it start import but immediately finished without any import
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40283060
what's in your log file?
0
 
LVL 23

Assisted Solution

by:David
David earned 400 total points
ID: 40283301
You can cross (identical) platforms with a db_link and appropriate privileges, see http://docs.oracle.com/cd/E11882_01/server.112/e16536/dp_import.htm#SUTIL919.

Be aware of any NLS differences such as language type and time zone.  This is in the Oracle documentation.  

It's my experience that working this out for the first time should always be done at the command level.  Any GUI adds complexity.

FWIW the syntax convention is that a schema is a user account which owns one or more objects.

Check out toadworld for possible examples and more focused advice.
0
 

Author Comment

by:NiceMan331
ID: 40295183
what's in your log file?
here is the text file for exporting the user
exp-h2002-rud.log
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40295931
I believe what sdstuber was wanting was the log file for the import so we could see why it was failing.
0
 

Author Comment

by:NiceMan331
ID: 40296160
THE LOG file fr import is empty
0
 
LVL 23

Expert Comment

by:David
ID: 40296660
Any chance you are looking in the wrong place, wrong file?  Or that the particular job execution is still running?  Even if there were errors in the first line, something gets logged.
0
 

Author Comment

by:NiceMan331
ID: 40296951
So what dvz ? I didn't get your idea
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40297367
If the import fails, there has to be some error messages somewhere.  Imports almost always provides a log.
0
 

Author Comment

by:NiceMan331
ID: 40297735
ok
let me repeat the process again and send you the error
0
 
LVL 23

Expert Comment

by:David
ID: 40298479
Log files are either in the default directory (from which  the datapump job was begun), if not specified in the command line.   If the command includes a parameter file, then the logfile path and name may be in the parameter file.  HTH
0
 

Author Comment

by:NiceMan331
ID: 40324675
i tried the process again
the import not performed
and here is the snapshot of the result

note : log file still empty
Toad.bmp
Toad-log.bmp
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40324800
There is no log file attached. The second one seems to be the parameter file which the import process uses. The log will have the details of import and errors in case of failure.
0
 

Author Comment

by:NiceMan331
ID: 40324812
the log file is empty as i mentioned above
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40325249
The TOUSER looks odd to me.  Never seen a username with a '.' in it.  I know one can be created by using double quotes but if you did that, I would strongly suggest you rethink that approach.
0
 

Author Comment

by:NiceMan331
ID: 40325277
yes because it is in another database , its sid = dev and the user = jun15
so i named the touser  dev.mar15
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40325479
>>so i named the touser  dev.mar15

If you created the user using double quotes, maybe you need the double quotes in the param file as well?

As I mentioned, I've never used a user with a '.' in the name.  Maybe it is causing issues.

As a test, can you create a user without using quotes and without a '.' and try importing a single table into the new user?
0
 

Author Comment

by:NiceMan331
ID: 40325738
The user mar15 I created without couts , it's name in its database dev is like this : mar15 , the import process here within user h2002 , when it ask me about user from user , h2092 available in the list , then to select : trouser , here I just refer to user mar15 followed after it's database name dev , I'm i correct ?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40325762
TOUSER should be mar15.
0
 

Author Comment

by:NiceMan331
ID: 40325821
But how it will understand where mar15 in which database is ?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40325841
mar15 is a user in whatever database you tell it to import into.

When you run the import command, you provide connection information for the database.

Are you trying to apply the SQL Server definition of a database to Oracle?

SQL Server and Oracle use very different terms when referring to databases and schemas.
0
 

Author Comment

by:NiceMan331
ID: 40325861
No , it's oracle , I'm not using SQL server ,
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40325869
I understand you are using Oracle.

I was asking if you were using the SQL Server definition of 'database' when you asked:
But how it will understand where mar15 in which database is ?

If not, I do not understand what you were asking.
0
 

Author Comment

by:NiceMan331
ID: 40325914
Sorry , let me try again using mar15 only to see if it will solve the problem
0
 

Author Comment

by:NiceMan331
ID: 40326123
look at the first shoot , here to select users
from user is ok , i can easly select the existing user h2002 in the current database
in the other field , the list not contain user mar15 which is logic , because it is from another database
and in this field i typed : mar15 , then i pressed button add
same result , empty log file , and no process, see the parameter file please
select.png
param.png
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40326135
You said you created the user mar15 in post http:#a40325738.
0
 

Author Comment

by:NiceMan331
ID: 40326744
Yes I did , but in another database , I mean this wizard gives you a list of all users in the current database , if I want to select user mar15 which is from different database it will not be included in those lists ,  but they mentioned in the same step of the wizard , user from another database may selected
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 400 total points
ID: 40326764
For you to import from a user to a user, it sort of makes sense that the user must exist in the database you are importing into.

You are exporting from userA in databaseA into databaseB to userB.

userA and databaseA information is inside the export file itself.  userB needs to exist inside databaseB to receive the information.
0
 

Author Comment

by:NiceMan331
ID: 40327107
It's like this : userB already exists inside databaseB
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40327823
I'm confused.

You said mar15 didn't exist in the database you are importing into.  It needs to.
0
 

Author Comment

by:NiceMan331
ID: 40327947
No , it is existing in the database I'm importing into , but not available in the list provided by wizard of toad because it show only users of current database
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40327954
I'm not a Toad user but if the user isn't listed in the TOUSER dropdown, then I'm thinking that user doesn't exist or you aren't connecting to the database you think you are connecting to.

I would suggest you give up trying to use the GUI and use the command line.  You already have the basics of the PARFILE.  Open up a CMD prompt and do it from there.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40330012
When you are doing import to another user it would fail as the another user will not be having full database import privileges, also remap option he might not be able to do it.

If doing from toad it would be much safer to do from and to the same user from source to destination.

SQL Developer has option to do this kind of operation but in that first it connects to both database and then list out the users from both sides to select. But you will need DBA or full database export privileges to do that.

If this is quite confusing you can go for straight forward simple export import with or without datapump from the server in command line mode.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40350517
Why the penalty grade?
0
 

Author Comment

by:NiceMan331
ID: 40350911
No penalty , it is by mistake only , the net interrupted suddenly then I have no authorization to change it , plz re open again then I can correct it
0
 

Author Closing Comment

by:NiceMan331
ID: 40353962
Dear All
i just accept your answers as an appreciating to your participation to solve my problem , but in actual , my problem not yet solved , i will try to use my account in "ToadWorld" to see if i got an answer
thanx to all
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question