Oracle Logbook

March 28, 2011

What objects should go to recycle

Filed under: Uncategorized — Helio Dias @ 8:03 pm

Basically the objects with very low TCH
Col object_name format a30
Col object_type format a20
SELECT o.owner, object_name, object_type, COUNT(1) buffers
FROM SYS.x$bh, dba_objects o
WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
AND obj = o. object_id
AND o.owner not in ('SYSTEM','SYS')
GROUP BY o.owner, object_name, object_type
ORDER BY buffers;
ALTER TABLE

STORAGE (BUFFER_POOL RECYCLE)

2 Comments »

  1. Col object_name format a30
    Col object_type format a20
    select buffer1000*1000+999 buffer_ate,qtd from (
    select trunc(buffers/1000) buffer1000,count(*) qtd from (
    SELECT o.owner, object_name, object_type, COUNT(1) buffers
    FROM SYS.x$bh, dba_objects o
    WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
    AND obj = o. object_id
    AND o.owner not in ('SYSTEM','SYS')
    GROUP BY o.owner, object_name, object_type
    ORDER BY buffers) group by trunc(buffers/1000)
    order by 1);

    Groupping view

    Comment by heliodias — September 13, 2013 @ 7:22 pm | Reply

  2. with blocos as (
    SELECT /*+materialize*/ o.owner, object_name, object_type,
    COUNT(1) buffers
    FROM SYS.x$bh, dba_objects o
    WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
    AND obj = o. object_id
    AND o.owner not in ('SYSTEM','SYS')
    GROUP BY o.owner, object_name, object_type
    ORDER BY buffers)
    select sum(buffers)*8192/1024/1024 from blocos
    ;
    Total MB size in need for recycle

    Comment by heliodias — September 13, 2013 @ 7:56 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

March 2011
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
28293031  
  • Recent Posts

  • Recent Comments

    wordman512 on Grouping with Rollup
    heliodias on AWR it´s not free and requires…
    Venkata on AWR it´s not free and requires…
    jsixface on Best way to delete millions ro…
    heliodias on What objects should go to…
  • _

  • Pages

  • Archives

  • Blog Stats

    • 129,927 hits
  • Blog at WordPress.com.