set lines 500 set pages 500 column ts format a8 column tablespace_name format a30 column allocated format 999,999,999 column unallocated format 999,999,999 column used format 999,999,999 column allocated_free format 999,999,999 column total_free format 999,999,999 column allocated_used_pct format 990.00 column total_used_pct format 990.00 select 'TS' as TS, ts.tablespace_name, allocs.allocated allocated, allocs.unallocated unallocated, trunc((allocs.allocated - trunc(free.MB))/(allocated+unallocated)*100,2) total_used_pct from dba_tablespaces ts inner join (select tablespace_name, sum(allocated) allocated, sum(unallocated) unallocated from (select tablespace_name, sum(bytes)/1024/1024 allocated, sum(maxbytes-bytes)/1024/1024 unallocated from dba_data_files where autoextensible = 'YES' group by tablespace_name union select tablespace_name, sum(bytes)/1024/1024 allocated, 0 from dba_data_files where autoextensible = 'NO' group by tablespace_name) inner_allocs group by tablespace_name) allocs on ts.tablespace_name = allocs.tablespace_name left join (select tablespace_name, sum(bytes)/1024/1024 MB from dba_free_space group by tablespace_name) free on ts.tablespace_name = free.tablespace_name where ts.contents = 'PERMANENT' order by 5 desc; set lines 150 column file_name format a80 column MB format 999,999,999 column MAXMB format 999,999,999 select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB from dba_data_files where tablespace_name = upper('&ts_name'); col tablespace format A16 SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used" FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name,df.bytes Order by 4; set lines 152 col FreeSpaceGB format 999.999 col UsedSpaceGB format 999.999 col TotalSpaceGB format 999.999 col host_name format a30 col tablespace_name format a30 select tablespace_name, (free_blocks*8)/1024/1024 FreeSpaceGB, (used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB, i.instance_name,i.host_name from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and i.inst_id=ss.inst_id;