ckfreesp.sql

REM *************************************************************************** 
REM 
REM        TITLE:
REM        AUTHOR:
REM        DESCRIPTION:
REM
REM
REM
REM        USAGE W/ PARAMETERS:
REM        
REM        TABLES USED:
REM
REM  MODIFICATION ALLOWED (Y/N):	
REM  MODIFICATION HISTORY:
REM  WHO          WHEN         WHAT
REM  --------------------------------------------------------------------------
REM               MM/DD/YY     INITIAL CREATION
REM
REM ***************************************************************************
REM ******************************************************
REM * ROUTINE:          ckfreesp 
REM * DESCRIPTION:      Script obtains information on 
REM *                   contiguous space from 
REM *			dba_free_space;
REM *
REM * AUTHOR:           John Burrows
REM *
REM *****************************************************

set linesize 140
set pagesize 40

set echo off;
set verify off;
set feedback on;

COLUMN tablespace_name  HEADING "TBL SPACE" FORMAT A12
COLUMN bytes 		HEADING "BYTES"     FORMAT 999999999999
COLUMN blocks 		HEADING "BLOCKS"    FORMAT 999999999
COLUMN file_id      	HEADING "FILE ID"   FORMAT 999999
COLUMN block_id      	HEADING "BLOCK ID"  FORMAT 999999
COLUMN RTRIM(a.file_name) HEADING "FILE NM" FORMAT A40 

PROMPT
PROMPT **** CHECK CONTIGUOUS FREE SPACE  (DBA_FREE_SPACE) ****
PROMPT
ACCEPT tblsp_nm  CHAR PROMPT 'Enter desired TABLESPACE: ';


SELECT    b.tablespace_name,
          RTRIM(a.file_name),
          b.file_id,
          b.bytes,
          b.blocks, 
          b.block_id
  FROM    sys.DBA_DATA_FILES a,
          sys.DBA_FREE_SPACE b
  WHERE   b.file_id = a.file_id
    AND   b.tablespace_name LIKE UPPER('&&tblsp_nm') 
    ORDER BY b.tablespace_name, b.file_id 
; 
 
undefine tblsp_nm;