Oracle lob Introduction

What is lob?

Lob is a big object data type of the Oracle database. It can store strings of more than bytes, binary data, OS files, and other large object information. the maximum storage capacity. The root Oracle version depends on the size of the Oracle block.

Which lob types are available?

Currently, Oracle provides four lob types: clob, nclob, blob, and bfile. clob and nlob are big strings, and nlob is a multi-language set character type, similar to nvarchar, they are used to replace the previous long type; the blog is of the big binary type, which is used to replace the previous long raw type; bfile can store various files in the operating system.

What are internal lobs and external lobs?

Internal lobs refers to lob data stored in Oracle tablespace. clob, nclob, and blog are all internal lobs; external lobs refers to lob data stored in the operating system file outside the database, and bfiel is the only external lobs. bfile provides a way for us to access external files in SQL.

How much data can a lob store?

From 10 Gb Oracle Database reference
Bfile: maximum size: 4 GB maximum size of a file name: 255 characters maximum size of a directory name: 30 characters maximum number of open bfiles: the maximum number of bfiles is limited by the value of the session_max_open_files initialization parameter, which is itself limited by the maximum number of open
Files the operating system will allow. BLOB: maximum size: (4 GB-1) * db_block_size initialization parameter (8 TB to 128 TB) the number of Lob columns per table is limited only by the maximum number of columns per table (that is, 10001 ). clob: maximum size: (4 GB-1) * db_block_size initialization parameter (8 TB to 128 TB) the number of Lob columns per table is limited onlyby the maximum number of columns per table (that is, 10001 ).
Nclob: maximum size: (4 GB-1) * db_block_size initialization parameter (8 TB to 128 TB) the number of Lob columns per table is limited only by the maximum number of columns per table (that is, 10001 ).

Where can lob be used?

In addition to defining column types in tables, lob can also be used to store XML data. For the Collection types of Oracle, see the following example:

1) Create Table lobtest (A varchar2 (20), B clob, C BLOB) tablespace users;-Definition of the lob table

2) lob storage set type

Create tablespace person_lob datafile size 102464 K autoextend on next 100 m maxsize 1024000 K extent management local uniform size 1 M Segment space management auto;
/
Alter user mayp quota unlimited on person_lob;

Create or replace type person_att is object (ID number (5), name varchar2 (30), sex varchar2 (1), age Number (5, 2 ));
/
Create or replace type person_atts is varray (5) of person_att;
/
Create Table person (person_id number (5), attribute1 person_atts)
Varray attribute1 store as lob person_attribute (
Tablespace person_lob
Enable storage in row
Chunks 16384
Pctversion 5
Cache reads
Index person_lob_idx)
Tablespace users
Pctfree 0
/
An example of storing varray data with lob is defined above.

What are the attributes of the lob segment?

By default, when a table containing the lob field is defined, Oracle automatically creates two segments for each lob, lob segment and LOB index segment. the lob segment stores the specific values of each lob, while the lob index segment stores the addresses of each lob value. lob segment, lob index segment, and table segment are stored in the same tablespace. oracle provides separate segment attributes for LOB segments. when creating a table, we can define that the lob and table are stored in different tablespaces respectively. When defining lob, we must consider the following important attributes:

Chunk:A logical block larger than the Oracle block size is dedicated to the storage of Lob data. The default value is the size of db_block_size. If it is manually defined, it must be defined as a multiple of db_block_size. The maximum value cannot exceed 32 K. Unreasonable chunk definition does not waste storage space, but also affects performance. before definition, you must understand the average size of data in each lob column of the application to minimize the space waste of lob. the following table describes everything:

  Data Size                     CHUNK Size    Disk Space Used to Store the LOB  Space Utilization(Percent)  3500 enable storage in row    irrelevant    3500 in row                        100  3500 disable storage in row   32 KB         32 KB                              10  3500 disable storage in row   4 KB          4 KB                               90  33 KB                         32 KB         64 KB                              51  2 GB +10                      32 KB         2 GB + 32 KB                       99+

The above table uses some data to illustrate the storage relationship between Chunk and data, and more vividly shows the disk space interest rate. The red part indicates the unreasonable chunk definition.It must be noted that the Space wasted by the lob cannot be reused.

