IMP-00019: row rejected due to ORACLE error 12899

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Did you get ORA-12899 and its associated IMP-00019 while attempting to import an old-style EXP dump? 

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."TMAGANFS"."DESCR" (actual: 51, maximum: 50)

The reason is quite simple: the exported dump comes from a database whose character set is different from the one where you are attempting to import the data into *AND* one or more characters in the offending string in the source database needed fewer bytes than they need now in the target database.

For example, an accented character like 'à' in a WE8ISO8859P1 database requires just 1 byte, but in a AL32UTF8 database it needs two bytes:

select lengthb('à') l , rawtohex('à') u from dual;

L U  
- ----
2 C3A0

The error however is caused by the fact that the receiving column has been defined in bytes, for instance 50 bytes as in my case, whereas the total length, in bytes, is now 51).

The quick fix, that is far from being optimal, at least in my view, is to enlarge the receiving column and in order to do so, you might need to split the data import in three phases:

1. in the first phase you just create the objects without importing the data (IMP parameter ROWS=N)
2. in the second phase, you enlarge the affected columns
3. in the third phase, you import the dump again, specifying the parameter IGNORE=Y, because the existing objects would make the import fail otherwise.

See message translations for ORA-12899 and search additional resources.

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