Lets not waste time
Why impdp gives error ORA-01950: no privileges on tablespace <tablespacename> even with system user?
I executed impdp with these parameters:
impdp system/<password> directory=<imp_dir> dumpfile=<dumpfilename>.dmp logfile=imp_yyyymmdd.log full=y remap_schema=<oldschemaname>:<newschemaname>
But in log file imp_yyyymmdd.log I found the error:
ORA-01950: no privileges on tablespace <tablespacename>
Whats wrong, why such error with “system” user?
Few hours browsing web…
The problem is that <newschemaname> user don’t have rights for tablespace <tablespacename> mentioned in error.
You have to connect to database and execute this alter user operation:
alter user <newschemaname> quota unlimited on <tablespacename>;
Longer story about my experience using Oracle 11g Data Pump.
Time to time I have to move data from one database to other. We use Oracle 11g database and Data Pump for exporting (expdp) and importing data (impdp).
Export
I use this command line to export data:
expdp <username>/<password> directory=<exp_dir> dumpfile=<dumpfilename>.dmp logfile=<exp_yyyymmdd>.log schemas=<schemanametoexport> include=table,index
I have to comment few things:
- <username> is database user
- <exp_dir> is directory name that is created in database. Directory can be created by command below. Please, be sure that <path> exists and Oracle service have access to <path>:
-
create directory <exp_dir> AS '<path>'
- be sure that <username> have rights to write in <path>:
-
grant read, write on directory <exp_dir> to <username>
- <dumpfilename>.dmp will be data file in <exp_dir> where exported data will be stored
- <exp_yyyymmdd>.log will be log file in <exp_dir> where export execution log will be stored
- <schemanametoexport> is schema name that will be exported
Detailed description of Oracle 11g Data Pump Export you can read here:
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm
After export is performed you will have two files in <path> – <dumpfilename>.dmp and <exp_yyyymmdd>.log. If you don’t have dump file then you have to look for the problem in <exp_yyyymmdd>.log.
Import
Now you can copy <dumpfilename>.dmp where the new database can access it. In my case new database is also Oracle 11g.
I use this command line to import data:
impdp system/<password> directory=<imp_dir> dumpfile=<dumpfilename>.dmp logfile=<imp_yyyymmdd>.log full=y remap_schema=<oldschemaname>:<newschemaname>
I have to comment few things:
- I use system user for import. I am sure that other users also can be used for import that have enough rights
- <imp_dir> is directory name that is created in new database. Directory can be created by command below. Please, be sure that <path> exists and Oracle service have access to <path>:
-
create directory <imp_dir> AS '<path>'
- <dumpfilename>.dmp will be data file in <imp_dir> where import data will be taken
- <imp_yyyymmdd>.log will be log file in <imp_dir> where import execution log will be stored
- in my case I have to remap schemas and that can be done with “remap_schema”
- <oldschemaname> is schema name in <dumpfilename>.dmp that was exported
- <newschemaname> is schema name in new database where data will be imported. You have to know few things about new schema:
- new schema have to be created before import:
-
create user <newschemaname> identified by ***
- new user need rights to access <imp_dir>
-
grant read, write on directory <imp_dir> to <newschemaname>
- and user <newschemaname> need rights on tablespace otherwise you will get ORA-01950
-
alter user <newschemaname> quota unlimited on <tablespacename>
Detailed description of Oracle 11g Data Pump Import you can read here:
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm
Pingback: Documentation of hard work | How to find path of Oracle directory (exp_dir, imp_dir)?
Pingback: Documentation of hard work | Expdp and ORA-39070: Unable to open the log file