/*
 * Title  : sho_data_file.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Datafile info for the given tablespace.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on

set linesize 120
set pagesize 0

set heading on
set headsep on
set underline on
set colsep " "

set verify off
set escape off
set embedded on

column dummy	noprint
column id	format 9,999,999	heading "File ID"
column name	format a60		heading "File Name"
column total	format 99,999,999,999	heading "Total"
column occupied	format 99,999,999,999	heading "KBytes|Occupied"
column auto	format a7		heading "Auto?"

compute sum of total	on report
compute sum of occupied on report

break on report

accept p_ts_name char prompt "Enter Tablespace Name "

spool &&rpt_dir/sho_data_file.rpt

select 
	file_id							id,
	file_name						name,
	decode(autoextensible,'YES',maxbytes/1024,bytes/1024)	total,
	bytes/1024						occupied,
	autoextensible						auto
from 
	dba_data_files	
where 
	tablespace_name=upper('&&p_ts_name')
order by
	1;

spool off;

set embedded off
set escape off
set verify on

set colsep " "
set underline on
set headsep on
set heading on

set pagesize 24
set linesize 80

set serverout off
set feedback on
set echo off
