This discussion is archived
11 Replies Latest reply: Dec 24, 2012 6:22 AM by Srini Chavali-Oracle RSS

Import of external table fail because directory object doesn't exist

Pyrocks Explorer
Currently Being Moderated
Hi,

In our databases we have a few external tables which rely on files that can be located in different physical directories, and these directories are being created dynamically.
loading these files is not an issue since we can create directory objects dynamically - and it works great.
The directory names are created as 'AUTO_GENERATED_DIR_n' where n is derived from a sequence - we cannot reuse directory names because of certain limitations.

The problem starts when trying to import the extenral table from an expdp export - they exist in the export with the latest directory object used (in example DEFAULT DIRECTORY AUTO_GENERATED_DIR_1), and then impdp fails to import them to a database where the dynamically created directory objects don't exist (AUTO_GENERATED_DIR_1 doesn't exist).

how can we bypass it?
Is there a way to do a transform - like can be done on types using TRANSFORM=oid:n:type ?
or maybe remap all directory objects to a directory object that i can assure exists in all of our DBs ?

Since i'm not familiar with such switches - I thought about an alternative, but it is also problematic:
change the default directory of all external tables before the export to a directory that exists in all DBs, but for that we must ensure that no one will be changing the default dir during the export or by the time the export is issued (and in 99% of the time this will happen because files are being loaded all the time and we can't stop everything from working).
We can also try to pre-create all possible directory object names in all DBs but i want to avoid it as it is both ugly and not 100% failsafe.

any suggestions would be more than welcome.
thanks
-Mor
  • 1. Re: Import of external table fail because directory object doesn't exist
    damorgan Oracle ACE Director
    Currently Being Moderated
    Three things would be really helpful here ...

    1. A version number (SELECT * FROM v$version).
    2. The actual error messages received when the import fails.
    3. The operating system name and version.
  • 2. Re: Import of external table fail because directory object doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl also post the complete export and import commands used, along with the first 15 lines of the export and import logs

    HTH
    Srini
  • 3. Re: Import of external table fail because directory object doesn't exist
    Pyrocks Explorer
    Currently Being Moderated
    Sure, no problem:

    1. A version number (SELECT * FROM v$version).
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE     11.2.0.3.0     Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    2. The actual error messages received when the import fails.
    Creating a directory object which is unique to this DB + a test external table
    CREATE DIRECTORY NON_EXISTING_DIR_IN_OTHER_DB as 'd:\user_exports';
    
    CREATE TABLE MOR_DBA.BASIC_ADL
    (
      ADMIN_LOG_DATE            DATE,
      ADMIN_LOG_HOST            VARCHAR2(4000 BYTE),
      ADMIN_LOG_APP             VARCHAR2(4000 BYTE),
      ADMIN_LOG_MODULE          NUMBER(10),
      ADMIN_LOG_TYPE            NUMBER(10),
      ADMIN_LOG_INFO            VARCHAR2(4000 BYTE),
      USER_ID                   NUMBER(10),
      ADMIN_LOG_KEY_VALUES      VARCHAR2(4000 BYTE),
      LRE_ID                    NUMBER(10),
      SERVER_ID                 NUMBER(10),
      ADMIN_LOG_ACTION          NUMBER(10),
      ADMIN_LOG_EVENT_SOURCE    VARCHAR2(4000 BYTE),
      ADMIN_LOG_EVENT_SEVERITY  NUMBER(10),
      ADMIN_LOG_SPARE1          VARCHAR2(4000 BYTE),
      ADMIN_LOG_SPARE2          VARCHAR2(4000 BYTE),
      ADMIN_LOG_SPARE3          VARCHAR2(4000 BYTE),
      ADMIN_LOG_SPARE4          VARCHAR2(4000 BYTE),
      ADMIN_LOG_SPARE5          VARCHAR2(4000 BYTE)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY NON_EXISTING_DIR_IN_OTHER_DB
         ACCESS PARAMETERS 
           ( records delimited  by '\n'
          nologfile 
          nobadfile
          nodiscardfile
          fields  terminated by ';' 
          missing field values are null( ADMIN_LOG_DATE DATE "YYYY-MM-DD HH24:MI:SS",ADMIN_LOG_HOST ,ADMIN_LOG_APP ,ADMIN_LOG_MODULE ,ADMIN_LOG_TYPE ,ADMIN_LOG_INFO ,USER_ID ,ADMIN_LOG_KEY_VALUES ,LRE_ID ,SERVER_ID ,ADMIN_LOG_ACTION ,ADMIN_LOG_EVENT_SOURCE ,ADMIN_LOG_EVENT_SEVERITY ,ADMIN_LOG_SPARE1 ,ADMIN_LOG_SPARE2 ,ADMIN_LOG_SPARE3 ,ADMIN_LOG_SPARE4 ,ADMIN_LOG_SPARE5 )             )
         LOCATION (NON_EXISTING_DIR_IN_OTHER_DB:'dummy.txt')
      )
    REJECT LIMIT 0
    NOMONITORING; 
    performing the export
    C:\Users\OracleUser>expdp schema_exporter dumpfile=external_table_test.dmp logfi
    le=external_table_test.log directory=user_exports tables=mor_dba.basic_adl
    
    Export: Release 11.2.0.3.0 - Production on Mon Dec 24 08:47:45 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
    Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SCHEMA_EXPORTER"."SYS_EXPORT_TABLE_01":  schema_exporter/******** dump
    file=external_table_test.dmp logfile=external_table_test.log directory=user_expo
    rts tables=mor_dba.basic_adl
    Estimate in progress using BLOCKS method...
    Total estimation using BLOCKS method: 0 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Master table "SCHEMA_EXPORTER"."SYS_EXPORT_TABLE_01" successfully loaded/unloade
    d
    ******************************************************************************
    Dump file set for SCHEMA_EXPORTER.SYS_EXPORT_TABLE_01 is:
      D:\USER_EXPORTS\EXTERNAL_TABLE_TEST.DMP
    Job "SCHEMA_EXPORTER"."SYS_EXPORT_TABLE_01" successfully completed at 08:47:55
    performing the import:
    C:\Users\OracleUser>impdp schema_exporter@MAGNOLIA_ORACLESRV DIRECTORY=USER_EXPO
    RTS DUMPFILE=external_table_test.dmp LOGFILE=external_table_test_imp.log CONTENT
    =ALL TRANSFORM=oid:n:type
    
    Import: Release 11.2.0.3.0 - Production on Mon Dec 24 08:55:54 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    Password:
    
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
    Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SCHEMA_EXPORTER"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    
    Starting "SCHEMA_EXPORTER"."SYS_IMPORT_FULL_01":  schema_exporter/********@MAGNO
    LIA_ORACLESRV DIRECTORY=USER_EXPORTS DUMPFILE=external_table_test.dmp LOGFILE=ex
    ternal_table_test_imp.log CONTENT=ALL TRANSFORM=oid:n:type
    Processing object type TABLE_EXPORT/TABLE/TABLE
    ORA-39083: Object type TABLE:"MOR_DBA"."BASIC_ADL" failed to create with error:
    ORA-06564: object NON_EXISTING_DIR_IN_OTHER_DB does not exist
    Failing sql is:
    CREATE TABLE "MOR_DBA"."BASIC_ADL" ("ADMIN_LOG_DATE" DATE, "ADMIN_LOG_HOST" VARC
    HAR2(4000 BYTE), "ADMIN_LOG_APP" VARCHAR2(4000 BYTE), "ADMIN_LOG_MODULE" NUMBER(
    10,0), "ADMIN_LOG_TYPE" NUMBER(10,0), "ADMIN_LOG_INFO" VARCHAR2(4000 BYTE), "USE
    R_ID" NUMBER(10,0), "ADMIN_LOG_KEY_VALUES" VARCHAR2(4000 BYTE), "LRE_ID" NUMBER(
    10,0), "SERVER_ID" NUMBER(10,0), "
    Job "SCHEMA_EXPORTER"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 08:56:01
    {code}
    
    ORA-39083: Object type TABLE:"MOR_DBA"."BASIC_ADL" failed to create with error:
    ORA-06564: object NON_EXISTING_DIR_IN_OTHER_DB does not exist
    this is my problem...
    
    3. The operating system name and version.
    Both windows 2008 R2 and RHEL6 (doesn't really matter, really).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 4. Re: Import of external table fail because directory object doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Is the directory object NON_EXISTING_DIR_IN_OTHER_DB defined in the target database ?

    HTH
    Srini
  • 5. Re: Import of external table fail because directory object doesn't exist
    Pyrocks Explorer
    Currently Being Moderated
    Hi,

    Thanks for trying to help.
    Please read my original post again.

    We are creating directory objects dynamically.
    Some of the directory objects (most, actually) do not exist in the target database (as can be understood from the example name I have provided NON_EXISTING_DIR_IN_OTHER_DB).
    since I have many external tables each defined with its own dynamically created directory object - I'm looking for a way around this - a way for the import not to fail on these tables, and something that can be done automatically. I'm looking to avoid creating all dynamically created directory objects in the target db before the import, as there can be tens of thousands of such directory objects.

    Thanks.
  • 6. Re: Import of external table fail because directory object doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Import expects the directory objects to exist on the target. One option could be to just export all of directory objects first using a metadata filter ( http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1009903 ), then importing these directory objects into the target, then doing the export/import of needed tables. I have not tried this personally.

    HTH
    Srini
  • 7. Re: Import of external table fail because directory object doesn't exist
    Pyrocks Explorer
    Currently Being Moderated
    Thanks, but Object Paths:
    DATABASE_EXPORT/DIRECTORY/DIRECTORY
    DIRECTORY
    DIRECTORY/DIRECTORY

    seems to exist only in database (full) mode, while we do schema exports and imports.
    I was hoping for a remap or transform option. I want to avoid creating all the directory objects in the target DB, because they are not relevant for it.

    I guess there's no such option and we are left with the manual solution then?
  • 8. Re: Import of external table fail because directory object doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    If these external tables need to be imported into the target, then I do not believe you have any options currently. If they are not needed on the target, then you can use a filter to prevent them from being exported

    HTH
    Srini
  • 9. Re: Import of external table fail because directory object doesn't exist
    Pyrocks Explorer
    Currently Being Moderated
    They are indeed needed.
    so manual solution it is.

    I wish they would add that option - it would make my life so much easier...
    Thanks for the inputs.
  • 10. Re: Import of external table fail because directory object doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    You could use the FULL export option for just the directory objects, then perform necessary schema level exports/imports - this will remove the need for a manual solution

    Srini
  • 11. Re: Import of external table fail because directory object doesn't exist
    Pyrocks Explorer
    Currently Being Moderated
    that is a manual process for me :|
    we happen to do these kind of imports on a regular basis and this is just one more time consuming step.
    thanks anyway.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points