Category Archives: Administration

You will find here useful scripts for everyday Oracle database administration.

Database maintenance tasks

oracle maintenance

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:

Consumer Group/subplanLevel 1Maximum Utilization Limit
ORA$AUTOTASK5%90
OTHER_GROUPS20%
SYS_GROUP75%

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.

Continue reading Database maintenance tasks

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

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