I have a quick question on how Cloud Control monitor free space on tablespaces. I tried several scripts and received same results that showing one of our tablespaces ran out of spaces, but EM said that TS has 45% free space. Apparently, EM is correct because we are still able to insert data. However, most tablespaces free spaces from my scripts is about ~5% variance from the EM online report. All tablespaces are ASM, locally managed with autoextent.
Below are the scripts I tried:
-- 1. --
select * From DBA_TABLESPACE_USAGE_METRICS
;
-- 2. --
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
column "tablespace_name" format a10
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
;
-- 3. --
col tablespace_name format a25 heading "Tablespace Name"
col logging format a10
col status format a12
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space,((free_space/total_space)*100) Pct_free
FROM
(select tablespace_name, sum(nvl(bytes,0)/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name
);