Summary
1. list datafiles
2. get type, auroextend, management type etc
-- ---------------------------------------------------------------
-- Script : qtbsfiles.sql
-- Purpose : Returns list of datafiles for a tablespace
-- ---------------------------------------------------------------
rem qtbsfiles.sql
rem
set lines 120
rem
ttitle 'Files By Tablespace'
rem
col fname format a60 heading 'FILE NAME'
rem
select file_name fname
,bytes/(1024*1024)
from dba_data_files
where tablespace_name = upper('&1');
rem
set lines 80
rem
rem end of script
-- ---------------------------------------------------------------
-- Script : qtbsmngt.sql
-- Purpose : Returns list of datafiles, tablespace type etc for a tablespace
-- ---------------------------------------------------------------
rem qtbsmngt.sql
rem
set lines 132
rem
col tablespace_name format a20 heading 'TABLESPACE|NAME'
col filename format a40 heading 'FILE|NAME'
col initial_extent format 999,999,999 heading 'INITIAL|EXTENT'
col next_extent format 999,999,999 heading 'NEXT|EXTENT'
col extent_management format a10 heading 'EXTENT|MNGT'
col SEGMENT_SPACE_MANAGEMENT format a10 heading 'SEGMENT|MNGT'
col INCREMENT_BY format 999,999,999 heading 'INCREMENT|BY'
rem
select t.tablespace_name,
substr(file_name, instr(file_name,'/',1,6)+1) filename,
t.extent_management,
t.allocation_type,
f.AUTOEXTENSIBLE,
f.MAXBYTES,
f.INCREMENT_BY
from dba_tablespaces t,
dba_data_files f
where t.tablespace_name=f.tablespace_name;
and t.tablespace_name like upper('%&ts%')
order by t.tablespace_name;
rem
set lines 80
rem
rem end of script
Hope this helps!. Regards Rupam
