ORA-27476: "SYS.MY_FANCY_JOB_CLASS" does not exist

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

Ever exported a whole schema containing DBMS_SCHEDULER jobs with non-default job classes?
Chances are that you hit the following error message when you attempt to import the dump later on.

ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27476: "SYS.MY_FANCY_JOB_CLASS" does not exist

Job classes defined with DBMS_SCHEDULER.CREATE_JOB_CLASS are not owned by the schema user but by user SYS (as inferred by looking at the error message itself).

As a result, any jobs whose job class has not been re-created before importing the dump, won't be (re)created.



If, for some reason, the script for creating the job is not readily available but you have access to the database where the dump was taken, you can quickly generate a script for the job via the "Create like" feature of SQLDeveloper.
Note however that the missing job class must be (re)created manually, SQLDeveloper doesn't help with that.

exec DBMS_SCHEDULER.CREATE_JOB_CLASS('MY_FANCY_JOB_CLASS');

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

ORA-00439: feature not enabled: Deferred Segment Creation

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

If you are wondering why after exporting with EXPDP a schema from an Oracle Enterprise Edition 11gR2 database and importing it on an Oracle Standard Editon 11gR2 you are getting errors like:

ORA-00439: feature not enabled: Deferred Segment Creation

then you must know you have just hit one of the new features of 11gR2. Unfortunately this feature makes the life of the casual DBA a little bit more complicated than necessary.

I found an interesting debate about whether this must be considered a feature or a bug in the OTN forums but I very much appreciated the last comment where Michiel provided a working solution without delving into philosophical matters. I remember doing the same thing when dealing with another EXPDP/IMPDP problem between versions 11.1.0.6 and 11.1.0.7.

-- on the source instance
EXPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2

-- on the target instance
IMPDP user/pwd dumpfile=somename.dmp directory=DATA_DUMP_DIR nologfile=Y version=10.2

If there are any 11g features that need to be preserved across the import, then you will need to find an alternate solution, like artificially populating the empty tables that seem to trigger this problem or re-create manually the failing objects, until Oracle comes up with some command line switch for EXPDP or IMPDP to turn off or ignore the deferred segments option.

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

ORA-31694: master table "SYS"."SYS_IMPORT_FULL_01" failed to load/unload

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

It's amazing how much time one can waste because of certain bugs.
Yesterday evening i got the following stack of errors upon importing (IMPDP) a dump made on Oracle 11.1.0.7.0 to 11.1.0.6.0.

ORA-39002: invalid operation
ORA-31694: master table "SYS"."SYS_IMPORT_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "SYS"."SYS_IMPORT_FULL_01".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

