Forum Stats

  • 3,872,124 Users
  • 2,266,390 Discussions
  • 7,911,052 Comments

Discussions

Get storage size of CLOB column within a table

Chaka1313
Chaka1313 Member Posts: 23 Blue Ribbon
edited Aug 29, 2019 3:06PM in General Database Discussions

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?

Tagged:
Mustafa_KALAYCI
«1

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Aug 29, 2019 3:15AM
    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
    Adityanath Dewoolkar Member Posts: 346 Bronze Badge
    edited Jun 28, 2021 1:47PM

    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

    BlondeInTally
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,405 Bronze Crown
    edited Aug 29, 2019 3:17AM

    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
    Chaka1313 Member Posts: 23 Blue Ribbon
    edited Aug 29, 2019 9:35AM

    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
    Jonathan Lewis Member Posts: 10,115 Blue Diamond
    edited Aug 29, 2019 9:49AM

    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

    Mustafa_KALAYCI
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Aug 29, 2019 10:35AM
    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
    Chaka1313 Member Posts: 23 Blue Ribbon
    edited Aug 29, 2019 12:31PM

    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
    cormaco Member Posts: 2,034 Silver Crown
    edited Aug 29, 2019 1:00PM
    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
    Chaka1313 Member Posts: 23 Blue Ribbon
    edited Aug 29, 2019 2:32PM

    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
    cormaco Member Posts: 2,034 Silver Crown
    edited Aug 29, 2019 3:06PM
    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.