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
 | 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… |
Create a free website or blog at WordPress.com.
Oracle Logbook
%d bloggers like this:
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 |
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 |