The default maintenance window is not a good fit for every database, by default the maintenance window start at 10 PM and run for 4 hours during the week and start at 6 AM and last for 20 hours during the week-end. A different window exists for each day of the week and all the windows are grouped inside the MAINTENANCE_WINDOW_GROUP. It is possible to alter each window independently or to create a single window for all days of the week.

The following maintenance tasks are performed during the maintenance window:

• Optimizer statistics (OPTIMIZER_STATS)
• Segment advisor (SEGMENT_ADVISOR)
• SQL Tuning (SQL_TUNE_ADVISOR)
• Health Monitor (HEALTH_MONITOR)

And the default window group named MAINTENANCE_WINDOW_GROUP is associated with a DEFAULT_MAINTENANCE_PLAN which has the following configuration:

In this plan 75% of the resources are affected to sessions created by SYS or SYSTEM user. If these resources are not used by the SYS_GROUP then they are transfered to the other groups. As you can see there is also a restriction for the automatic maintenance tasks, they cannot use more that 90% of the resources.

here is what you is configured by default, independently of the management pack configured and the edition you use. This configuration is from a standard edition database with control_management_pack_access set to NONE.

SQL> select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME          WINDOW_NEXT_TIME                                   WINDO AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE HEALTH_M
-------------------- -------------------------------------------------- ----- -------- -------- -------- -------- --------
SUNDAY_WINDOW        27-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
SATURDAY_WINDOW      26-MAR-16 06.00.00.000000 AM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
FRIDAY_WINDOW        25-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
THURSDAY_WINDOW      24-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
WEDNESDAY_WINDOW     23-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
TUESDAY_WINDOW       29-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED
MONDAY_WINDOW        28-MAR-16 10.00.00.000000 PM EST5EDT               FALSE ENABLED  ENABLED  ENABLED  ENABLED  DISABLED

Continue reading Database maintenance tasks →

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 →

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