Trace a specific ORA- error

How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.

For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows

This time, the alert file is not very helpful.

To determine the root cause, event 1422 can be set as follows:

connect /as sysdba 
alter system set events '1422 trace name ERRORSTACK level 3'; 

[…reproduce the issue…]

The event can be turned off again using:

connect /as sysdba

alter system set events '1422 trace name ERRORSTACK off';

In the event 1422 trace file, below sql query was causing error.

*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)

Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;

In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.

The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.

 
Author: Vincent Fenoll, Oracle DBA Montreal

Generate and format trigger DDL

How to format DBMS_METADATA.GET_DDL?

To get a formatted definition of your DDLs, with all the semicolons and avoid lines break (Wrapped lines).
Here is an example to retrieve the DDL of a triggers using the DBMS_METADATA package.

For Oracle >=10:

COLUMN Text FORMAT a2480 WORD_WRAPPED

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

REM Without use of spool, remove "REM"
REM set term on

REM With use of spool (no display on stdout)
set term off


BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/


spool ddl_schema_trigger.sql
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name', 'schema_name') Text FROM SYS.DUAL;
spool off

set feed on echo on head on pages 14 term on

REM *****************************************
REM New script ======> ddl_schema_trigger.sql
REM *****************************************

Other useful options you can use:

exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘STORAGE’,false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘SEGMENT_ATTRIBUTES’,false);

The generate all the DDL for a schema it’s here.

Dynamic Grants mgmt

 
-- ############################################################
-- # Creator: Yann Cousin
-- # Created: 2011/04/22
-- # Name: dynamic Grants mgmt
-- ############################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ############################################################
-- #
-- # How to create a read only user and dynamicaly grant
-- # privileges to this new user:
-- # - List all existing tables of USER1 and grant select on it
-- # to USER2
-- # - Create a trigger used to grant select on each new table
-- # to USER2
-- #
-- ############################################################


-- First, we give static grants to the new user USER2
select 'grant select on ' || owner ||  '.' || table_name || ' to USER2;' from dba_tables where owner='USER1';


-- Execute the generated grants 


-- Connect to database with USER1 and create the trigger 

CREATE OR REPLACE TRIGGER Grant_Select_On_Schema
  after CREATE ON schema
  declare
    instr varchar2(255);
    exec_instr number;
  begin
      IF ( ora_dict_obj_type = 'TABLE' )
      then
      instr := 'execute immediate "grant select on ' ||ora_dict_obj_name ||' to USER2";';
      dbms_job.submit( exec_instr, replace(instr,'"','''') );
      end IF;
  end;

Email Sys_context trigger

 
-- ############################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/04/19
-- # Name: Email Sys_context trigger 
-- ############################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ############################################################
-- #
-- # Send an email in a trigger (here after the deletion of a 
-- # row in a table)and display informations about 
-- # user sys context (e.g who deleted this row)
-- # 
-- ############################################################

-- Pe-requisites
Connected like SYS: 
@?/rdbms/admin/utlsmtp.sql 
@?/rdbms/admin/prvtsmtp.plb 
@?/rdbms/admin/utlmail.sql 
@?/rdbms/admin/prvtmail.plb 
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;


CREATE OR REPLACE TRIGGER TRG_emp 
AFTER DELETE ON emp REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW 
begin 
        EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1'''; 
          UTL_MAIL.send(sender => 'vincent@oraclescripts.net', 
                 recipients => 'cyrille@oraclescripts.net', 
                        subject => 'Deletion of an employee', 
                        message => 'An employee has been deleted'      ||  utl_tcp.CRLF ||  utl_tcp.CRLF || 
                '   Informations: ' ||  utl_tcp.CRLF || 
                '       Employee name: ' || :old.EMP_NAME  ||  utl_tcp.CRLF || 
                '       Country  : ' || :old.COUNTRY    ||  utl_tcp.CRLF || utl_tcp.CRLF || 
                '   Technical Informations: ' ||  utl_tcp.CRLF || 
                '       DB Name: ' || SYS_CONTEXT('USERENV','DB_NAME')   ||  utl_tcp.CRLF || 
                '       OS USER: ' || SYS_CONTEXT('USERENV','OS_USER')      ||  utl_tcp.CRLF || 
                '       Session ID: ' || SYS_CONTEXT('USERENV','SESSIONID')     ||  utl_tcp.CRLF || 
                '       Session User: ' || SYS_CONTEXT('USERENV','SESSION_USER')    ||  utl_tcp.CRLF || 
                '       IP Address: ' ||  SYS_CONTEXT('USERENV','IP_ADDRESS')    ||  utl_tcp.CRLF || 
                '       Terminal: ' || SYS_CONTEXT('USERENV','TERMINAL')      ||  utl_tcp.CRLF || 
                '       Is DBA: ' || SYS_CONTEXT('USERENV','ISDBA') , 
                      mime_type => 'text; charset=us-ascii'); 
end; 

Start stop service triggers

-- ################################################
-- # Creator: Cyrille MODIANO
-- # Created: 2010/08/20
-- # Name: Start_stop_service_triggers.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # This script creates a trigger used to 
-- # run a scheduler job which call an external shell 
-- # script or executable.
-- # Useful to run a pre command before starting 
-- # and shutdown the DB
-- # Depending of your program it can be  mandatory
-- # to START THE LISTENER BEFORE THE DATABASE
-- ################################################

begin
DBMS_SCHEDULER.create_program (
   program_name => 'START_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');
   
dbms_scheduler.create_job  
  (job_name => 'START_SERVICE',  
   program_name=> 'START_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program START_SCRIPT');
end;
/

begin
DBMS_SCHEDULER.create_program (
   program_name => 'STOP_SCRIPT',
   program_type => 'EXECUTABLE',
   program_action => 'script_path',
   number_of_arguments => 0,
   enabled => TRUE,
   comments => 'COMMENTS ABOUT THE SCRIPT ROLE');

dbms_scheduler.create_job  
  (job_name => 'STOP_SERVICE',  
   program_name=> 'STOP_SCRIPT',  
   enabled=>true,  
   auto_drop=>false,  
   comments=>'Job used to run the program STOP_SCRIPT');
end;
/

CREATE OR REPLACE TRIGGER START_SCRIPT AFTER STARTUP ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('START_SERVICE');
END;
/

CREATE OR REPLACE TRIGGER STOP_SCRIPT BEFORE SHUTDOWN ON DATABASE
BEGIN
dbms_scheduler.RUN_JOB('STOP_SERVICE');
END;
/