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:
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, '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!