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


SQL Tuning Health Check (SQLHC)

What is SQL Tuning Health Check?

Sql Tuning Health Check
The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs.
It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed.

The script generates an HTML report with information on data collected by the script. The script has no footprint and can be run on any system.

You must connect as SYS or with a user with the DBA role.
The script takes 2 parameters, the first one is about your licensing (Tuning or Diagnostics or None T|D|N), the second is the SQL_ID that needs to be analyzed. Not that if you want to use both the Diagnostic and Tuninbg pack you have to use T as the first parameter.
Continue reading

Space used by objects

Calculate the space used by a single object

This script will help you calculate the size of a single object :

Calculate the space used by a whole schema

If you want the space used by a whole schema, then here is a variation of the first query :