CLOB datatype, where is it stored?

[Beantree]

I'm experimenting with a CLOB column.

Notes I've found indicate the actual CLOB is stored in 'OS files' and that just a pointer is stored in the actual table, if it's over 4000 bytes.

Where does the DB store these files, and what are they called?

I loaded the table via SQLLDR.

Thanks

 

[sem]

How did you find it?
I suppose that only BFILEs are stored in OS files. As for CLOBs, they may be stored inline, if sufficiently small, or separately, according to individual storage parameters. Regards, Dima

 

[Beantree]

per
Oracle 8i Application Developer's Guide - Large Objects (LOBs)
Release 8.1.5
A68004-01



LOBs in Comparison to LONG and LONG RAW Types


Only the LOB locator is stored in the table column; BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file. In the case of a LONG or LONG RAW the entire value is stored in the table column. For inline LOBs, Oracle will store up to 3964 bytes of data in the table column.

When you access a LOB column, it is the locator which is returned. When you access a LONG or LONG RAW, the entire value is returned.

A LOB can be up to 4 gigabytes in size. The BFILE maximum is operating system dependent, but cannot exceed 4 gigabytes. The valid accessible range is 1 to (232-1). By contrast, a LONG or LONG RAW is limited to 2 gigabytes.

 

[Wonboodoo]

sem is right. Only bfile's are stored as OS files. BLOBs & CLOBs are stored as segments within a tablespace in a similar fashion to tables & indexes. Run:

select * from user_lobs;

(or dba_lobs) for details.

 

[Beantree]

If that's the case, why does the Oracle docs say
"For inline LOBs, Oracle will store up to 3964 bytes of data in the table column.

When you access a LOB column, it is the locator which is returned. When you access a LONG or LONG RAW, the entire value is returned. "

Also, I loaded an entire 28 Mb text file into this table totally as CLOB data, and the table size is only 1 Mb.

This seems to indicate it is being stored elsewhere.

 

[Beantree]

I have found the answer.

The table had created a system generated segment elsewhere in the tablespace.

It is 24 Mb.

I will re-create the table to point the Lob Segment where I want it.

thanks

 

[sem]

As you may see that Oracle documentation is a bit self-contradictory.

The word "Only" in "Only LOB locator" should be omited.


The truth is that query DOES return only locator, though lob MAY be stored in separate tablespace. Lob may also be stored inline, but even in this case you obtain only locator
Regards, Dima