Statspack top queries script by elspased time

Hi all,

I was engaged on a report request that I needed to gather to get the TOP SQL by elapsed time and using Statspack. I got those and than I was asked to it on the following week and on the following, and you may see when this is going. So I created a script which would give is a report and I would not have to do it manually ever again 🙂

Usage: [-h ] [ -r 

If no parameters are used 120 minutes and 7 days will be used as default for History, 5 minutes for current running
-r = Set the time in minutes for the current running SQLs
-o = Specifies the minutes to be used for the long running SQLs History
-d = set the time period wanted for the execution history, default is 7 days
-h = Shows this help message

Might still have some bugs but it well enough to share here 🙂

You have some parameters that you need to change at the top, to suite your environment and of course have Statspack working and change the sqlplus connection line

Some tweaks are required as no 2 environments are never 100% alike. But still forth the work.

# Creates a report, using statspack, of the long running sqls from database
# History:
# Feb-21-2018 - Elisson Almeida - Created.
### Environment setup
### Global variables
DATE=$(date +%Y%m%d_%H%M%S)
#tns-admin if needed otherwise comment out
echo "Usage: [-h ] [ -r 

Hope it helps!

Elisson Almeida

Script to Setup ADRCI Policies

Hi all!
So, today just sharing some useful scripts to configure, set and check on databases and users.

This is specially useful for environments with several databases under different users, considering a possible server consolidation strategy.

# Script to Check on Current Configuration:

[root@greporasrv ~]# cat
su - $1 -c 'export ORAENV_ASK=NO ; ORACLE_SID=$2 ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; show control;" ; done'

# Script to Set New Configuration
On this example: (SHORTP_POLICY = 168, LONGP_POLICY = 720).

[root@greporasrv ~]# cat
export SID=$2
su - $ORAUSER -c 'export ORAENV_ASK=NO ; ORACLE_SID='$SID' ; . oraenv ; for f in $(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "clients") ; do adrci exec="set home $f; set control \(SHORTP_POLICY = 168, LONGP_POLICY = 720\);" ; done'

# To run them informing OSUSER and SID:


# Master one: Script to set for all DBs/Users:

[root@greporasrv ~]# cat
for h in $(grep -v "^#" /etc/oratab | awk 'BEGIN { FS=":";} {if (NF) print $2}' | sort -u)
ORAOWN=`ls -ld $h | cut -d " " -f 3`
# validate user?
# get a SID to use for this home
SID=`grep $h /etc/oratab | grep -v '^\*:' | cut -d ":" -f 1 | tail -1`

# Generating the code to check ADRCI settings on this OH

# Generating the code to change ADRCI settings on this OH

Nice, right?
Hope it helps. Cheers!

Oracle: Easily Decoding ROWID

Hi all,
Recently I needed to decode the rowid so I could find some information about it, I found this bit of code that I thought useful:

v_rid VARCHAR2(20) ;
v_type NUMBER;
v_obj NUMBER;
v_rfno NUMBER;
v_bno NUMBER;
v_rno NUMBER;
dbms_output.put_line('Row_ID = "'||v_rid||'"');
dbms_rowid.rowid_info(CHARTOROWID(v_rid), v_type, v_obj, v_rfno, v_bno, v_rno);
IF v_type = 0 THEN
dbms_output.put_line('RowID Type -> Restricted');
ELSE dbms_output.put_line('RowID Type -> Extended');
dbms_output.put_line('Object ID = "'||v_obj||'"');
dbms_output.put_line('Relative File Number = "'||v_rfno||'"');
dbms_output.put_line('Block Number = "'||v_bno||'"');
dbms_output.put_line('Row Number = "'||v_rno||'"');

Note that I have hard-coded the rowid but it is relatively easy to either edit this or indeed to incorporate this into a procedure.

Here’s the sample output

RowID Type -> Extended
Object ID = "18"
Relative File Number = "1"
Block Number = "241"
Row Number = "27"

Hope this helps!

Shellscript & Oracle: Run Script for a List of Databases

Hey all!
Quick tip/script for today: How to run same script for a list of databases quickly?

In my case I have same password/user in all databases, but in case you haven’t you can make a similar awk command to retrieve users and passwords from a file.

The list:

$: cat /tmp/dbs.cfg

The script:

for DBSID in ${*-$(awk -F: '!/^#/ {print $1}' /tmp/dbs.cfg}
    print "
        connect user/password@${DBSID}
        exit " |
    sqlplus /nolog >> /tmp/output_test.log

Hope it helps!

Purge Recycle Bin Older than…

Hello all!

Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?

Please don’t:

DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;

A good way to perform that is to execute output from:

# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' 
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' and 

There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)

And here you can see an awesome procedure in case you want to automate that in a job or similar:

Hope it help you!

Oracle Database 9i: Where is my SQL_ID? Which SQL is in lock?

Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?

In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.

Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?

Here it goes:

If session status is ACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS

If session status is INACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS

You’re welcome! 😉

See you next week!