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'; |