impdp and ORA-01950: no privileges on tablespace

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

Advertisements