I'm having problems creating an instance of Oracle and importing a dump from other environment. I'm using Oracle under AIX.

I used a ksh script and everything goes right except when creating the users.

After that I create the default user, used to connect to the database and other working modules. The problem comes when trying to import the dump.

sqlplus / as sysdba
grant connect, create session, imp_full_database to ANC;

The error when trying to import de dump is the following. Command used to load the dump: nohup impdp ANC/xxxxxx DIRECTORY=MIG11G DUMPFILE=dump_histo.expdp LOGFILE=impdp_29102013.log schemas=HISTO &

Output from the nohup.out file:

Import: Release - 64bit Production on Lundi, 23 Décembre, 2013 18:51:22

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "ANC.SYS_IMPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-01950: no privileges on tablespace 'TOOLS'

My question: Is it normal haven't got system user to load a dump file ?
Why the load of the dump file fails ?

  • Your question isn't clear. What errors are you seeing?
    – Philᵀᴹ
    Commented Dec 23, 2013 at 21:29
  • Sorry, i made a mistake cause i create the question before haven't finished it. Commented Dec 23, 2013 at 21:35
  • 4
    The user ANC doesn't have any quota on the TOOLS tablespace. Grant it, or use remap_tablespace to remap the tablespaces in the file to ones in the database that the user has the necessary grants for.
    – Philᵀᴹ
    Commented Dec 23, 2013 at 21:37
  • Could you explain your answer, i don't know how to do that. Commented Dec 23, 2013 at 21:38
  • 1
    If you don't know how to do that, there is a really good website for looking up instructions or asking questions if you get stuck. Need to google it as I forget the name, but it is either exchange something or something overflow.com
    – Thronk
    Commented Dec 24, 2013 at 16:38

1 Answer 1


The error you receive means that the user ANC does not have any quota on the TOOLS tablespace, as FreshGrinchOfSO says. From your code, it seems that your default permanent tablespace is TOOLS (and not USERS as usual).

SELECT * FROM database_properties WHERE property_name LIKE 'DEFAULT%TABLESPACE';

------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

Otherwise, you could create the user ANC just like this way:


To verify the quota of ANC on TOOLS use this query:

SELECT tablespace_name, username, bytes, max_bytes FROM dba_ts_quotas;

You will obtain "no rows selected". To assign a quota to ANC on TOOLS you can proceed as follows:

expdp histo/passwd estimate_only=y NOLOGFILE=y
Total estimation using BLOCKS method: 1.256 KB
ALTER USER anc QUOTA 5m on tools; -- a x3, safety margin :-)

Then perform the import:

impdp anc/anc directory=MIG11G dumpfile=dump_histo.expdp schemas=histo

If the job seems to be suspended, check for errors in the alertlog file. For example:

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was suspended due to
    ORA-01536: space quota exceeded for tablespace 'TOOLS'

Then, it is necessary to increase the quota, than the job will resume automatically.

statement in resumable session 'ANC.SYS_IMPORT_SCHEMA_01' was resumed

To see all current jobs:

sqlplus / as sysdba
SQL> SELECT job_name, state FROM dba_datapump_jobs;
  • @JorgeVegaSánchez: don't mention it! :-)
    – Shaunyl
    Commented Dec 24, 2013 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.