Export data to excel

Administration

who never needed to export data to excel from sqlplus ? everybody had one day needed to do that.
Sqlplus support the HTML markup which provides an excellent result once opened in excel.
With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table header.
Here is a small exemple:

spool myxlsfile.xls
SET MARKUP HTML ON ENTMAP ON PREFORMAT OFF ;
select 	to_char(end_time,'hh24:mi:ss') as sample_time,
		value
from 	v$sysmetric_history
where 	end_time between sysdate - interval '1' hour and sysdate
and 	group_id = 2
and 	metric_name = 'Host CPU Utilization (%)'
order by metric_name, end_time;
spool off

Continue reading Export data to excel →

SQL Activity for the last hour

Oracle Active Session History (ASH)

This script can be used to show the top 10 SQL activity for the last hour.
It uses the v$active_session_history view to search top SQL by resource consumption.

Top 10 SQL Activity

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1))   desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id 
order by trunc(sample_time,'MI') desc

Here is the result you can obtain:

SQL activity

and the active sessions history graph for the same period:

instance activity last hour