How to find BLOB/CLOB columns size in Oracle tables

Here I'll show you how to calculate blob, clob columns sizes in tables.
1) 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.

SELECT DBMS_LOB.GetLength("BLOB_COLUMN") / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB;

2) find the size of all rows with BLOB/CLOB for particular tables

SELECT SUM(DBMS_LOB.GetLength("BLOB_COLUMN")) / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB

3) If you want to find all BLOB/CLOB sizes in your oracle database for a particular schema here is code:

DECLARE
    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 '%SCHEMA_NAME%' -- SCHEMA_NAME
                        AND TABLE_NAME NOT LIKE 'BIN$%' -- EXCLUDE DELETED TABLES FROM RECYCLE_BIN
                 )
    LOOP
        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;

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes there is situation when you need to modify default attributes for tables, tables partitions, index or index partitions in Oracle database. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. Here I'll show you how to do these things:

Check Default atributes for partitioned Table :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES WHERE TABLE_NAME='TABLE_NAME';

Check Default atributes for partitioned Index :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES WHERE INDEX_NAME='INDEX_NAME';

Modify Default Attributes for a Table :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Table partitions :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index partitions :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;