export/import user from cleint on server

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 ?
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjeev LabhDatabase ConsultantCommented:
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
Sanjeev LabhDatabase ConsultantCommented:
However, you need to have export import previliges or you will be allowed to do that for your own schema only.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
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
sdstuberCommented:
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
Sanjeev LabhDatabase ConsultantCommented:
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
Sanjeev LabhDatabase ConsultantCommented:
Attached is the screenshot of sql developer help which clearly states that you can do data pump.
data-pump-sql-dev.JPG
0
NiceMan331Author Commented:
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
Sanjeev LabhDatabase ConsultantCommented:
Probably will have to check the log files for more details of the error.
0
slightwv (䄆 Netminder) Commented:
Datapump creates a DMP file on the database server.  Did you manually move the file from server1 to server2 before trying to import?
0
NiceMan331Author Commented:
No slightw , I didn't , I'm still doing all from my machine
0
slightwv (䄆 Netminder) Commented:
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
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
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
sdstuberCommented:
>>> , 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
NiceMan331Author Commented:
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
sdstuberCommented:
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
NiceMan331Author Commented:
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
sdstuberCommented:
what's in your log file?
0
DavidSenior Oracle Database AdministratorCommented:
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
NiceMan331Author Commented:
what's in your log file?
here is the text file for exporting the user
exp-h2002-rud.log
0
slightwv (䄆 Netminder) Commented:
I believe what sdstuber was wanting was the log file for the import so we could see why it was failing.
0
NiceMan331Author Commented:
THE LOG file fr import is empty
0
DavidSenior Oracle Database AdministratorCommented:
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
NiceMan331Author Commented:
So what dvz ? I didn't get your idea
0
slightwv (䄆 Netminder) Commented:
If the import fails, there has to be some error messages somewhere.  Imports almost always provides a log.
0
NiceMan331Author Commented:
ok
let me repeat the process again and send you the error
0
DavidSenior Oracle Database AdministratorCommented:
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
NiceMan331Author Commented:
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
Sanjeev LabhDatabase ConsultantCommented:
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
NiceMan331Author Commented:
the log file is empty as i mentioned above
0
slightwv (䄆 Netminder) Commented:
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
NiceMan331Author Commented:
yes because it is in another database , its sid = dev and the user = jun15
so i named the touser  dev.mar15
0
slightwv (䄆 Netminder) Commented:
>>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
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
TOUSER should be mar15.
0
NiceMan331Author Commented:
But how it will understand where mar15 in which database is ?
0
slightwv (䄆 Netminder) Commented:
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
NiceMan331Author Commented:
No , it's oracle , I'm not using SQL server ,
0
slightwv (䄆 Netminder) Commented:
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
NiceMan331Author Commented:
Sorry , let me try again using mar15 only to see if it will solve the problem
0
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
You said you created the user mar15 in post http:#a40325738.
0
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
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
NiceMan331Author Commented:
It's like this : userB already exists inside databaseB
0
slightwv (䄆 Netminder) Commented:
I'm confused.

You said mar15 didn't exist in the database you are importing into.  It needs to.
0
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
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
Sanjeev LabhDatabase ConsultantCommented:
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
sdstuberCommented:
Why the penalty grade?
0
NiceMan331Author Commented:
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
NiceMan331Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.