Oracle Minimum Datafile Size Report
This script calculates the minimum size each datafile can be resized to without requiring a reorganization. It uses information from DBA_DATA_FILES, DBA_EXTENTS, and V$PARAMETER to determine the highest used block and estimate how much space can be safely reclaimed.
oraclesqlstorage-tablespacesv1.0.0
0 stars0 downloads22 views0 comments
By OracleDba • Created
Code
(34 lines)12345678910111213141516171819202122232425262728293031323334
-- -----------------------------------------------------------------------------------
-- File Name : http://www.oracle-base.com/dba/monitoring/min_datafile_size.sql
-- Author : DR Timothy S Hall
-- Description : Displays smallest size the datafiles can shrink to without a reorg.
-- Requirements : Access to the V$ and DBA views.
-- Call Syntax : @min_datafile_size
-- Last Modified: 07/09/2007
-- -----------------------------------------------------------------------------------
COLUMN block_size NEW_VALUE v_block_size
SELECT TO_NUMBER(value) AS block_size
FROM v$parameter
WHERE name = 'db_block_size';
COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A50
COLUMN current_bytes FORMAT 999999999999999
COLUMN shrink_by_bytes FORMAT 999999999999999
COLUMN resize_to_bytes FORMAT 999999999999999
SET VERIFY OFF
SET LINESIZE 200
SELECT a.tablespace_name,
a.file_name,
a.bytes AS current_bytes,
a.bytes - b.resize_to AS shrink_by_bytes,
b.resize_to AS resize_to_bytes
FROM dba_data_files a,
(SELECT file_id, MAX((block_id+blocks-1)*&v_block_size) AS resize_to
FROM dba_extents
GROUP by file_id) b
WHERE a.file_id = b.file_id
ORDER BY a.tablespace_name, a.file_name;