Import: Release 11.2.0.3.0 - Production on Mon Aug 25 11:57:56 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORA-39099: cannot create index for "process_order, duplicate" on master table "SCOTT"."SYS_IMPORT_TABLE_01"

ORA-01422: exact fetch returns more than requested number of rows

Reason:

We had a trigger and the import job failed for the trigger.

CREATE OR REPLACE TRIGGER AUDIT_DDL_LOG_TRG

AFTER DDL

ON SCOTT.SCHEMA

 DECLARE

 oper  VARCHAR2(30);

 l_eventlogid number;

 l_sqlText    ORA_NAME_LIST_T;

 l_sqlTextTot clob;

 l_programname varchar2(100);

 BEGIN

    select PROGRAM into l_programname from v$session

    where audsid=sys_context('USERENV','SESSIONID');

    select audit_ddl_log_seq.nextval into l_eventlogid from dual;

     FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP

 l_sqlTextTot := l_sqlTextTot || l_sqlText(l);

            END LOOP;

         INSERT INTO audit_ddl_log (EVENTLOGID,EVENTDATE,OBJECTOWNER,OBJECTNAME,OBJECTTYPE,EVENTNAME,SQLTEXT,OSUSER,DBUSER,HOST,PROGRAMNAME)

        values

             (

              l_eventlogid,

              sysdate,

              ora_dict_obj_owner,

              ora_dict_obj_name,

              ora_dict_obj_type,

              ora_sysevent,

              l_sqlTextTot,

              sys_context('USERENV','OS_USER'),

              sys_context('USERENV','SESSION_USER'),

              sys_context('USERENV','HOST'),

              l_programname

              );

END audit_ddl_trigger;

/

It’s failed because the trigger used  “Select PROGRAM into l_programname from v$session where audsid=sys_context('USERENV','SESSIONID');” . This query returning more than one row during import time which causes the import fails.  SID is not a unique identifier of a session. Oracle can reused a SID before PMON clean up the dead session. SID+SERIAL# together can be a unique identifier for a session. SID 0 is used for many background processes.

Solution:

I change the sql statement by following sql and it worked fine.

select PROGRAM into l_programname from v$session where audsid=sys_context('USERENV','SESSIONID') and username not in  ('SYS','SYSTEM','DBSNMP') and type != 'BACKGROUND';