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