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,
table_name,column_name,a.segment_name,index_name;


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 

begin
 dbms_scheduler.drop_job('MYJOB',false);
 dbms_scheduler.drop_schedule('MYJOBSCHED',false);
 dbms_scheduler.create_schedule('MYJOBSCHED',null,'freq=WEEKLY;byday=MON;byhour=18',null,'Weekly Schedule to purge ADF persistency segments');
 dbms_scheduler.create_job('MYJOBS','MYJOBSCHED','PLSQL_BLOCK',
                           'Begin delete from MY_USER.ps_txn where creation_date<sysdate-1; alter table MY_USER.ps_txn modify lob (content) (shrink space); end;',
                            0,'DEFAULT_JOB_CLASS',FALSE,TRUE,'Comment');
 dbms_scheduler.disable('MYJOBSCHED');
 dbms_scheduler.enable('MYJOBSCHED');
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