Oracle 12c, this is the one I am currently looking at, but I don't think
this issue is any different from an 11g DB.
Autoextend off: I know oracle calculates free space on undo differently
whether this is on or off.
Referencing Link on Expired vs. Unexpired Undo:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2015/06/15/how-does-oracle-reuse-the-expired-and-unexpired-undo-extents
Problem: Want to write a generic query that tells me how much free space I
have in each tablespace. It will also include UNDO and my assumption is
that all EXPIRED UNDO can be reused so its effectively free.
First I post a query I wrote that treats expired undo as 'free space' for
monitoring purposes. Is this the correct way to handle it? Then below that
I post DDL from DBA_TABLESPACE_USAGE_METRICS and I have a question about
it.
Query to monitor free space: Treats expired undo like 'free space'
with freespace as
(SELECT d.tablespace_name, sum(nvl(d.bytes,0)/1024/1024) as
Free_Space
FROM sys.dba_free_space d
GROUP BY d.tablespace_name ),
total as
(SELECT tablespace_name,
sum(decode(autoextensible,'NO',bytes,maxbytes))/1024/1024
TOTAL_SPACE,
sum(decode(autoextensible,'NO',0,maxbytes -
bytes))/1024/1024 EXTEND_SPACE
FROM sys.DBA_DATA_FILES
GROUP BY tablespace_name
),
expired_undo as (select tablespace_Name,
sum(nvl(bytes,0)/1024/1024) as expired_Space
from dba_undo_extents
where status = 'EXPIRED'
group by tablespace_Name)
select b.tablespace_name ,
(case when a.tablespace_name != expired_undo.tablespace_name then
round((nvl(Free_space,0) + EXTEND_SPACE) /total_space*100,0)
else round((nvl(Free_space,0) +
round(nvl(expired_undo.expired_space,0)) + EXTEND_SPACE)
/total_space*100,0) end )
from freespace a, total b,expired_undo
where a.tablespace_name = b.Tablespace_name (+)
Section 2:
DBA_TABLESPACE_USAGE_METRICS: See DDL below for the undo tablespace
section. When I query this view, the % used output does not seem to take
into account 'expired undo'. I get a much higher percent used than I do
with the query I wrote above.
Check the DDL:
select dbms_metadata.get_ddl('VIEW','DBA_TABLESPACE_USAGE_METRICS') from
dual;
Section on undo tablespce below
Link to Doc on v$filespace_usage. Note that this section uses the FLAG=6.
The flags are not documented in the standard oracle docs. What does this
mean?
http://docs.huihoo.com/oracle/database/12cr1/server.121/e17615/refrn30333.htm
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
(sum(f.allocated_space)/sum(f.file_maxsize))*100
FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
WHERE
t.online$ != 3 and
t.bitmapped <> 0 and
f.inst_id = param.inst_id and
param.name = 'undo_tablespace' and
t.name = param.value and
f.flag = 6 and
t.ts# = f.tablespace_id
GROUP BY t.name, f.tablespace_id, t.ts#
