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

Oracle SQL: Aggregate List – LISTAGG

Know this command?

I think it’s very useful, but not so often I see that in use… More about it:

It was introduced in Oracle 11g Release 2, as an analytic function to make the life easier fot the PLSQL Dev, specially when working to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. If you are using 11g Release 2 you should use this function for string aggregation.

In case WM_CONCAT is in use for this end, be aware that WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). Also, WM_CONCAT has been removed from 12c onward, once it was completely replaced by LISTAGG.

More info and examples can be found here:

Hope you enjoy it. Cheers!

Oracle Pivot and Unpivot Operators

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

Pivot and Unpivot Operators

Pivoting tables are now possible in 11g through PIVOT clause. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. Prior to 11g new functions it was possible to accomplish by using DECODE combined with some aggregate function like SUM.

Also, Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, making possible to replace the hard coded IN clause with a subquery, or the ANY commands.

In same way, the UNPIVOT operator converts column-based data into separate rows. Some important considerations about feature:

  • Column names are required. These can be set to any name not currently in the driving table.
  • The columns to be unpivoted must be named in the IN clause.
  • By default the EXCLUDE NULLS clause is used. To override the default behavior use the INCLUDE NULLS clause.
  • The following query shows the inclusion of the INCLUDE NULLS clause.

For more information and examples:


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!

Identifying the top segments

Hello readers! My name is Bruno Kremer, this is my first post from a series, and I will be talking about how we can identify the top segments of the database.


It’s well known that we can create automated tasks to collect and save the space used/allocated by the database objects, such as saving snapshots of the DBA_SEGMENTS view. But what if this is your first contact with a specific database and you need to identify the top segments, estimate their growth ratio, check the history of space allocated, or even to perform some kind of capacity planning? There are some alternatives to answer these questions, but on this post I will share the starting point. Please feel free to customize the scripts to your own need.

Checking the top sized segments

round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
fetch first &TOP rows only;

Input values: &TOP – limit the number of rows returned.

Filters you might want to use: and s.segment_type in ('&OBJECT_TYPE') – ‘TABLE’, ‘TABLE PARTITION’, ‘INDEX’…

Return example:

Note: the scripts used in this series were tested on databases. Some of these use the “FETCH FIRST” clause to limit the number of rows returned, but if you are using older versions of Oracle Database, you can still use the old fashion like “ROWNUM”.


select * from (
select s.owner, s.segment_name, s.segment_type, round(sum(nvl(s.bytes,0))/1024/1024) size_mb
from dba_segments s
where s.bytes > 1048576 -- higher than 1MB
group by s.owner, s.segment_name, s.segment_type
order by size_mb desc
) where rownum <= &TOP;

Now that you already have an idea regarding the size of the largest database segments, you might want to check the top growing segments… On next publications we will talk about how we can use AWR data dictionary views and some DBMS_SPACE procedures to estimate space usage history and top growing segments.

Guru99: PL/SQL 101 Completely Free!

Hey all!
Just passing by today to share something I found really inetresting, and the best: it’s all free!
Do you know Guru99?

It’s a preety nice repository with lots of free tutorials and guides for PLSQL, SQL and several other languages, plus some other IT-related knowledge, like Big Data, PMP and etc.

It’s not rare to have people asking me “How to Start learning SQL” or saying “All material (including grepora :D) is too much specific, we don’t have good general documentation”.
Even Oracle having a pretty nice documentation on the products and features, general concepts are usually forgotten. Aaaaand, now I have something to link on those cases! 😀

Having a look on Guru99, First I found this guide PL/SQL First Program: Hello World, including detailed steps on:
– How to access SQL* Plus
– Connecting to Database
– How to write a simple program using PL/SQL
– Declaring and usage of variables in program
– Commenting Codes

Then found an entire category PL/SQL Tutorials. Talking to Alex (Editor), seems over 120 hours were spent on it. And they were well spent, the material is really very easy to read and understand. Chapters on this Course are:
1. Introduction to PL/SQL
2. Blocks in PL/SQL
3. PL SQL First Program: Hello World
4. PL/SQL Data Types
5. Complex Data Types in PL/SQL
6. PL/SQL Decision Making Statements
7. Loops in PL/SQL
8. Subprograms: Procedures and Functions in PL/SQL
9. Exception Handling in PL/SQL
10. SQL in PL/SQL
11. Packages in PL/SQL
12. Triggers in PL/SQL
13. Object Types in PL/SQL
14. Dynamic SQL in PL/SQL
15. Nested Blocks in Pl/Sql
16. PL/SQL Identifiers

Continue reading

Purging a SQL Cursor in 10g

Hello all,
Having issues to purge SQL Cursor in a 10g database?

DBMS_SHARED_POOL.PURGE is available from on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.


sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';


sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';


sys@PRODB10> alter session set events '5614566 trace name context forever';

Session altered.

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';


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!