Recycle

  1  SELECT
  2  ‘ALTER ‘||OBJECT_TYPE||’ PROD.’||OBJECT_NAME||’ STORAGE (BUFFER_POOL RECYCLE);’
  3  FROM (SELECT * FROM (
  4  SELECT OBJECT_NAME,VALUE,blocks,value/blocks val_block,OBJECT_TYPE FROM
  5  V$SEGMENT_STATISTICS t1,
  6  dba_segments t2
  7  WHERE t1.OWNER=’&schema’ and t2.OWNER=t1.owner AND
  8  STATISTIC_NAME=’logical reads’
  9  and segment_name=object_name
 10  and blocks<20000
 11  and value>100
 12  AND SEGMENT_NAME NOT LIKE ‘%BK%’
 13* order by 4) WHERE ROWNUM<2000)