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

Imp and ORA-02430 cannot enable constraint

There is nothing particularly difficult in finding the cause of this message, but it's somewhat funny to read the action paragraph in the official Oracle Database Error Message:

ORA-02430: cannot enable constraint (string) - no such constraint

Cause: the named constraint does not exist for this table.

Action: Obvious

Obviously, isn't it?

At any rate, as i said, it's not so difficult to understand the reason of the error message, but to find the real reason of the problem may require just a little longer.

Scenario:

I was running IMP for loading a schema dump.
It terminated with warnings.
In the list of errors i encountered, i found the error above and i lazily looked up the error message in the official error message book.

Why doesn't exist? It's supposed to be *in the dump*, i mean, i am not executing this statement manually, so the name of the constraint i am sure is correct.

So, if the name isn't the problem, it must be the constraint itself that is not present.
Why?
The constraint DDL statement must be good, i am importing an empty schema, without data, so it cannot be because of a referential violation in the data that caused my not deferrable initially not deferred constraint to fail.

Then i must conclude it's a problem with the underlying objects, either the table upon which the referential constraint is being built or with the table column(s) being referred to.

This means to go back to the import log and dig out previous error messages associated to those objects.
Eventually i got the real one:
the table upon which the constraint is built was not created because its DDL statement was referencing a nonexistent tablespace.
As a result, the constraint was not built, so, obviously, it could not be enabled by imp.exe at a later time.

This is just to show that what is obvious for Oracle, can be less obvious, albeit not dramatically difficult, to spot for the DBA.

---

Translated messages:
ORA-02430: impossibile abilitare vincolo - non esiste tale vincolo
ORA-02430: no se puede activar la restricción - no existe tal restricción
ORA-02430: no es pot activar la restricció - no existeix
ORA-02430: impossible désactiver contrainte - pas de telle contrainte
ORA-02430: Constraint nicht aktivierbar - Constraint ist nicht vorhanden
ORA-02430: δεν μπορεί να γίνει ενεργοποίηση περιορισμού - δεν υπάρχει τέτοιος περιορισμός
ORA-02430: begrænsningen kan ikke aktiveres - ingen sådan begrænsning
ORA-02430: Kan inte aktivera begränsningen - den saknas
ORA-02430: kan ikke aktivere skranken - skranken finnes ikke
ORA-02430: rajoitetta ei voi ottaa käyttöön - tällaista rajoitetta ei ole
ORA-02430: a(z) megszorítás nem engedélyezhető - nem létezik
ORA-02430: nu se poate activa constrângerea - nu există o astfel de restricţie
ORA-02430: Kan beperking niet activeren - beperking bestaat niet.
ORA-02430: não é possível ativar a restrição - esta restrição não existe
ORA-02430: não é possível activar restrição - não existe esta restrição
ORA-02430: невозможно разблокировать ограничение - нет такого ограничения
ORA-02430: nelze aktivovat omezení - toto omezení neexistuje
ORA-02430: nemožno aktivovať obmedzenie - také obmedzenie neexistuje
ORA-02430: nie można uaktywnić więzów - nie ma takich więzów
ORA-02430: kısıtlama etkinleştirilemez - böyle bir kısıtlama yok

Exporting and importing pages in Apex

If you see the following message:

This page was exported from a different application or
from an application in different workspace.
Page cannot be installed in this application.

while trying to install a single page in Oracle ApEx, then check if the following conditions apply to your case:

1. The workspace where the page was exported from, has got a different ID from the workspace where you want to install the page into.
In this case, you can manually change the ID in the export file, you can easily locate it by looking at the parameter p_security_group_id, contained in one of the first lines of the script, as in this case:

wwv_flow_api.set_security_group_id(p_security_group_id=>23111618468800456);

This is the source workspace ID (the number will be different of course...). You need to replace this number with your target workspace ID. If you don't know what is the target ID, export a page from your target environment and look for it in the file, as we already did above.

2. Your workspace ID is OK (either the workspace ID is the same for both environments or you just changed it as explained earlier) but you are still getting the message.

Check if the target application is currently selected or if the current application (assuming you have more than one in the same workspace) is a different one.
Let's say that in the Application builder environment you have two applications, one is application 12345 and the other one is 67890.
Now, if 12345 is currently selected (which means that you see this number in the upper right corner of the Application Builder environment, you will not be able to import a page for application 67890, even if the workspace ID is the same.

In order to successfully import a page, you need first to switch to application 67890 and reprocess the imported file. Note that you don't need to upload the file again in the repository, unless you deleted it, the file is still there and all you need to do is to click on the "Install" link.


Needless to say, your page components (underlying tables, LOVs, templates and so on) need to be the same in both environments, otherwise the page won't work correctly.



PS: I hereby decline any responsibility for messing up your environment! ;-)