Re: UNDO free space considered expired/unexpired undo which views to use?

On Thu, May 26, 2016 at 12:11 AM, Dba DBA <oracledbaquestions_at_gmail.com> wrote:

> 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#
>
>

-- 

*--*
*BR*
*Sumit Tyagi*
*+91-7829543355*

--
http://www.freelists.org/webpage/oracle-l