Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 &

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 ?

share|improve this question
    
Your question isn't clear. What errors are you seeing? –  Phil Dec 23 '13 at 21:29
    
Sorry, i made a mistake cause i create the question before haven't finished it. –  Jorge Vega Sánchez Dec 23 '13 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 Dec 23 '13 at 21:37
    
Could you explain your answer, i don't know how to do that. –  Jorge Vega Sánchez Dec 23 '13 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 Dec 24 '13 at 16:38

1 Answer 1

up vote 5 down vote accepted

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> SELECT job_name, state FROM dba_datapump_jobs;
share|improve this answer
    
THanks for your answer. –  Jorge Vega Sánchez Dec 24 '13 at 13:31
    
@JorgeVegaSánchez: don't mention it! :-) –  Shaunyl Dec 24 '13 at 13:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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