Oracle DBA


Fzero

Advanced Member
Joined
Mar 9, 2010
Messages
4,702
Do we have any in the house?


I am having a bit of a problem with the Data Pump commands, specifically with what it does to my system user login afterwards -- Changing the password I think.


I've set up my import dir in SQLPLUS [MyIMPdir] and given permissions to it.


I was then using the following string to import:

impdp system/[MyPassword]@[MyDatabaseName] directory=[MyIMPdir] dumpfile=MyBackupDump.dmp schema=user1,user2

This was importing from theses schemas, but was omitting any tables which had no records, which causes a problem, I need to import all the tables regardless if they contain records or not.


So, I tried the below string, which I believe brings in all tables and schemas, as I am not defining specific ones.

impdp system/[MyPassword]@[MyDatabaseName] directory=[MyIMPdir] dumpfile=MyBackupDump.dmp

Problem with this method though... My system user login seems to get corrupt/changed.


After import, I try to connect to the database via SQLPLUS with my system login and it states the login is incorrect.


I then had to use CMD to run SQL in SQLPLUS as sysdba and change my password back for user system.


Not ideal obviously.


I've no idea why this is happening.


Anyone able to explain why my login is changed, or how I should be running the IMPDP command so that all tables are imported, even those with no records belonging to them ... without upsetting my system user password?


Been reading some Oracle sites but can't see what I am doing wrong, yet sure I'm not meant to be needing to reset my password...


Am I going wrong by using the user system to run this?


Cheers for any help, save me registering on an Oracle help forum hopefully
 
Last edited by a moderator:
First off, datapump is not a backup tool but a data-migration tool. If you need backup, use rman.


If you dont tell impdp what data it have to import, then it will import the whole dump.


As system have IMP_FULL_DATABASE, it is allowed to import even password. so in your case, impdp just restaured the system password from your dump file. (I bet you have made the export with full=yes)


But without expdp and impdp logs I cannot be sure.


also you should register to metalink ;)
 
Hmmm


Login as sys user and grant ‘import’ and ‘export’ privileges to any other user.


Along with that, you need to grant read , write access to the new user on DIRECTORY used by datapump.


give that a go!
 
Thanks sebt3, that makes sense. That is no doubt what is happening ... I'm restoring my system password with the one in the dump file.


Ideally, I would import only the tables belonging to my schemas 'user1' and 'user2', this would not overwrite/restore my system password...


...But when I do this currently I cannot get it to import tables without any records in.


That is what I really need to be able to do, I'll look into it, and may well join up on metalink then.


thanks
 
stainy. thanks I will give that a go too.


I have granted read,write to my user 'user1' but have not granted them import/export privileges.


I'll try that and see if I can run the IMPDP under their login, rather than system.


What I really need is a command that says 'export tables even if they have no records' ... which I can apply to my first string given above, that would do what I need.
 
Ideally, I would import only the tables belonging to my schemas 'user1' and 'user2', this would not overwrite/restore my system password...
...But when I do this currently I cannot get it to import tables without any records in.
your command with the schemas arguments should do it. there is something fishy going on. I just cant tell without the logs
 
Doh!


Schoolboy error on my part.


I tested with another dmp and had success, using the first method; defining the schemas.


This imported what I wanted and did not mess up my system login [i guess that was getting overwritten when not defining and importing all - as you said]


I can see tables with 0 records too, which I expected/hoped.


It seems the dmp I was provided before is missing some tables, which I thought were empty of records, seems they were not even on the dmp I have at all.


They will have to take another export to give me.


thanks for help sebt3
 
Back
Top