Get CLOB sizes

July 30, 2012 admin Oracle

SELECT
AVG(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)),
MIN(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)),
MAX(DBMS_LOB.GETLENGTH(&&vCOLUMN_NAME)),
COUNT(CASE WHEN DBMS_LOB.GETLENGTH(COMM_COMMENTS) > 4000 THEN 1 ELSE NULL END ),
COUNT(*)
FROM UD_STW.ZZZ_PS_COMMUNICATION
;
AVG MIN MAX COUNT COUNT(*)
———- — —- ——- ——–
14.2310298 1 1396 0 3108718

1 row selected.
what that means is that out of the 500 MB the lobs take only around 40 MB, which is less than 10%.

Check RPMs on Linux Data pump export (expdp) and import (impdp) examples