Oracle BLOB - type family and size limits

Type in Oracle	Maximum size
`BLOB`	up to (4 GB - 1) x db_block_size
`SecureFiles LOB`	the default since 11g, adds deduplication, compression and encryption
`BFILE`	points at an operating-system file; the data stays outside the database

BLOB versus CLOB in Oracle

Oracle is the only engine here that really has types named `BLOB` and `CLOB`. BLOB counts bytes and CLOB counts characters under the database character set. The theoretical ceiling is enormous - roughly 32 TB with 8 KB blocks - so something else will stop you first.

What limits you first

The theoretical limit is far away, but a single INSERT is bounded by PGA memory and round trips: you must obtain an EMPTY_BLOB() locator and append with DBMS_LOB in chunks rather than sending the whole value at once.

BLOB from the command line

Export: expdp (Data Pump) or SQL*Loader with a LOBFILE
Import: impdp, or SQL*Loader - point large objects at a `LOBFILE` instead of inlining them

Things that bite with BLOB in Oracle

Inserting a large BLOB literal directly exhausts PGA memory first; use EMPTY_BLOB() plus chunked DBMS_LOB.WRITEAPPEND
The legacy exp / imp utilities handle large LOBs poorly - use Data Pump, expdp and impdp
LONG is a pre-1990s type that cannot be mixed with LOBs; new tables should use CLOB
Inlining large LOBs in a classic SQL*Loader control file makes the control file explode - reference a LOBFILE instead