Category Archives: Triggers

Trace a specific ORA- error

2017/01/27 Vincent Leave a comment

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

DDL, Triggers

Generate and format trigger DDL

2017/01/27 Vincent Leave a comment

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 18c / 12c / 11g / 10g:

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.

See also:
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package.function dbms_metadata.get_ddl user can be used to generate the user creation DDL.

Author: Vincent Fenoll

Compatibility: Oracle 18c, 12g, 11g

Triggers

Dynamic Grants mgmt

2011/04/26 Vincent Leave a comment

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

Triggers

Email Sys_context trigger

2011/04/19 Vincent Leave a comment

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

Triggers

Start stop service triggers

2011/01/26 Cyrille

-- ################################################
-- # 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;
/

Oracle Database SQL scripts

Category Archives: Triggers

Trace a specific ORA- error

Generate and format trigger DDL

Dynamic Grants mgmt

Email Sys_context trigger

Start stop service triggers

example DBA 18c 12c 11g Unix