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 exported data will be copied
  • <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:
  • it 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

About these ads

2 thoughts on “impdp and ORA-01950: no privileges on tablespace

  1. Pingback: Documentation of hard work | How to find path of Oracle directory (exp_dir, imp_dir)?

  2. Pingback: Documentation of hard work | Expdp and ORA-39070: Unable to open the log file

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s