How to troubleshoot Oracle related space issues

Products

• Data Loss Prevention

Issue/Introduction

You are seeing growth in your database and would like to trace this down further and see where the space is being taken.

Environment

Oracle 12c/19c

Resolution

First, I would look at trying to figure out where the space is being taken up.  To do this you can run export_gather_stats.sql contained in <installed dir of dlp>\protect\scripts.  So for 15.8 it would look like 'C:\Program Files\Symantec\DataLossPrevention\EnforceServer\15.8.00000\Protect\scripts'.  The syntax to run this script is below.

sqlplus protect@protect @export_gather_stats.sql export_gather_stats_output.html

You could also log into sqlplus using sqlplus /nolog and then connect to the protect instance.  From here you can run '@export_gather_stats.sql export_gather_stats_output.html' and this will work as well.

I would recommend running this script on a daily base if you are looking for smaller changes.  Weekly you can run to get a better picture as you normally dont see big differences in smaller time intervals when it comes to the database.  Either can be done as the more data sets you have, the better the growth you can track.  I would add the date to the filename so you know when this dataset is from.

Now there is lots of good data within the html that can be useful.  We will just look at a small set.  First, I would look at the total number of incidents you have.  Search for "# of incidents" and you will find the total number of incidents.  This is a good check to see how many incidents are added daily/weekly to your database.  

Next most often the not the space will be taken in the lob tablespace.  Searched for each and it will direct you to each column you want to look at.  So here we want to look at table MESSAGECOMPONENTLOB.  There are 3 columns you want to pay attention to under this table.

To explain these, I will use email message.  Let's say user sends an email with an attachment.

UNCRACKEDCOMPONENT - will contain the attachment

CRACKEDCOMPONENT - extracted text from the message

NETWORKORIGINALMESSAGE - this is the network message as a whole

So now you can understand why these columns are important.  Here you should be able to see what the growth is like and trace this back to incidents and what type of data is taking all the space.  This is one example, but you could also look at the data files.  It shows you space taken by each file.  From here you can create a table and take a few key points like the ones above and you should see the growth and where.

 

Additional Information

For more information about running the export_gather_stats.sql  report described above, see How To Run Database Tablespaces Summary "Full Report" manually (broadcom.com).