Query To Get Datafile Details for a Given Tablespace

Download(datafilesforts.sql)

REM +======================================================================+
REM                    
REM File Name: datafilesforts.sql
REM 
REM Description:
REM   Query To Get Datafile Details for a Given Tablespace
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @datafilesforts.sql 
REM
REM   This Query Requires Tablespace Name as an Input
REM   
REM +======================================================================+

ACCEPT TS_NAME PROMPT "Enter Tablespace Name: ";

clear columns
set lines 180
set pages 100
col AUTOEXTENSIBLE for a16
col DATA_FILE_NAME for a60 WORD_WRAPPED
SELECT SUBSTR (df.file_name, 1, 60) DATA_FILE_NAME, df.bytes / 1024 / 1024 Size_MB,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_mb,df.AUTOEXTENSIBLE,
df.increment_by * (df.bytes/df.blocks) /1024/1024 "NEXT_EXTENT_MB",
df.MAXBYTES  / 1024 / 1024 MAXSIZE_MB
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
AND df.TABLESPACE_NAME = '&TS_NAME'
GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes,df.AUTOEXTENSIBLE,df.MAXBYTES,df.increment_by,df.blocks
ORDER by df.file_name;
undef TS_NAME