Wednesday, November 17, 2010

Monitor LOBs space

If you use LOBs and delete records from the tables with a LOB attribute, you should monitor LOBs segments.
For example, ADF is using a table (PS_TXN) to serialize user session state to the database, a good monitoring of any growing space used by PS_TXN must be done.

Check Space used by LOB segments

select a.tablespace_name LOB_TSpace, b.tablespace_name TAB_TSPACE,
       b.owner TAB_OWNER,a.owner LOB_OWNER,
       table_name,column_name,a.segment_name LOG_SEGMENT,index_name,
       sum(bytes) LOB_SEGMENT_SIZE from all_lobs a , dba_segments b
where a.segment_name= b.segment_name
and a.owner in ('MY_USER')
group by a.tablespace_name,b.tablespace_name, b.owner,a.owner,

Free Up LOB space

ALTER TABLE mytable modify lob (image) (shrink space);

To Automatically delete and shrink LOBs space on Log file or ADF PS_TXN file, you can create a DBMS scheduled job 

 dbms_scheduler.create_schedule('MYJOBSCHED',null,'freq=WEEKLY;byday=MON;byhour=18',null,'Weekly Schedule to purge ADF persistency segments');
                           'Begin delete from MY_USER.ps_txn where creation_date<sysdate-1; alter table MY_USER.ps_txn modify lob (content) (shrink space); end;',

You can check if Job ran successfully with

select * from dba_scheduler_job_run_details where job_name like '%MYJOB%';

No comments:

Post a Comment