Determining LOB space consumption by policy

Products

• Data Loss Prevention Enforce

Issue/Introduction

Some policies are more aggressive than others.  Is there a way to approximate the amount of space that each policy takes up?

Resolution

The following SQL script will only APPROXIMATE the amount of space that the policies occupy.  Treat this as the minimum amount of space occupied.  This is due to the use of NLS_LENGTH_SEMANTICS parameter of 'CHAR'.  See "KB: 54223 What is NLS_LENGTH_SEMANTICS" for more details.

Using SQLPlus, logged in as the Vontu schema owner, run the following:

set pagesize 100
break on report
compute sum of ml_orig on report
compute sum of mcl_cracked on report
compute sum of mcl_uncracked on report
compute sum of cvl_markers on report
compute sum of sum_total on report
select policyid,
       sum(dbms_lob.getlength(ml.networkoriginalmessage)) ml_orig,
       SUM(dbms_lob.getlength(mcl.crackedcomponent)) mcl_cracked,
       SUM(dbms_lob.getlength(mcl.uncrackedcomponent)) mcl_uncracked,
       sum(dbms_lob.getlength(cvl.crackedcomponentmarkers)) cvl_markers,
       sum(dbms_lob.getlength(ml.networkoriginalmessage))
      + SUM(dbms_lob.getlength(mcl.crackedcomponent))
      + SUM(dbms_lob.getlength(mcl.uncrackedcomponent))
      + sum(dbms_lob.getlength(cvl.crackedcomponentmarkers)) sum_total
  from messagelob ml,
       messagecomponentlob mcl,
       messagecomponent mc,
       conditionviolation cv,
       conditionviolationlob cvl,
       incident i
where ml.messageid = i.messageid
and i.messageid = mc.messageid
and mc.messagecomponentid= mcl.messagecomponentid
and cv.conditionviolationid = cvl.conditionviolationid
and cv.messagecomponentid = mc.messagecomponentid (+)
group by policyid
order by 1
/