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!
SQL Jana 
The last script doesn’t work, would you please check it?
I am new to sql, was trying to correct it, but was unable to.
Sorry for my English.
Hi Yuan, The last section that looks like code is actually sample output. If you are referring to the script before it, what is the error that you received?
not displaying anything just this line -> PL/SQL procedure successfully completed.
You need to enable DBMS_OUTPUT and the sizes will be displayed there. The procedure is slightly different depending on the tool you are using.
Kindly set the serveroutput on by running the following command.
set serveroutput on ;
this might help.
As a faster alternative to DBMS_LOB when using SecureFile , you can use DBMS_SPACE –
DECLARE
l_segment_size_blocks NUMBER;
l_segment_size_bytes NUMBER;
l_used_blocks NUMBER;
l_used_bytes NUMBER;
l_expired_blocks NUMBER;
l_expired_bytes NUMBER;
l_unexpired_blocks NUMBER;
l_unexpired_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => ‘MY_SCHEMA’,
segment_name => ‘MY_SEGMENT’,
segment_type => ‘LOB’,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes);
DBMS_OUTPUT.put_line(‘segment_size_blocks:’ || l_segment_size_blocks);
DBMS_OUTPUT.put_line(‘segment_size_bytes :’ || l_segment_size_bytes);
DBMS_OUTPUT.put_line(‘used_blocks :’ || l_used_blocks);
DBMS_OUTPUT.put_line(‘used_bytes :’ || l_used_bytes);
DBMS_OUTPUT.put_line(‘expired_blocks :’ || l_expired_blocks);
DBMS_OUTPUT.put_line(‘expired_bytes :’ || l_expired_bytes);
DBMS_OUTPUT.put_line(‘unexpired_blocks :’ || l_unexpired_blocks);
DBMS_OUTPUT.put_line(‘unexpired_bytes :’ || l_unexpired_bytes);
END;
Thanks AB! This should definitely help a lot of the readers who visit this post.