Disable/enable storage in row:The default value is enable storage in row. When the lob segment is not separated, each row of data in the table is stored in the same block. If the lob column is large, it may cause serious row links. When the lob segment and the table segment are separated, Oracle will automatically store the lob data smaller than 4 K in Table segment, store lob data larger than 4 K in the lob segment. if it is set to disable storage in row, Oracle will store the lob data in the lob segment regardless of the size of the lob data when the lob segment and the table segment are separated, in this way, the above
3500 disable storage in Row 32 KB, 32 KB, 10 cases, Waste 90% of storage space.

Pctversion N/retention:These two attributes are used to solve the consistent read problem of Lob segments. The special nature of Lob determines that it cannot use Undo/rollback segment to manage its own updated old version. Generally, lob will divide some space in its tablespace to manage its own undo, read consistent. the lob update principle is to allocate a new chunk in the lob segment to insert new data and keep the old image. If multiple updates exist for one data, there will be multiple versions. pctversion defines the size of the Undo area in the lob segment. pctverision is a percentage. It defines the percentage of all lob spaces used to store the pre-image. If the pre-image space exceeds this percentage, oracle does not automatically expand the size of this Part and will reuse the space of these front images. if a lob Segment segment is updated frequently, the growth of this segment may be very fast. retention is a new 9i parameter and can only be used when tablespace uses assm. When lob is updated, the image will be retained for a period of time. The specific time is determined by the undo_retention parameter. the decision adopts the Undo method and must be determined after the application test.

Nocache/cache reads/Cache: Defines the lob cache mode. nocache indicates that no lob data is cached; cache reads indicates that data is cached when lob read is used; cache indicates that data is cached when both read and write are performed.

Example of Lob creation:
Create Table person_new (ID number (5), name varchar2 (30), remark clob, photo blob not null)
Lob (remark) store as person_remark (
Tablespace person_lob
Enable storage in row
Chunks 8192
Pctversion 2
Cache reads
Index person_remark_idx)
Lob (photo) store as person_photo (
Tablespace person_lob
Disable storage in row
Chunks 16384
Pctversion 2
Cache reads
Index person_photo_idx)
Tablespace users
Pctfree 10
/

Can lob be operated in the SQL environment?

Lob can perform DML operations like other data types.

Insert into person_new values (1, 'Robin. M', 'he is a goold boy ', empty_blob ());

Update person_new set remark = 'He is a goog boy, he is from Beijing China' Where id = 1;

Delete from person_new where id = 1;

Commit;

Create Table bfile_test (Files bfile) tablespace users;

Insert into bfile_test values (bfilename ('Port', 'C. dat '));

Commit;

SQL> select * From bfile_test;
SP2-0678: column or attribute type can not be displayed by SQL * Plus
SQL> select * From person_new;
SP2-0678: column or attribute type can not be displayed by SQL * Plus

SQL> select remark from person_new;

Remark
---------------------------
He is a goog boy, he is from Beijing China

As shown in the preceding example,Blog and bfiel cannot be directly displayed in SQL * Plus.

Which lob APIs can be operated on?

PL/SQL can use dbms_lob to operate any lob and bfile objects. Oracle also provides an OCI programming interface to operate lob. Java and C # provide corresponding API operations for lob.

How can I convert other character types to clob and binary types to blob?

To_clob can be used to convert char, nchar, varchar2, nvarchar2, and nclob to clob;
To_lob can be used to convert long raw to blob and long to clob;
To_nclob can be used to convert char, nchar, varchar2, nvarchar2, and clob to nclob.

Can a lob object be remotely operated by DB link?

In addition to bfile and clob, blob can be used to locally access and operate remote lob objects. Currently, the following operations are allowed:

Create Table as select... From table @ remote;
Insert into .. select * Form Table @ remote;
Update... Set .. = (Select... from Table @ remote );
Insert into Table @ remote (...) As select * From local_table;
Update remote @ table set... = (Select... from local_table );
Delete from remote @ table where .....

Lob-related SQL functions and dbms_lob cannot access or operate remote lob objects.

Can a lob column be indexed?

Currently, clob columns support Oracle Text Index, domain index, function-base index, and extensible index.

Does lob support partition tables?

You can use the lob column in a partition table.,It also supports exchange partition, move partition, merge partition, and other operations.

Does lob Support Index-organization table?

Oracle supports creating clob, blog, and bfile columns in index-organization table, but does not support creating lob columns in index-organization table.

Reference: Oracle Database Application Developer's Guide-large objects 10g Release 2 (10.2)