/********************************************************************** * File: prevent1555.sql * Type: SQL*Plus script * Author: Tim Gorman (Evergreen Database Technologies, Inc.) * Date: 04-Dec-02 * * Description: * * Modifications: *********************************************************************/ whenever oserror exit failure whenever sqlerror exit failure set echo on feedback on timing on spool prevent1555_ddl show user show release whenever sqlerror continue drop table prevent1555_dummy_table; whenever sqlerror exit failure create table prevent1555_dummy_table ( rbs_name varchar2(30) not null, username varchar2(30) not null, timestamp date not null ); create or replace package prevent1555 as procedure display_rbs_names; procedure set_roadblock(in_sid in number default NULL, in_serial_nbr in number default NULL, in_rbs_name in varchar2 default NULL, in_mins in integer); end prevent1555; / create or replace package body prevent1555 as procedure display_rbs_names is cursor get_rbs_names is select rn.name from sys.dba_rollback_segs rs, sys.v_$rollname rn where rs.segment_name <> 'SYSTEM' and rs.status = 'ONLINE' and rn.usn = rs.segment_id; begin -- for rbs in get_rbs_names loop -- dbms_output.put_line(rbs.name); -- end loop; -- end display_rbs_names; -- procedure set_roadblock(in_sid in number default NULL, in_serial_nbr in number default NULL, in_rbs_name in varchar2 default NULL, in_mins in integer) is -- v_dummy varchar2(1); v_rbs_name varchar2(30); v_rowid rowid; v_errcontext varchar2(100); v_errmsg varchar2(1000); v_save_module varchar2(48); v_save_action varchar2(32); e_already_in_use exception; e_no_active_txn exception; e_no_active_session exception; e_no_valid_rbs exception; e_invalid_parms exception; e_already_locked exception; pragma exception_init(e_already_locked, -54); -- cursor get_obsolete_dummy_rows is select rowid from prevent1555_dummy_table where timestamp < (sysdate - 3); -- begin -- /* * ...save previous values in MODULE and ACTION in V$SESSION row for * this current database session... * * ...then, reset to indicate that this stored procedure is running... */ dbms_application_info.read_module(v_save_module, v_save_action); dbms_application_info.set_module('PREVENT1555', 'begin'); -- /* * ...if SID/SERIAL# was supplied as parameters, then this procedure * is intended to detect a specific transaction within the specified * session and set up a "rollback roadblock" within that rollback * segment (using a dummy transaction)... * * ...otherwise, if a USN was supplied as a parameter, then this * procedure is being asked merely to set up a "rollback roadblock" * on the specified rollback segment (using a dummy transaction)... * * ...if no parameters were supplied, then it is an error... */ if in_sid is not null and in_serial_nbr is not null then -- /* * ...if a SID/SERIAL# was specified, then find the * session, it's transaction, and it's associated * rollback segment... */ v_errcontext := 'query V$SESSION/TRANSACTION/ROLLNAME'; dbms_application_info.set_action(v_errcontext); begin select n.name into v_rbs_name from sys.v_$session s, sys.v_$transaction t, sys.v_$rollname n where s.sid = in_sid and s.serial# = in_serial_nbr and t.addr (+) = s.taddr and n.usn (+) = t.xidusn; -- if sql%notfound then raise no_data_found; end if; -- exception when no_data_found then raise e_no_active_session; end; -- if v_rbs_name is null then raise e_no_active_txn; end if; -- elsif in_rbs_name is not null then -- /* * ...if a specific rollback segment (i.e. USN = "undo segment #") * was specified, then verify that it is a valid USN... */ v_errcontext := 'query V$ROLLNAME'; dbms_application_info.set_action(v_errcontext); begin select name into v_rbs_name from sys.v_$rollname where usn > 0 and name = upper(in_rbs_name); exception when no_data_found then raise e_no_valid_rbs; end; -- else -- raise e_invalid_parms; -- end if; -- /* * ...first, clean up any rows in the "dummy" table older than 3 days. If the * row is locked for any reason, then skip over it so as not to get hung up... */ v_errcontext := 'open/fetch get_obsolete_dummy_rows'; dbms_application_info.set_action(v_errcontext); for x in get_obsolete_dummy_rows loop -- begin -- v_errcontext := 'select PREVENT1555_DUMMY_TABLE for update nowait #1'; select 'x' into v_dummy from prevent1555_dummy_table where rowid = x.rowid for update nowait; -- v_errcontext := 'delete PREVENT1555_DUMMY_TABLE #1'; delete from prevent1555_dummy_table where rowid = x.rowid; -- exception when e_already_locked then null; end; -- v_errcontext := 'fetch/close get_obsolete_dummy_rows'; -- end loop; -- /* * ...insert a new row into the "dummy" table so that we can run a "dummy" * transaction against it later on... */ v_errcontext := 'insert into PREVENT1555_DUMMY_TABLE'; dbms_application_info.set_action(v_errcontext); insert into prevent1555_dummy_table (rbs_name, timestamp, username) values (v_rbs_name, sysdate, user) returning rowid into v_rowid; -- /* * ...commit the DELETEs and INSERTs above before continuing... */ v_errcontext := 'first COMMIT'; commit; -- /* * ...use the DBMS_TRANSACTION package to issue the SET TRANSACTION * USE ROLLBACK SEGMENT command... */ v_errcontext := 'dbms_transaction.use_rollback_segment('||v_rbs_name||')'; dbms_application_info.set_action(v_errcontext); dbms_transaction.use_rollback_segment(v_rbs_name); -- /* * ...create a transaction by running SELECT ... FOR UPDATE NOWAIT against * the row just inserted into the "dummy" table... */ begin -- v_errcontext := 'select PREVENT1555_DUMMY_TABLE for update nowait #2'; dbms_application_info.set_action(v_errcontext); select 'x' into v_dummy from prevent1555_dummy_table where rowid = v_rowid for update nowait; -- v_errcontext := 'delete PREVENT1555_DUMMY_TABLE #2'; dbms_application_info.set_action(v_errcontext); -- exception when e_already_locked then raise e_already_in_use; end; -- /* * ...sleep for the requested number of minutes, and then COMMIT to clean up * the dummy transaction... */ v_errcontext := 'dbms_lock.sleep('||(in_mins*60)||')'; dbms_application_info.set_action(v_errcontext); dbms_lock.sleep(in_mins*60); -- v_errcontext := 'final COMMIT'; commit; -- dbms_application_info.set_module(v_save_module, v_save_action); -- exception -- when e_already_in_use then dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20005, 'This procedure already has rollback segment "'||v_rbs_name||'" in use...'); when e_invalid_parms then dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20004, 'Must specify either SID/SERIAL# or USN'); when e_no_active_txn then dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20003, 'No active transaction for this SID/SERIAL#'); when e_no_valid_rbs then dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20002, 'No non-SYSTEM rollback segment with this USN'); when e_no_active_session then dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20001, 'No active database session with this SID/SERIAL#'); when others then v_errmsg := sqlerrm; dbms_application_info.set_module(v_save_module, v_save_action); raise_application_error(-20000, v_errcontext || v_errmsg); -- end set_roadblock; -- end prevent1555; / show errors whenever sqlerror continue drop public synonym prevent1555; whenever sqlerror exit failure REM create public synonym prevent1555 for prevent1555; REM grant execute on prevent1555 to public; exit success