USERS tablespace is growing much faster than LOB_TABLESPACE. Customer performs a large number of high volume discover scans frequently.
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.
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
from (select w.walkid walkid,
to_char(w.startdate, 'DD-MON-YYYY HH24:MI') STARTDATE,
from walk w,
where w.walkid = wr.walkid
and wr.walkreportid in (select walkreportid
order by 2 desc
Subscribing will provide email updates when this Article is updated. Login is required.