In Oracle database what is LOB and when use ?

 In Oracle, LOB means Large Object.

You use a LOB column when the data is too large, unstructured, or not practical to store in normal VARCHAR2 / RAW columns.

---

When do we use LOB?

Use LOBs when you need to store:

1) Large text data

Use CLOB (Character Large Object)

Examples

• XML / JSON documents
• long application logs
• email body / message content
• contracts / terms & conditions
• large comments / notes
• HTML content

Example use case

A document-management app stores full agreement text in a CLOB.

---

2) Binary data

Use BLOB (Binary Large Object)

Examples

• PDF files
• images
• audio
• video
• ZIP files
• scanned signatures
• certificates

Example use case

An HR system stores employee documents like:

• Aadhaar copy
• passport scan
• offer letter PDF
• profile photo

These are typically stored in a BLOB.

---

3) Unicode large text

Use NCLOB

This is similar to CLOB, but for national character set data.

Example use case

If you store large multilingual text requiring national character support, use NCLOB.

---

4) External file reference

Use BFILE

A BFILE does not store the file inside the database.
It stores only a pointer/reference to a file kept on the OS filesystem.

Example use case

A media application keeps video files on storage/NAS, and Oracle stores only the file reference.

---

Simple rule to choose datatype

Use normal columns when data is small

• VARCHAR2
• NVARCHAR2
• RAW

Use LOB when data is large

• Large text → CLOB
• Binary file → BLOB
• Unicode text → NCLOB
• External file reference → BFILE

---

Common real-world use cases of LOB

A) Document storage systems

Store:

• PDFs
• Word files
• scanned documents
• invoices
• reports

Recommended type: BLOB

---

B) Application audit / logging

Store:

• detailed JSON payloads
• API request/response bodies
• long exception stack traces
• large XML

Recommended type: CLOB

---

C) Healthcare systems

Store:

• medical reports
• radiology reports
• scanned lab files
• prescription images

Recommended type:

• report text → CLOB
• image/PDF → BLOB

---

D) Banking / financial systems

Store:

• KYC documents
• statement PDFs
• signed forms
• transaction payload archives

Recommended type: BLOB / CLOB

---

E) Product / e-commerce systems

Store:

• product manuals
• product images
• rich descriptions
• long HTML content

Recommended type:

• images/manual PDFs → BLOB
• long descriptions → CLOB

---

F) Middleware / integration / API platforms

Store:

• SOAP XML payload
• JSON request body
• large message body
• queue payload archive

Recommended type: CLOB

---

G) Knowledge repositories / content platforms

Store:

• article content
• wiki pages
• large markdown/html content

Recommended type: CLOB

---

When should you NOT use LOB?

Do not use LOB when:

1) Data is small

If your value is small enough for VARCHAR2, use VARCHAR2.

Example:

• name
• city
• email
• status
• small JSON text

Using LOB unnecessarily can make design and maintenance more complex.

---

2) You need frequent filtering/joining on the entire value

LOBs are not ideal for regular equality joins and standard indexing patterns like normal scalar columns.

Example:

• joining tables on file content
• grouping by huge text body

That is usually a bad design.

---

3) The file should live outside DB for architectural reasons

For very large media repositories (huge videos, large archives), sometimes it is better to store files in:

• object storage
• filesystem
• content server

and keep only metadata / path / URL in Oracle.

---

Benefits of using LOB

Advantages

• can store very large data
• supports unstructured and semi-structured content
• keeps data inside database security/backup framework
• transactional consistency
• can use SecureFile features like:
  • compression
  • deduplication
  • encryption

---

Drawbacks / considerations

Things to think about

• more storage consumption
• backup/restore can become heavy
• network transfer can be slower for very large LOB fetches
• application code must handle streaming/chunk reading properly
• poor design can impact performance

---

Example table design

Storing PDF in Oracle

CREATE TABLE employee_docs (

emp_id NUMBER,

doc_type VARCHAR2(50),

file_name VARCHAR2(200),

mime_type VARCHAR2(100),

upload_date DATE,

file_content BLOB

);

Use case

• PAN card
• passport
• degree certificate
• salary slip PDF

---

Storing large text / JSON / XML

CREATE TABLE api_audit_log (

log_id NUMBER,

api_name VARCHAR2(100),

request_time DATE,

request_payload CLOB,

response_payload CLOB

);

Use case

• API payload archive
• debugging integrations
• compliance audit

---

Performance guidance

Prefer SECUREFILE for modern Oracle databases

For most modern Oracle systems, SecureFile LOBs are preferred over BasicFile.

---

Performance depends on access pattern

LOB performance depends on:

• how often you read/write the LOB
• size of the LOB
• whether it is cached
• whether compression is enabled
• whether deduplication is enabled
• whether app fetches full LOB or only part of it

---

Quick decision guide

Use CLOB when:

• data is text
• length can be very large
• examples: logs, XML, JSON, HTML, comments

Use BLOB when:

• data is binary
• examples: PDF, image, video, ZIP, certificate

Use NCLOB when:

• large multilingual text with national character requirements

Use BFILE when:

• file should remain outside DB
• Oracle should only store the reference

---

Very practical examples

Example 1: Good use of LOB

A ticketing app stores full chat transcript of 2 MB per case.
✅ Use CLOB

Example 2: Good use of LOB

A claims application stores scanned image/PDF documents.
✅ Use BLOB

Example 3: Bad use of LOB

Storing customer name in CLOB
❌ Wrong — use VARCHAR2

Example 4: Bad use of LOB

Storing small status JSON of 300 bytes in CLOB for every row when VARCHAR2 is enough
❌ Usually unnecessary

1) Difference between BLOB, CLOB, NCLOB, and BFILE

BLOB — Binary Large Object

