ORA-01422: exact fetch returns more than requested number of rows (Error during Import)

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

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