Home → oracle → Archive by category "DBA util"

Category Archives: DBA util

/ DBA util / oracle
# 1180
OCT, 18 2010

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

/ DBA util / oracle
# 1256
OCT, 16 2010

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,

/ DBA util / oracle
# 1008
OCT, 5 2010

change database id of database

SQL script to change database id of database.

shutdown immediate
startup mount
host nid target=/
disconnect
connect / as sysdba
startup mount
alter database open resetlogs;

/ DBA util
# 7093
JAN, 18 2008

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

/ DBA util
# 2552
JUN, 27 2006

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

remidian