Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Large Object Datatypes

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

* Multiple LOBs are allowed in a single row.

* LOBs can be attributes of a user-defined datatype (object).

* The LOB locator is stored in the table column, either with or without the actual LOB value; BLOB, NCLOB, and   CLOB values can be stored in separate


* BFILE data is stored in an external file on the server.

* When you access a LOB column, it is the locator that is returned.

* A LOB can be up to 4 GB in size. BFILE maximum size is operating system-dependent, but cannot exceed 4 GB.

* LOBs permit efficient, random, piecewise access to, and manipulation of, data.

* You can define one or more LOB datatype columns in a table.

* With the exception of NCLOB, you can define one or more LOB attributes in an object.

* You can declare LOB bind variables.

* You can select LOB columns and LOB attributes.

* You can insert a new row or update an existing row that contains one or more LOB columns, and/or an object with one or more LOB attributes. (You can set the internal LOB value to NULL or empty, or replace the entire LOB with data. You can set the BFILE to NULL or so that it points to a different file.)

* You can update a LOB row/column intersection or a LOB attribute with another LOB row/column intersection or LOB attribute.

* You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. Note that for BFILEs, the actual operating system file is not deleted.       

The LOB datatypes themselves are defined as follows:

BLOB. Binary large object, usually used to store graphics, video, or audio data. Maximum length of 4 GB under Oracle8, 8i, and 9i.  

CLOB. Character large object, usually used to store single-byte character objects such as large text files. Maximum length of 4 GB under Oracle8, 8i, and 9i.  

NCLOB. National character large object, usually used to store multibyte character data. Maximum length of 4 GB under Oracle8, 8i, and 9i.

BFILE. Binary external file locator. This probably varies in size depending on the value of the directory and filename placed into it. In empirical testing, with a directory specification of ‘GIF_FILES’, which contained the directory value ‘e:\Oracle3\Ortest1\Bfiles’ and an average name length of 10 for the actual external files, this column showed a length of 40.

See Code Depot


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.