Querying Datafiles, ASM and Segments in Oracle11g (including tablespaces) in Oracle11g, 10g and 9i. This is stuff I have online on my main website and in books, but I’ve tweaked it a bit over the years.
ASM:
set wrap off lines 132 pages 5000; col name format a30 select name,state,total_mb,free_mb from v$asm_diskgroup; select name,state ,round(sum(total_mb/1024),0) "UsedGb" ,round(sum(free_mb/1024),0) "FreeGb" from v$asm_diskgroup group by grouping sets(name,state);
Temp space:
select tablespace_name,file_name from dba_data_files union select tablespace_name,file_name from dba_temp_files order by 1,2;
Datafiles:
col tbs format a15
col Tb format 990
col Gb format 999,990
col Mb format 999,999,990
col file_name format a30
select tablespace_name "Tbs",autoextensible,maxbytes
,round(maxbytes/1024/1024,0) "Mb"
,round(maxbytes/1024/1024/1024,0) "Gb"
,round(maxbytes/1024/1024/1024/1024,0) "Tb"
, file_name
from dba_data_files
order by tablespace_name;
Tablespaces with free space:
set wrap off lines 132 pages 5000;
col GBUsed format 9990
col GBFree format 9990
col %Free format 990
select a.TABLESPACE_NAME
, sum(round((nvl(a.BYTES,0))/1024/1024/1024,0)) "GBHighWater"
, sum(round((nvl(a.BYTES,0)-nvl(b.BYTES,0))/1024/1024/1024,0)) "GBUsed"
, sum(round(nvl(b.BYTES,0)/1024/1024/1024,0)) "GBFree"
, sum(round(100-round(((nvl(a.BYTES,0)-nvl(b.BYTES,0))/nvl(a.BYTES,0))*100,0),0)) "%Free"
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
group by rollup(a.tablespace_name)
order by a.TABLESPACE_NAME;
Temp tablespaces:
set wrap off lines 132 pages 5000; col GBUsed format 9990 select TABLESPACE_NAME , round(sum(BYTES)/1024/1024/1024,0) "GbUsed" , round(sum(maxbytes)/1024/1024/1024,0) "MaxGb" from dba_temp_files group by TABLESPACE_NAME order by TABLESPACE_NAME;
Tablespace attributes:
col tab format a10 select tablespace_name "tbs" ,bigfile ,contents ,extent_management "ext" ,allocation_type "alltyp" ,segment_space_management "ssm" from dba_tablespaces order by tablespace_name;
Segment space – be careful with this one as it can be kinda slow:
set lines 132 wrap off pages 5000;
col Tbs format a10;
col owner format a10;
col segt format a10;
col segn format a10;
col Tb format 990;
col Gb format 999990;
col Mb format 999999990;
SELECT tablespace_name "Tbs"
, owner
, segment_type "SegT"
, segment_name "SegN"
, sum(ROUND(bytes/1024/1024/1024/1024)) "Tb"
, sum(ROUND(bytes/1024/1024/1024)) "Gb"
, sum(ROUND(bytes/1024/1024)) "Mb"
, sum(bytes) "Bytes"
, sum(blocks) "Blocks"
FROM dba_segments
where owner='Mrs Ada Foo'
group by owner, tablespace_name, segment_type, segment_name;
