User creation and rights to import a dump I'm having problems creating an instance of Oracle 10.2.0.4 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.

export ORACLE_SID=HISTO;
sqlplus / as sysdba
CREATE USER ANC IDENTIFIED BY "xxxxxxx";         
grant connect, create session, imp_full_database to ANC;
commit;


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 &amp;

Output from the nohup.out file:

Import: Release 10.2.0.4.0 - 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 10.2.0.4.0 - 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 ?
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';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
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:

CREATE USER anc IDENTIFIED BY anc
       DEFAULT TABLESPACE tools;


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&gt; SELECT job_name, state FROM dba_datapump_jobs;