highwater.sql

-------------------------------------------------------------------
--
-- Script:      highwater.sql
-- Description: Query to list high watermarks of data
--              files and the freespace available to
--              reclaim.
-- Usage/Notes: Should be run as a DBA privileged user.
--
-- Copyright:   ABCdba.com 2007
--
-------------------------------------------------------------------
 
COL TABLESPACE_NAME    hea 'Tablespace' for a30
COL FILE_ID            hea 'Id'         for 999
COL FILE_SIZE_IN_BYTES hea 'File Size'  for 999999999999
COL HWM_BYTES          hea 'High Water' for 999999999999
COL SAVING             hea 'Saving'     for 999999999999
 
PROMPT
PROMPT
ACCEPT blksz PROMPT 'Enter database blocksize in bytes : '
PROMPT
PROMPT
PROMPT Preparing report - please wait...
PROMPT
PROMPT
 
SET VERIFY OFF
SET TIMING ON
SET TERMOUT OFF
SET ECHO ON
SPOOL hwm.log
 
CREATE TABLE hwm_max_extents
TABLESPACE users
AS
SELECT
   file_id, MAX( block_id ) maximum
FROM
   dba_extents
GROUP BY
   file_id
/
 
CREATE INDEX xhwm_max_extents_file_id
ON
hwm_max_extents( file_id )
TABLESPACE users
/
 
CREATE INDEX xhwm_max_extents_maximum
ON
hwm_max_extents( maximum )
TABLESPACE users
/
 
CREATE TABLE hwm_data_files
TABLESPACE users
AS
SELECT
   *
FROM
   dba_data_files
/
 
CREATE INDEX xhwm_data_files_file_id
ON
hwm_data_files( file_id )
TABLESPACE users
/
 
CREATE TABLE hwm_extents
TABLESPACE users
AS
SELECT
   *
FROM
   dba_extents
/
 
CREATE INDEX xhwm_extents_file_id
ON
hwm_extents( file_id )
TABLESPACE users
/
 
CREATE INDEX xhwm_extents_block_id
ON
hwm_extents( block_id )
TABLESPACE users
/
 
SET ECHO OFF
SET TERMOUT ON
 
SELECT
   a.tablespace_name,
   a.file_id,
   a.bytes file_size_in_bytes,
   ( c.block_id +( c.blocks - 1 ) ) * &blksz HWM_BYTES,
   a.bytes - ( ( c.block_id + ( c.blocks - 1 ) ) * &blksz) SAVING
FROM
   hwm_data_files a,
   hwm_max_extents b,
   hwm_extents c
WHERE
   a.file_id = b.file_id
AND
   c.file_id = b.file_id
AND
   c.block_id = b.maximum
ORDER BY
   5
/
 
SET TERMOUT OFF
SET ECHO ON
 
DROP TABLE hwm_max_extents
/
 
DROP TABLE hwm_data_files
/
 
DROP TABLE hwm_extents
/
 
SET TIMING OFF
SET VERIFY ON
SET ECHO OFF
SET TERMOUT ON
SPOOL OFF
© copyright 2001-2014 ABCdba.com | all rights reserved