Get storage size of CLOB column within a table

Chaka1313

Hi all!

We are experiencing an issue where tons of rows are being inserted within a clob column and we are trying to determine what the size of the entire column is. We have the table size, but we'd like to know just how much this one column is taking up so that we can shrink it down and reduce the space within that table. Is there a query that anyone can provide that can determine this?

AndrewSayer

Chaka1313 wrote:Hi all!We are experiencing an issue where tons of rows are being inserted within a clob column and we are trying to determine what the size of the entire column is. We have the table size, but we'd like to know just how much this one column is taking up so that we can shrink it down and reduce the space within that table. Is there a query that anyone can provide that can determine this?

Why is the clob the problem? When you remove rows, you remove the entire row. If you are considering removing a column because a lot of rows were inserted then I dont think you explained the whole problem.

For am out of line clob, check the segment size in dba_segments, obtain the segment name from dba_lobs. For in line clobs, realise that Oracle is storing it with the rest of the row so there’s not going to be any metadata that tells you the size of the individual column, you could run a query summing up the size the column (look at dbms_lob) for rows where the lob is stored in line, but what does that help you with?

If you want to know how much space will be released on a shrink operation, try it on your development server and see.

Adityanath Dewoolkar

Hello,

Firstly check LOB segment name:

SELECT OWNER,TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,INDEX_NAME

FROM DBA_LOBS

WHERE TABLE_NAME=<YOUR_TABLE_NAME>

AND OWNER =<YOU_TABLE_OWNER_NAME>

AND COLUMN_NAME=<LOB_COLUMN_NAME>;

Once you get segment name, you can query dba_segments

SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024/1024) "LOB size (GB)"

FROM DBA_SEGMENTS

WHERE SEGMENT_NAME =<SEGMENT_NAME_FROM_QUERY_1> group by OWNER,SEGMENT_NAME;

Regards,

Adi

Mustafa_KALAYCI

it depends. if clob data length is less than 3964 then it is stored inline so your clob size is in tablesize if lob length is higher than 3964 then it stored outline and you can get it size by checking lob segment:

select sum(bytes/1024/1024) size_in_MB from DBA_Segments a

where (owner, segment_name ) in (select L.owner, L.segment_name from DBA_lobs L where table_name =UPPER('YOUR_TABLE_NAME_HERE!!!!!!!!!'));

Chaka1313

The reason we're looking at this is because there are XML files stored in this column and we are trying to shrink the size of these XML files, but to determine if this will work, we need to know the amount of storage this column takes up in the database.

Jonathan Lewis

There are four main aspects to this - of which two have been highlighted so far:

The size allocated to the LOB segment (which is separate from the table segment).

The fact that some LOBs may be stored "in row", while others will be stored "out of row".

These two facts mean you have to decide exactly what it is you want to measure and how careful you are to measure it correctly

The two other points are

LOBs based on a multi-byte characterset are converted to a two-byte fixed width character set for storage - and the length() (or dbms_lob.getlength()) calls return the number of characters, not the number of bytes - so a LOB segment could be twice the size you think it ought to be. (This also means that a LOB value could go "out of row" at roughly close to 2,000 characters, rather than close to the 4,000 that most people tend to think of).

There is a "pctversion"/undo_retention option that dictates how much of the currently allocated space in a LOB segment should be used for retaining old versions of LOBs - so don't forget to allow for that when working out what the size of your LOB segment ought to be. There have been cases where Oracle doesn't handle space re-used very well - depending on version and whether you're using securefile LOBs or basicfile LOBs - so one reason for checking the sizes is to get an idea of whether or not the  LOB segment has become very much larger than it ought to be.

Here's a note I wrote about LOB sizing a little while ago, with a couple of links to articles that talk about checking storage requirements: https://jonathanlewis.wordpress.com/2019/05/02/lob-length-2/

Regards

Jonathan Lewis

John Thorton

Chaka1313 wrote:The reason we're looking at this is because there are XML files stored in this column and we are trying to shrink the size of these XML files, but to determine if this will work, we need to know the amount of storage this column takes up in the database. 

Raw XML data representation is EXTREMELY verbose.

If the XML data values could be stored in a normalized heap table, the space required would be drastically reduced.

Chaka1313

Thank you so much for the response and explanation on this! I really found your explanation super helpful and put me on the right track on what I needed.

cormaco

The reason we're looking at this is because there are XML files stored in this column and we are trying to shrink the size of these XML files.

Consider converting you XML files to XMLTYPE with Binary XML storage:

XMLType Storage Models

Binary XML storage (the default) – XMLType data is stored in a post-parse, binary format designed specifically for XML data. Binary XML is compact, post-parse, XML schema-aware XML data. This is also referred to as post-parse persistence.

Chaka1313

Unfortunately, we can't do that at the moment, since it would require a lot of dev time which we don't have at the moment. But it is worth considering for sure. Appreciate the suggestion!

cormaco

Unfortunately, we can't do that at the moment, since it would require a lot of dev time which we don't have at the moment.

Dev time required is minimal, conversion from CLOB is a builtin feature of XMLTYPE:

XMLTYPE

Constructs an instance of the XMLType datatype. The constructor can take in the XML as a CLOB, VARCHAR2 or take in a object type.