SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It is a lightweight tool (only 11MB) developed by the SQL Developer team fully compatible with Windows and Unix/Linux. You don’t need to install it so it is totally portable.
The tool does not need any Oracle client to be installed, however you need a JRE 7.0 or newer.
This query returns the top 5 wait events for the last hour from the v$active_session_history view.
Be careful, this view is part of the diagnostic pack, you should not query this view if you not licensed for it.
select * from ( select WAIT_CLASS , EVENT, count(sample_time) as EST_SECS_IN_WAIT from v$active_session_history where sample_time between sysdate - interval '1' hour and sysdate group by WAIT_CLASS,EVENT order by count(sample_time) desc ) where rownum <6
This is obviously an approximation, because v$active_session_history contains only 1 second samples, and who knows what happens during each second sample. If you compare the time given by this query with information from v$system_event it will not exactly match but you should be close if you choose a sufficiently long period.
This script will help you find the space reclaimable in your datafiles, it finds the High Water Mark of all your datafiles (the minimum size) and then report the following information:
set pages 1000
set serveroutput on
set lines 500
DECLARE
v_hwm number :=0;
v_current_size number :=0;
v_percent_gain number :=0;
v_total_space_rec number :=0;
v_total_data_size number :=0;
BEGIN
for v_file_info in (select FILE_NAME, FILE_ID, BLOCK_SIZE
from dba_tablespaces tbs, dba_data_files df
where tbs.tablespace_name = df.tablespace_name)
loop
select ceil( (nvl(hwm,1) * v_file_info.block_size)/1024/1024 ) ,
ceil( blocks * v_file_info.block_size/1024/1024) into v_hwm,v_current_size
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.file_id = v_file_info.file_id;
v_total_space_rec := v_total_space_rec +(v_current_size-v_hwm);
v_total_data_size := v_total_data_size +v_current_size;
dbms_output.put_line(v_file_info.file_name || ':');
dbms_output.put_line('Current size: ' || v_current_size || 'M' );
dbms_output.put_line('HWM: ' || v_hwm || 'M' );
dbms_output.put_line('Percentage reclaimable: ' || round((v_current_size-v_hwm)*100/v_current_size,2) || '%');
dbms_output.put_line('Use following command to resize: ALTER DATABASE DATAFILE ''' || v_file_info.file_name || ''' RESIZE ' || v_hwm|| 'M;');
dbms_output.put_line(' ');
dbms_output.put_line(' ');
end loop;
dbms_output.put_line('Total datafiles size reclaimable: ' || v_total_space_rec || 'M');
dbms_output.put_line('Percentage of space reclaimable in the datafiles: ' || round(v_total_space_rec*100/v_total_data_size,2) || '%');
END;
/