search cancel

Identifying very large Walkreport Records

book

Article ID: 162280

calendar_today

Updated On:

Products

Data Loss Prevention Enforce Data Loss Prevention Network Discover

Issue/Introduction

USERS tablespace is growing much faster than LOB_TABLESPACE.
Customer performs a large number of high volume discover scans frequently.
 

Cause

For each scan, if it's successful or not, if it brings back incidents or completes without detecting anything, a record is created within a CLOB column called REPORT within the WALKREPORT table.  The larger the scan target, the greater the frequency, and the greater the number of individual targets, the larger the consumption of USERS tablespace through the growth of the WALKREPORT table.

Resolution

Use the following script to identify the largest REPORT column records in the WALKREPORT table.  Script will show the top 20 records and identify their associated scans.  Follow up the investigation by tuning the scans, and clearing the scan history where no incidents were detected.  Run script from SQLPlus logged in as protect (or the schema owner):

set pages 100
column walkid format 9999999
column report format 999999999999
column startdate format a17
column name format a30
column diff format a4

select *
from (select w.walkid walkid,
       dbms_lob.getlength(wr.report) report,
       sa.name,
       to_char(w.startdate, 'DD-MON-YYYY HH24:MI') STARTDATE,
       to_char(w.isdifferential) diff
  from walk w,
       walkreport wr,
       sysinfotargettype stt,
       scanassignment sa
 where w.walkid = wr.walkid
   and wr.walkreportid in (select walkreportid
                             from walkreport
                            where dbms_lob.getlength(report)>1000000)
   and wr.walkid=stt.walkid
   and stt.scanassignmentid=sa.scanassignmentid
order by 2 desc
)
where rownum<20
/