Find the largest LOB in an Oracle schema

Readme.md

Find the largest LOB in an Oracle schema

This is meant for DMS migration tasks using "Limited LOB Mode", where you need to set the LobMaxSize parameter.

• Run either as the schema owner or,
• For multiple schemas or if you're not the schema owner:
  • Adapt the line "AND OWNER = USER" to the list of schemas to be queried
  • Run script as admin user.

max_lob_size.sql

-- Find size of largest LOB per schema and table/column.
-- Use output to set LobMaxSize parameter for AWS DMS migration tasks.
-- Run either as the schema owner or with a privileged user.
set serveroutput on size unlimited
DECLARE
    v_TableCol VARCHAR2(100) := '';
    v_Size NUMBER := 0;
    v_MaxSize NUMBER := 0;
    c_LOB_FACTOR_INCREASE NUMBER := 1; -- <=== Adapt if you want a safety margin factored in
BEGIN
    FOR v_Rec IN (
                  SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME AS TableAndColumn,
                      'SELECT MAX(nvl(DBMS_LOB.GetLength("' || COLUMN_NAME || '"),0))/1024 AS SizeKB FROM ' || OWNER || '."' || TABLE_NAME || '"' AS sqlstmt
                  FROM ALL_TAB_COLUMNS
                  WHERE DATA_TYPE LIKE '_LOB'
                        AND OWNER = USER -- <========= Adapt schemas to search here
                        AND TABLE_NAME not like 'BIN\$%'
                 )
    LOOP
        EXECUTE IMMEDIATE v_Rec.sqlstmt INTO v_Size;
        IF nvl(v_Size, 0) > 0 THEN
            DBMS_OUTPUT.PUT_LINE (v_Rec.TableAndColumn||': '||v_Size);
            v_Size := ROUND(v_Size * c_LOB_FACTOR_INCREASE);
            v_MaxSize := greatest (v_MaxSize, NVL(v_Size,0));
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ('LOB max size: '||ceil(v_MaxSize)||' kB');
exception
    when others then
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack());
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace());
        raise;
END;
/