Use BLOB when the content is binary and should be stored inside the database. Typical examples are PDFs, images, audio, video, and other binary documents. Because it is an internal LOB, it is stored in the database and can participate in transactions. 

Use BLOB for:

• PDF documents
• scanned images / signatures / photos
• certificates, ZIP files, binary payloads

---

CLOB — Character Large Object

Use CLOB when the content is large text stored in the database character set. It is best for things like XML, JSON, HTML, logs, long descriptions, and message payloads. As an internal LOB, it is stored in the database and participates in transactions.

Use CLOB for:

• XML / SOAP payloads 
• JSON documents / large API requests & responses
• long logs / stack traces / comments / HTML content 

---

NCLOB — National Character Large Object

Use NCLOB when you need large text but specifically in the national character set, typically for multilingual or Unicode-heavy text scenarios. Oracle describes it as large string/document storage in the National Character Set. 

Use NCLOB for:

• multilingual documents
• large text where national character set support is explicitly required

---

BFILE — External Binary File

Use BFILE when the file should remain outside the database, on the server operating system file system, and the database should store only a reference/locator to it. Oracle states that BFILE is read-only from the database/application perspective and is suitable for static data and byte-stream access. 

Use BFILE for:

• static images/media managed outside DB 
• files stored on OS/NAS that should not be updated via Oracle
• loading external file content into internal LOBs later if needed 

---

2) Quick rule: when to use what

Choose CLOB if the data is text

Examples:

• JSON payload archive
• XML documents 
• long audit trail text or app logs

Choose BLOB if the data is binary

Examples:

• PDF invoice
• image/photo/signature 
• audio/video/document binaries 

Choose NCLOB if it is large multilingual text

Examples:

• large Unicode document content
• content where national character set handling is required 

Choose BFILE if the file must stay outside Oracle

Examples:

• externally managed static media library 
• OS-stored binary files that Oracle only references 

---

3) Internal LOB vs external file — how to decide

Store the file inside Oracle (BLOB / CLOB / NCLOB) when:

You need the data to be part of normal database transactions, backup/recovery, and commit/rollback behavior. Oracle states that persistent internal LOBs participate in transactions and can be recovered on transaction or media failure. 

This is the better choice when:

• the document is business-critical
• backup/restore must include the content automatically 
• you want consistent security/governance under DB controls 

Typical examples:

• KYC / HR / finance PDFs stored as BLOB 
• API payloads and logs stored as CLOB
• content repository text stored as CLOB or NCLOB 

---

Store the file outside Oracle (BFILE, or external content store) when:

You do not want the file bytes inside the database, and a reference is enough. Oracle defines BFILE as a locator to an OS file and notes that it is read-only and suitable for static data. 

This is usually better when:

• files are very large and managed by another system 
• content is mostly read-only / static 
• you want to avoid putting all binary storage growth into the database itself (architectural choice) while keeping metadata in Oracle

Typical examples:

• large video/media library managed by a content server, with Oracle storing metadata + pointer 
• static graphics shared by applications from OS/NAS storage

---

4) Performance recommendation

For internal LOBs, SecureFiles is generally the preferred modern storage architecture. Oracle documents SecureFiles as the newer LOB architecture and notes performance equal to or better than file-system-style performance for large objects; SecureFiles also support features like compression, deduplication, and encryption.

So for performance/design:

• BLOB/CLOB/NCLOB inside DB + SecureFile → best general choice for modern Oracle workloads 
• BFILE → only when you intentionally want external, read-only file handling

---

5) Real-world use cases

Use case A: API / middleware payload archive

Store request/response payloads as CLOB, because the content is text and often large (JSON/XML). Keeping it internal makes it transactional and easy to manage under DB backup/recovery. 

Use case B: HR or finance documents

Store PDFs, scanned docs, and signatures as BLOB, because the content is binary and should usually remain under DB backup/security control. 

Use case C: multilingual knowledge base

Store the content as NCLOB if national character set / multilingual handling is a hard requirement. 

Use case D: static external media repository

Use BFILE when files already live on server storage and Oracle only needs to reference them. This works best when the files are static and read-only from the DB side. 

---

6) What I’d recommend in practice

Given your background as a Database Architect, here is the most practical decision framework:

Use CLOB when:

• value is text
• the text can become large
• examples: audit payloads, logs, XML/JSON, configs, long comments

Use BLOB when:

• value is a binary document/file
• examples: PDFs, images, certificate files, office documents

Use NCLOB when:

• text is large and you explicitly need national character set semantics 

Use BFILE when:

• file storage is outside DB by design
• file is mostly static/read-only
• Oracle should store only the reference

Final one-line answer

Use CLOB for large text, BLOB for binary files, NCLOB for large multilingual text, and BFILE when the file should stay outside the database and be referenced read-only

Query to check Quick “red flag” query for performance review

SELECT l.owner,

       l.table_name,

       l.column_name,

       CASE WHEN l.securefile = 'YES' THEN 'SECUREFILE' ELSE 'BASICFILE' END AS lob_type,

       l.cache,

       l.in_row,

       l.chunk,

       l.retention,

       l.pctversion,

       l.compression,

       l.deduplication,

       l.encrypt,

       ROUND(s.bytes/1024/1024, 2) AS lob_mb

FROM   dba_lobs l

LEFT JOIN dba_segments s

       ON s.owner = l.owner

      AND s.segment_name = l.segment_name

WHERE  l.owner = UPPER('&OWNER')

AND   (

         l.securefile = 'NO'

      OR NVL(s.bytes,0) > 1024*1024*1024   -- > 1 GB

      OR l.compression <> 'NO'

      OR l.deduplication <> 'NO'

      OR l.encrypt = 'YES'

      )

ORDER BY lob_mb DESC NULLS LAST;