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
SELECT *
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE LIKE ‚_LOB‘
AND OWNER LIKE ‚%MYSCHEMA%‘;
Find the size of BLOB/CLOB columns
SELECT DBMS_LOB.GetLength(„CLOB_DATA“)/1024/1024 AS SizeMB
FROM MY_SCHEMA.MY_TABLE_WITH_BLOB_CLOB
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
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;
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