My first reaction was to tamper with the IMPDP parameters, on the false assumption that i was doing something wrong (who knows why i always assume I'm wrong...).
After various attempts, i started looking around and found several references that unfortunately were misleading. Someone claimed a VERSION=11.1.0.6.0 parameter was missing, another suggested a wrong character set or a corrupt dump and so on. Every round implied a new dump to be made available by the customer, downloaded and imported.
Finally, this morning, i found a rather recent OTN thread were it was made clear that the above errors are a consequence of bug 7590679 in 11.1.0.7.

The quick solution is to perform the export (EXPDP) adding parameter VERSION=10.2 (with some caveats as per cited metalink note) or alternatively install the patch for upgrading from 11.1.0.6 to 11.1.0.7.

As an experiment i also tried importing the downgraded dump to XE and it worked like a charm.

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

ORA-31641: unable to create dump file

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

You are getting this error while performing a data pump export (EXPDP) using a dump filename that is already present at the destination.

EXPDP 'user/pwd' SCHEMAS=user DUMPFILE=dumpfile.dmp NOLOGFILE=Y DIRECTORY=expdir

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product\10.1.0\admin\orcl10\dpdump\dumpfile.dmp"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists

Dump files are never overwritten (in contrast with LOG files and SQL files generated by the data pump), so you must either change the dump filename in the command line or rename or move or delete the existing file prior to attempting the operation again.

See a previous posting for other common error situations that you can encounter when performing a data pump export.

See message translations for ORA-31641 and search additional resources

ORA-39145: directory object parameter must be specified and non-null

If you, like me, tend to forget the varied syntax of the myriad of oracle commands that have been introduced over the years, you won't be surprised to get errors like that i am going to describe.

EXPDP is the data pump export introduced with Oracle version 10 and it comes with a somewhat funny syntax for what concerns the specification of the export folder, because, unless you are doing the export as a privileged user, a user presumably holding EXP_FULL_DATABASE role like the DBA, it expects that you specify the oracle directory object name along with the name of the file dump. See the linked document for additional possibilities.

This is why when i submitted the following command:

expdp schemas=yocoya dumpfile=testdp.dmp nologfile=Y

i got this verbose error message in return:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39145: directory object parameter must be specified and non-null

Supposing you have defined a directory object called EXP_DIR, then the correct form of the command is the following:

expdp schemas=yocoya dumpfile=exp_dir:testdp.dmp nologfile=Y

or

expdp schemas=yocoya directory=exp_dir dumpfile=testdp.dmp nologfile=Y

As usual, the object name is case insensitive unless you have explicitly created it enclosed in double quotes. See also the related article about errors caused by the lack of read and write privileges on the destination folder or when an existing file is found.

See error translations for ORA-39145 and search additional resources.

---------------------------------------
ORA-39145: il parametro oggetto directory deve essere specificato e diverso da null
ORA-39145: se debe especificar el parámetro de objeto de directorio y no ser nulo
ORA-39145: el paràmetre d''objecte de directori s''ha d''especificar i no pot ser nul
ORA-39145: le paramètre d'objet de répertoire doit être indiqué et il ne doit pas avoir la valeur Null
ORA-39145: Verzeichnisobjektparameter muss angegeben werden und darf nicht Null sein.
ORA-39145: η παράμετρος αντικειμένου καταλόγου πρέπει να προσδιοριστεί με τιμή διαφορετική της τιμής null
ORA-39145: katalogobjektparameter skal være angivet og ikke-NULL
ORA-39145: katalogobjektparametern måste anges och får inte vara null
ORA-39145: katalogobjektparameteren må være angitt og ikke være null
ORA-39145: hakemisto-objektiparametri on määritettävä, ja se ei saa olla tyhjä
ORA-39145: a könyvtárobjektum paramétert meg kell adni, nem lehet üres
ORA-39145: parametrul pentru obiectul director trebuie să fie specificat şi nenul
ORA-39145: een directoryobjectparameter moet worden opgegeven die niet NULL mag zijn
ORA-39145: o parâmetro de objeto de diretório deve ser especificado e não-nulo
ORA-39145: é necessário que o parâmetro do objecto do directório seja especificado e não seja nulo
ORA-39145: требуется задать параметр 'объект каталога' с определенным значением
ORA-39145: parametr objektu adresáře musí být zadán a nesmí být null
ORA-39145: parameter objektu adresára musí byť zadaný a nesmie mať hodnotu null
ORA-39145: parametr obiektu-katalogu musi być podany i nie może mieć wartości Null
ORA-39145: dizin nesnesi parametresi belirtilmeli ve null olmamalıdır
ORA-39145: directory object parameter must be specified and non-null

See message translations for ORA-39145 and search additional resources

ORA-39001, ORA-39000, ORA-39087, ORA-31631 and the missing directory privileges

As i explained in my previous posting, after fixing the quota problem, you can still hit a few problems:

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

Export: Release 10.2.0.1.0 - Production on Thursday, 31 May, 2007 10:06:24

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

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39087: directory name EXP_DIR is invalid

Oracle is complaining about EXP_DIR, but if we check ALL_DIRECTORIES:

select * from all_directories;

OWNER  DIRECTORY_NAME  DIRECTORY_PATH
-----  --------------  --------------
SYS    EXP_DIR         C:\Temp

We see that the directory is existing and (i guess) points to a valid path.

This means that most likely we forgot to grant the WRITE privilege on that directory to the user doing the export. Before granting (or asking for) the privilege, you can find out if you have been granted the privilege in this fashion:

select * from user_tab_privs
where table_name = 'EXP_DIR';

no rows selected

So let's try and see what we get:

grant write on directory exp_dir to yocoya;

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

Export: Release 10.2.0.1.0 - Production on Thursday, 31 May, 2007 11:10:53

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

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31631: privileges are required

With some surprise we see that Oracle this time is complaining about missing privileges just after that we granted the write permission!
Interestingly enough, it means that the READ privilege is necessary too, not just WRITE.
Let's do it:

grant read on directory exp_dir to yocoya;

Now, unless you forgot to delete a previous dump with the same name, you should get the export dump, finally!


PS: if you specified an existing name for the dump file, you will see the following error:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "H:\Documents\Apex\oracletext\testdp.dmp"
ORA-27038: created file already exists
OSD-04010: opci┐CREATE> especificada; el archivo ya existe

This is another difference between EXP and EXPDP, because EXP overwrites the export file without warnings.

Funnily enough i got the last message in spanish, the primary installation language i guess, even if i had...

set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

There must be some quirk in some DLL, but don't count on me to find out which one it is!

¡Que viva Mexico!

See message translations for ORA-39001, ORA-39000, ORA-39087, ORA-31631 and search additional resources.

ORA-31626,ORA-31633,ORA-01950 and the missing quota

If while using the Oracle Data Pump Export utility EXPDP you get something like:

Export: Release 10.2.0.1.0 - Production on Thursday, 31 May, 2007 10:14:20

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

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-31626: job does not exist
ORA-31633: unable to create master table "YOCOYA.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'

It means that you forgot to grant a quota on the default tablespace of the user, in this case named YOCOYA.

alter user YOCOYA quota n M on USERS

will fix the problem unless the total allowance of n megabytes is insufficient to carry out the export, in which case you will get:

ORA-31626: job does not exist
ORA-31633: unable to create master table "YOCOYA.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'

See message translations for ORA-31626, ORA-31633, ORA-01950, ORA-01536 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