Oracle: Find The Total Size Of All Of Your CLOB & BLOB Columns In All Tables

Today I received a request from an application owner who wanted to know the size of all the CLOB and BLOB columns in all the non-system tables so that they have a good idea of the size involved for a migration.

Tables with BLOB/CLOB columns

The SQL to find the tables with the BLOB/CLOB columns is quite simple. In my case, I had the schemas in which I needed to look. You can alter the condition to suit your case

--This matches all columns that have CLOB or BLOB data type
SELECT *
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE '_LOB'
    AND OWNER LIKE '%MYSCHEMA%';

I got a list of columns back:
Oracle_BLOB_CLOB_Columns

As I looked at the number of tables that had CLOB and BLOB columns, it was more than a handful and I was not going to add them up by hand!

DBMS_LOB.GetLength – Find the size of BLOB/CLOB column

This is simply how to find the size of individual rows with the CLOB/BLOB column. DBMS_LOB.GetLength returns the number of characters (bytes) in the CLOB/BLOB column. We convert that value to MB by dividing it by 1024 twice.

SELECT DBMS_LOB.GetLength("CLOB_DATA")/1024/1024 AS SizeMB
FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB

While the above SQL will give you the detail level size for each row, the below SQL will give you the overall size

SELECT SUM(DBMS_LOB.GetLength("CLOB_DATA"))/1024/1024 AS SizeMB
FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB

Calculating the size for all tables with BLOB/CLOB columns

This is where the coding part comes in (simple, in this case). We just have to sum-up the size for all tables returned by the SQL that identifies BLOB/CLOB columns

DECLARE
    v_TableCol VARCHAR2(100) := '';
    v_Size NUMBER := 0;
    v_TotalSize NUMBER := 0;
BEGIN
    FOR v_Rec IN (
                  SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME AS TableAndColumn,
                      <span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>'SELECT SUM(DBMS_LOB.GetLength("' || COLUMN_NAME || '"))/1024/1024 AS SizeMB FROM ' || OWNER || '.' || TABLE_NAME AS sqlstmt
                  FROM DBA_TAB_COLUMNS
                  WHERE DATA_TYPE LIKE '_LOB'
                        AND OWNER LIKE '%MYSCHEMA%')
    LOOP
        DBMS_OUTPUT.PUT_LINE (v_Rec.sqlstmt);
        EXECUTE IMMEDIATE v_Rec.sqlstmt INTO v_Size;

        DBMS_OUTPUT.PUT_LINE (v_Rec.TableAndColumn || ' size in MB is ' || ROUND(NVL(v_Size,0),2));
        v_TotalSize := v_TotalSize + NVL(v_Size,0);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Total size in MB is ' || ROUND(v_TotalSize,2));
END;

In the code above, we generate the SQL with a SELECT which gives us the SUM. Then, we EXECUTE IMMEDIATE that SQL and aggregate total.

The DBMS_OUTPUT of this gives us the numbers we want

  • The sizes of the individual columns that are BLOB/CLOB
  • The total size of all the tables with BLOB/CLOB at the end
MY_SCHEMA_02.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 19.6
MY_SCHEMA_02.DOC_DOCS_HISTORY.VALUE size in MB is 6.52
MY_SCHEMA_02.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is 3.62
MY_SCHEMA_02.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is 3.9
MY_SCHEMA_02.DOC_DOC_DOCS.DOC_DATA size in MB is 3.06
MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0
MY_SCHEMA_02.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0
MY_SCHEMA_02.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0
MY_SCHEMA_02.DOC_IMD_PROCESS_LOG.LOG size in MB is 0
MY_SCHEMA_02.DOC_PL_DOC_DOCS.DOC_DATA size in MB is .27
MY_SCHEMA_02.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA.DOC_AR_DOC_DOCS.DOC_DATA size in MB is 1.52
MY_SCHEMA.DOC_DOCS_HISTORY.VALUE size in MB is .43
MY_SCHEMA.DOC_ARS_SIMS.ARS_SIM_BODY size in MB is .49
MY_SCHEMA.DOC_ARS_SIMS.ARS_REMINDER_BODY size in MB is .33
MY_SCHEMA.DOC_DOC_DOCS.DOC_DATA size in MB is .22
MY_SCHEMA.DOC_IMD_ERROR_RESULT.STACK_TRACE size in MB is 0
MY_SCHEMA.DOC_IMD_ERROR_RESULT.EXCEPTION_OBJECT size in MB is 0
MY_SCHEMA.DOC_IMD_JBYTE_ARRAY.VALUE size in MB is 0
MY_SCHEMA.DOC_IMD_PROCESS_LOG.LOG size in MB is 0
MY_SCHEMA.DOC_PL_DOC_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA.DOC_WF_STEP_DOCS.DOC_DATA size in MB is 0
MY_SCHEMA_02.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 22.65
MY_SCHEMA.DOC_VIEW_DOC_DOCS.DOC_DATA size in MB is 1.74

Total size in MB is 64.35

There is nothing fancy about the simple code above which has no comments! Hope someone will find this useful!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s