Home » RDBMS Server » Server Utilities » DATAPUMP ORA-39152 and ORA-31696 while importing
DATAPUMP ORA-39152 and ORA-31696 while importing [message #225831] Wed, 21 March 2007 12:23 Go to next message
sebastianR
Messages: 33
Registered: August 2005
Member
Hi there!

When I am importing a table on oracle 10g, with the import parameter "TABLE_EXISTS_ACTION" = APPEND, the import fails with the following info provided in the import log:

Master-Tabelle "SCHEMA_1"."TEST" erfolgreich geladen/entladen
"SCHEMA"."TEST": wird gestartet
Objekttyp TABLE_EXPORT/TABLE/TABLE wird verarbeitet
ORA-39152: Tabelle "SCHEMA_1"."DATAPUMP_TEST" ist vorhanden. Daten werden an bestehende Tabelle angehängt, alle abhängigen Metadaten werden jedoch wegen table_exists_action von Append übersprungen.
Objekttyp TABLE_EXPORT/TABLE/TABLE_DATA wird verarbeitet
ORA-31696: TABLE_DATA:"SCHEMA_1"."DATAPUMP_TEST" konnten mit von Client angegebener Methode AUTOMATIC nicht exportiert/importiert werden
Objekttyp TABLE_EXPORT/TABLE/INDEX/INDEX wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT wird verarbeitet
Objekttyp TABLE_EXPORT/TABLE/TRIGGER wird verarbeitet
Job "SCHEMA_1"."TEST" mit 2 Fehler(n) um 20:34:34 abgeschlossen


My code which starts the import looks like this:
declare
l_handle    NUMBER;
begin
    l_handle := DBMS_DATAPUMP.open(
        operation => 'IMPORT',
        job_mode => 'TABLE',
        job_name => 'TEST');
    DBMS_DATAPUMP.add_file(
        handle => l_handle,
        filename => 'TEST.dmp',
        directory => 'TEST'
        );       
    DBMS_DATAPUMP.add_file(
        handle => l_handle,
        filename => 'TESTLOG_IMP.log',
        directory => 'TEST',
        filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
        );
    DBMS_DATAPUMP.METADATA_REMAP
    (handle => l_handle,
    name => 'REMAP_SCHEMA',
    old_value => 'SCHEMA_1',
    value => 'SCHEMA_2');    
         DBMS_DATAPUMP.set_parameter(
        handle => l_handle,
        name => 'TABLE_EXISTS_ACTION',
        value => 'APPEND');
    DBMS_DATAPUMP.start_job(l_handle);
    DBMS_DATAPUMP.detach(l_handle);
end;    


I would be really happy if anyone could explain the two ORA's to me in some detail, since I don't really know what's wrong.

english:
ORA-39152: Table "SCHEMA_1"."DATAPUMP_TEST" exists. data will be appended to existing table, all depending Metadata will be skipped since table_exists_action is Append. (I cannot find a single entry in google about this ORA!)

english:
ORA-31696: unable to export/import "SCHEMA_1"."DATAPUMP_TEST" using client specified AUTOMATIC method

Additional Info: When setting the TABLE_EXISTS_ACTION = REPLACE it works, so it definately has something to do with the APPEND option.

Thank you for your time, any response is appreciated!

SebastianR
Re: DATAPUMP ORA-39152 and ORA-31696 while importing [message #225834 is a reply to message #225831] Wed, 21 March 2007 12:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For now, ignore about ORA-39152.
For ORA-31696
Is that a long/lob column.?

[Updated on: Wed, 21 March 2007 12:34]

Report message to a moderator

Re: DATAPUMP ORA-39152 and ORA-31696 while importing [message #226095 is a reply to message #225831] Thu, 22 March 2007 09:12 Go to previous messageGo to next message
sebastianR
Messages: 33
Registered: August 2005
Member
There is one LONG column and one CLOB column, besides that, there is a DATE, VARCHAR2, NUMBER column.

Do you think it is something about the column type (LONG)?

greets,

sebastianR
Re: DATAPUMP ORA-39152 and ORA-31696 while importing [message #226180 is a reply to message #225831] Thu, 22 March 2007 14:05 Go to previous message
sebastianR
Messages: 33
Registered: August 2005
Member
Sorry, wrong thread!


(as I read this http://orafaq.com/node/74, it seems the export of sequences is NOT done, when using EXPORT mode = "TABLE", this is the same for views (any maybe other object types too))

[Updated on: Thu, 22 March 2007 14:08]

Report message to a moderator

Previous Topic: IMP-00003 - ORA-06550 - PLS-00103
Next Topic: DATAPUMP example, appending rows to existing table (parent-child relation)
Goto Forum:
  


Current Time: Wed Jul 03 09:46:00 CDT 2024