Category Archives: Scripts

This page contains all scripts of dba-scripts.com, either performance tuning, diagnosis scripts or administration scripts.

SQLcl, a revolution for SQL*Plus users

What is SQLcl ?

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.

Continue reading SQLcl, a revolution for SQL*Plus users

Top 5 wait events from v$active_session_history

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.

Top 5 wait events from v$active_session_history

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.

Datafile space reclaimable report.

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:

  • Datafile Size
  • Datafile HWM
  • Percentage of space reclaimable
  • Command to resize the datafile
  • Total space reclaimable in your datafiles
  • Percentage of space reclaimable in your datafiles

Find space reclaimable in the datafiles

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;
/