IMP-00017, ORA-01536, ORA-00959: IMPDP vs IMP

What happened to me yesterday is a good example of a too-long-deferred transition from the traditional EXP/IMP process to the newest EXPDP/IMPDP (aka Oracle Data Pump technology).

Scenario:

CREATE TABLE "IMAGES" (
"ID" NUMBER(9, 0),
"IMAGE" BLOB)
TABLESPACE "YOCOYA"
LOB ("IMAGE") STORE AS (TABLESPACE "YOCOYA")
/
CREATE TABLE "ITEMS" (
"ID" NUMBER(9, 0),
"NAME" VARCHAR2(50))
TABLESPACE "YOCOYA"
/

Two simple tables that i want to copy from one user to another, with each user having his/her objects in a separate tablespace, a typical situation for an hosted environment for instance.

> exp yocoya/yocoya@xe file=test.dmp tables=(items, images)

> imp yocoyatx/yocoyatx@xe file=test.dmp tables=(items, images)

Import: Release 10.2.0.1.0 - Production on Wed May 30 12:20:51 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by YOCOYA, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing YOCOYA's objects into YOCOYATX
. importing YOCOYA's objects into YOCOYATX
. . importing table                        "ITEMS"          0 rows imported
IMP-00017: following statement failed with ORACLE error 1536:
"CREATE TABLE "IMAGES" ("ID" NUMBER(9, 0), "IMAGE" BLOB)  PCTFREE 10 PCTUSED"
" 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
"PS 1 BUFFER_POOL DEFAULT) TABLESPACE "YOCOYA" LOGGING NOCOMPRESS LOB ("IMAG"
 "E") STORE AS  (TABLESPACE "YOCOYA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERS"
 "ION 10 NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
 " BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1536 encountered
ORA-01536: space quota exceeded for tablespace 'YOCOYA'
Import terminated successfully with warnings.

Now, let's review the IMP log:

in green you see that table ITEMS has been successfully imported even if the specified tablespace in the export dump was YOCOYA and this is an expected behavior because IMP takes the owner's default tablespace if the original one cannot be used because it doesn't exist or the user does not have the necessary quota, as in this case.

What can be surprising indeed is the second failing statement, where Oracle complains about the lack of quota on tablespace YOCOYA and one may wonder why it didn't complain before then.

Well, the reason is in that Oracle is not complaining about the main table storage clause, but about the LOB storage clause (above, in red). According to the documentation this restriction applies also for IOT tables, nested tables and user defined object tables.

Note also that if i had imported these objects into a database where no YOCOYA tablespace was existing, i would get instead:

IMP-00017: following statement failed with ORACLE error 959:
...
ORA-00959: tablespace 'YOCOYA' does not exist

So Oracle makes a useful distinction here between a nonexistent tablespace and an existing tablespace with no quota.

And now for the workaround.

Forget to manually create the table and re-execute IMP, there is a much simpler solution for this, avalaible since Oracle version 10, kindly suggested by my friend John Scott, who adopted EXPDP/IMPDP (Oracle Data Pump) much earlier than me:

> expdp yocoya/yocoya@xe tables=(items,images) dumpfile=exp_dir:testdp.dmp nologfile=Y

> impdp yocoyatx/yocoyatx@xe remap_schema=YOCOYA:YOCOYATX REMAP_TABLESPACE=YOCOYA:YOCOYATX
DIRECTORY=exp_dir dumpfile=testdp.dmp logfile=test.log

Import: Release 10.2.0.1.0 - Production on Wednesday, 30 May, 2007 15:29:34

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

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "YOCOYATX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "YOCOYATX"."SYS_IMPORT_FULL_01":  yocoyatx/********@xe
remap_schema=YOCOYA:YOCOYATX REMAP_TABLESPACE=YOCOYA:YOCOYATX
DIRECTORY=exp_dir dumpfile=testdp.dmp logfile=test.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "YOCOYATX"."IMAGES"                             0 KB       0 rows
. . imported "YOCOYATX"."ITEMS"                              0 KB       0 rows
Job "YOCOYATX"."SYS_IMPORT_FULL_01" successfully completed at 15:29:59

One important difference between EXP/IMP and EXPDP/IMPDP is the fact that the latter is a tool that works entirely at the server side. In other words, don't expect to receive the file on your client machine if you are connected remotely, the data pump will export the file in a local folder specified in various ways and depending on the configuration and privileges granted to the user doing the operation. You can find the relevant information about these parameters in the Overview of Oracle Data Pump. In the example above i had to create a DIRECTORY object exp_dir beforehand, as well as granting read and write privileges to both users, otherwise the export operation would fail (which will the topic of my next posting i guess...).

So, what if you need to export a file over a network?

You'll need to have access to the directory where the file has been exported and get the file via FTP, via a network share or whatever other method is available.

Updated July 27, 2007:
John Scott points out that if the user performing the import holds unlimited tablespace privilege, then error ORA-01536 may not be returned at all. Note also that such privilege can be held indirectly if the user has been granted a role like resource (this role has been deprecated since Oracle 9iR2 by the way).

See message translations for IMP-00017, ORA-01536, ORA-00959 and search additional resources.
---------------------------------------

ORA-01536: quota di spazio superata per tablespace ''
ORA-01536: cuota de espacio excedida para tablespace ''
ORA-01536: s'ha excedit la quota d'espai del tablespace ''
ORA-01536: dépassement du quota d'espace affecté au tablespace ''
ORA-01536: Speicherplatz-Zuteilung für Tablespace '' überschritten
ORA-01536: το quota χώρου ξεπεράσθηκε για το χώρο πινάκων (tablespace) ""
ORA-01536: pladskvota er overskredet for tabelområdet ''
ORA-01536: utrymmeskvot överskriden för tabellutrymme ''
ORA-01536: plasskvoten er overskredet for tabellområdet
ORA-01536: tilakiintiö ylitetty taulualueelle ''
ORA-01536: terület kvóta túllépés a(z) '' táblaterületre
ORA-01536: cota de spaţiu depăşită pentru spaţiul tabelă ''
ORA-01536: Geheugenquota is overschreden voor tabelruimte ''.
ORA-01536: cota de espaço excedida para o tablespace ''
ORA-01536: quota de espaço excedida para o tablespace ''
ORA-01536: превышена квота памяти для раздела ''
ORA-01536: kvóta pro prostor tabulek '' překročena
ORA-01536: kvóta prekročená pre tabuľkový priestor ''
ORA-01536: przekroczenie limitu w przestrzeni tabel ''
ORA-01536: tablo '' için alan kotası aşıldı

ORA-00959: tablespace '' inesistente
ORA-00959: el tablespace '' no existe
ORA-00959: el tablespace '' no existeix
ORA-00959: le tablespace '' n'existe pas
ORA-00959: Tablespace '' nicht vorhanden
ORA-00959: ο χώρος πινάκων (tablespace) "" δεν υπάρχει
ORA-00959: tabelområdet '' findes ikke
ORA-00959: tabellutrymmet '' finns inte
ORA-00959: tabellområdet  finnes ikke
ORA-00959: taulutilaa '' ei ole olemassa
ORA-00959: a(z) '' táblaterület nem létezik
ORA-00959: spaţiul tabel '' nu există
ORA-00959: Tabelruimte '' bestaat niet.
ORA-00959: tablespace '' não existe
ORA-00959: tablespace '' não existe
ORA-00959: раздел '' не существует
ORA-00959: tabulkový prostor '' neexistuje
ORA-00959: tabuľkový priestor '' neexistuje
ORA-00959: przestrzeń tabel '' nie istnieje
ORA-00959: '' tablo alanı mevcut değil