Manage Large Objects

Manage Large Objects

LONG’s vs. LOB’s
There are four types of large object types, or LOBs: CLOB, NCLOB, BLOB and BFILE.
While CLOBs, NCLOBs and BLOBs are stored in tablespaces, BFILEs are pointers to binary files stored outside the actual database. Because of this BFILEs are read-only.
Tables can contain only one LONG column and many LOB columns.
A table containing a LONG can not be partitioned, with LOB’s it can.
Max size of a LONG is 2GB, max size of a LOB is 4GB.
LONG’s are read sequentially while LOB’s can be randomly accessed.
LONG’s are stored in-line, that is within the column of the table. LOB’s can be stored in-line or out-of-line. LOB’s are made up of 2 parts, the locator and the actual LOB data. The Locator is stored in the column, but the LOB data can be stored in a separate tablespace. BFILES are pointers to external files.
LONG’s can not be defined as attributes in a user-defined object, while LOB’s can.
LOB bind variables can be defined.

Implement Oracle DIRECTORY Objects
Since BFILEs are stored externally they do not require the LOB storage clause but instead need a directory object which tells Oracle where they are located in the file system. Directory objects are created with the CREATE OR REPLACE DIRECTORY statement and specify the operating system file path of the directory which contains the BFILE in question.
You need to make sure that Oracle has OS permissions to access a directory object. Oracle will let you create invalid directory objects (i.e., non-existent directories or directories for which it does not have permissions) and will not check their validity and will NOT create them for you, so check your typing carefully when creating them. You will only get an error down the line when Oracle tries to access the BFILE and cannot.
Creating directory objects which reference directories that contain the actual Oracle binaries, datafiles, control files, etcetera is a big security risk and is strongly discouraged by Oracle.

Create LOB storage
There is an additional storage clause that defines out-of-line storage characteristics for LOB’s.
Example:
CREATE TABLE docs
(doc_id varchar2(50),
doc CLOB,
CONSTRAINT pk_docs PRIMARY KEY (doc_id))
STORAGE (INITIAL 32K NEXT 32K PCTINCREASE 0)
TABLESPACE doc1
LOB (doc) STORE AS
(TABLESPACE doc2
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
CHUNK 16K PCTVERSION 10 NOCACHE LOGGING) ;
Note that the LOB has its own tablespace defined for out-of-line storage.
CHUNK: number of dB blocks in a LOB “manipulation” chunk. Max value is 32K. Must be less than or equal to the NEXT value. It is a multiple of the dB block size and is the minimum unit when accessing LOB’s. CHUNKs must be composed of contiguous blocks. On the other hand, CHUNKs need not be stored next to each other.
PCTVERSION: % of how much LOB storage needs to be changed before empty chunk space is reused. Older versions of LOB’s will not be overwritten until at least this much of the storage has been consumed. 10 is the default.
(NO)CACHE (NO)LOGGING: specifies whether or not to load object into the dB buffer cache and whether to turn on or off redo logging.
Oracle does not maintain data integrity for external files, BFILES.