viewts.sql

-- 
-- Title :       viewts.sql 
-- Description : To display all database 
--               tablespaces and their 
--               associated datafiles and 
--               sizes.
-- 
-- Usage/Notes : 
-- 
-- Copyright :   ABCdba.com 2002 
--
 
SET TERMOUT OFF 
STORE SET save_env.sql REPLACE 
SET TERMOUT ON 
 
CLEAR BREAKS 
CLEAR COLUMNS 
CLEAR COMPUTES 
 
COL mbytes HEA " MBytes " FOR 99999.999 
COL tablespace_name HEA " Tablespace " FOR a18 
COL file_name HEA " DBF Name " FOR a45 
COL ts# HEA "TS#" FOR 99 
 
SELECT 
ts.tablespace_name, vts.ts#, 
ts.file_name, ts.mbytes 
FROM
v$tablespace vts,
( 
SELECT 
tablespace_name, file_id, file_name, 
( bytes / 1048576 ) mbytes 
FROM 
sys.dba_data_files 
UNION 
SELECT 
tablespace_name, file_id, file_name, 
( bytes / 1048576 ) mbytes 
FROM 
sys.dba_temp_files 
) ts 
WHERE
vts.name = ts.tablespace_name 
ORDER BY
ts.tablespace_name, ts.file_id 
/
 
@save_env.sql 
host rm save_env.sql 
SET TERMOUT ON

© copyright 2001-2014 ABCdba.com | all rights reserved