ORAENV_ASK

ORAENV_ASK=no …

export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;export ORACLE_SID=********;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql 
export ORAENV_ASK=NO;
export ORACLE_SID=sidje0;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje1;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORACLE_SID=sidje2;. /usr/local/bin/oraenv;sqlplus '/ as sysdba' @batch.sql
export ORAENV_ASK=YES;
Read more →

monitor progress of long running job with dbms_application_info.set_session_longops

Investigating a major performance issue with a PL/SQL loop running for more then 12 hours. Added dbms_application_info.set_session_longops to have entry in v$session_longnops. …

DECLARE

	-- @what: display elapsed msecs
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2389164927483
	n_starttime NUMBER;
	n_endtime   NUMBER;
	n_elapsed   NUMBER;
	
	d_date			DATE   := SYSDATE;

	-- @what: dbms_application_info.set_session_longops
	-- @link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1099233454171#18173358903352
	l_nohint 		NUMBER DEFAULT dbms_application_info.set_session_longops_nohint;
	l_rindex 		NUMBER DEFAULT l_nohint;

  n_slno      NUMBER;
  n_totalwork NUMBER := 847; -- init total work, number of records in loop recordset is 847
  n_sofar     NUMBER := 0;


BEGIN

  FOR i IN
  (
          SELECT  *********.DBKey  FloorDBKey,
                  *********.Value3 StoreVersionID
          FROM    *********,
                  *********,
                  *********
          WHERE   *********.DBParentStoreKey = *********.DBKey
          AND     *********.DBKey = *********.DBParentFloorplanKey
          AND     *********.DBStatus = 1
          AND     *********.DBStatus = 1
  --        
Read more →

Batch SQL statements in parallel (multiple) sessions

So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday. …

I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep. …

Setup The table holding my SQL statements
CREATE TABLE batchsql
 ( batchid              VARCHAR2(64)    NOT NULL
 , sqlid                INTEGER         NOT NULL UNIQUE
 , sqltext              varchar2(4000)  NOT NULL
 , starttime            DATE
 , endtime              DATE
 , sqlcode              INTEGER
 , sqlerrm              VARCHAR2(256)
 , error_stack          VARCHAR2(4000)
 , error_backtrace      VARCHAR2(4000)
 );

would be nicer …

Read more →

dba$log_blocker

Code below will save the information of the ‘blocking session’ in an ORA-00060 situation. …

Since both sessions are equally guilty of the deadlock Oracle picks one session at random to get the ORA-00060, the update will fail for this session and a trace file is written to the user_dump_dest. In this trace file you can find the current SQL statement of the session that got the ORA-00060 and the os pids of the sessions on the server, however there is not as much information as to the session that ’caused the deadlock’ as you might want to see. …

The ‘servererror’ triggers (around since 8i), enable you to write a trigger and storing some usefull information for later analysis. …

Please note the triggers firing on the ‘servererror’ start a separate transaction and commits it after firing the trigger. No need to start an autonomous transaction, since it already is so to say, see Database Application Developer’s Guide – Fundamentals – 9 Coding Triggers – Table 9-3 System Manager Events. …

tables

Create some tables to hold the information I’m interested in. …

[REMI@DB01.REMIDIAN.COM]
SQL> create table log$event
  2     ( event#     number         constraint pk_log$event primary key
  3     , event      varchar2(512)
  4     , eventtime  date
  5     )
  6  /

Table created.

[REMI@DB01.REMIDIAN.COM]
SQL> create table log$session
  2  as
  3 
Read more →