DBA Tips Archive for Oracle

  


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

Contents

Creating Tables with LOB Columns

When creating a table, you have the option of creating one or more LOB columns, unlike with LONG or LONG RAW columns, in which case, only one column can be made either LONG or LONG RAW. Attempting to create a table with more than one LONG or LONG RAW will result in an ORA-01754: a table may contain only one column of type LONG.

Many options are available when creating LOB columns like whether to store data within the LOB in-line or out-of-line. This is done using the ENABLE | DISABLE STORAGE IN ROW clause. Keep in mind though, that if you enable store in-line, Oracle will only store the data within the row provided the length of the data (and the locator) is less then 4K. If the length of the LOB data (along with the row locator) are greater than 4K, then the LOB value will be moved out of the row into a LOB segment. However, the lob locator remains stored within the row.

When creating a table with a LOB column, keep in mind that under the covers, Oracle will create a LOB segment and LOB index for each LOB column. By default, the LOB segment and LOB segment will be created in the same tablespace as the table, but you have the option of changing this. Neither the LOB segment nor the LOB index can be altered or dropped. The default name for the LOB segment is SYS_LOBxxx, where xxxx is a hexadecimal number. The default name of the LOB index is SYS_ILxxx where xxxx is a hexadecimal number. The hexadecimal numbers for both the LOB segment and the LOB index are the same. For both the LOB segment and the LOB index, you have the option of specifying a name as well as specifying which tablespace they will go into.

Creating a table with LOB columns, is nothing more than creating a column while using one of the LOB types as the data type for that column. The following simple example creates a table named test_lob that creates a BLOB, CLOB, and BFILE column:


CREATE TABLE test_lob ( id NUMBER , xml_file CLOB , image BLOB , log_file BFILE );

For each of the LOB columns, Oracle will implicitly create a LOB segment and LOB index for each LOB column. You can view the information about the LOB columns you just created by quering either DBA_LOBS, ALL_LOBS, or USER_LOBS. Use the following query to view information on the test_lob created above:


SELECT table_name "Table" , column_name "Column" , segment_name "Segment" , index_name "Index" FROM user_lobs WHERE table_name = 'TEST_LOB'; Table Column Segment Index --------------- ------------------ -------------------------- -------------------------- TEST_LOB XML_FILE SYS_LOB0000148997C00002$$ SYS_IL0000148997C00002$$ TEST_LOB IMAGE SYS_LOB0000148997C00003$$ SYS_IL0000148997C00003$$

Another view of interest is USER_SEGMENTS:


SELECT l.table_name "Table" , segment_name "Segment" , s.segment_type "Type" , s.tablespace_name "Tablespace" FROM user_segments s INNER JOIN user_lobs l USING (segment_name) WHERE segment_name like 'SYS_LOB%' OR segment_name like 'SYS_IL%' ORDER BY l.table_name , segment_name; Table Segment Type Tablespace --------------- -------------------------- ------------------ ------------------ TEST_LOB SYS_LOB0000148997C00002$$ LOBSEGMENT USERS TEST_LOB SYS_LOB0000148997C00003$$ LOBSEGMENT USERS

A Complete Create Table Example

We now take a look at a more complex form of the CREATE TABLE... that specifies the name of the LOB segment and LOB index as well as storage characteristics. To perform these actions we use the LOB clause of the CREATE TABLE... statement. Although, with Oracle8i, the ability to specify details for the LOB index have been deprecated. Here is the general syntax for the CREATE TABLE... statement:


CREATE TABLE <table name> (column list) [physical attributes] [storage clause] [LOB (<lobcol1> [, <lobcol2>...]) STORE AS [<lob_segment_name>] ( [TABLESPACE <tablespace_name>] [{ENABLE | DISABLE} STORAGE IN ROW] [CHUNK <chunk_size>] [PCTVERSION <version_number>] [ { CACHE | NO CACHE [{LOGGING | NOLOGGING}] | CACHE READS [{LOGGING | NOLOGGING}] } ] [<storage_clause_for_LOB_segment>] [INDEX [<lob_index_name>] [physical attributes] [<storage_for_LOB_index>] ] ) ] [LOB (<lobcol1> [, <lobcol2>...]) ... ]

With the LOB clause, you can specify a single LOB, or several/all LOBs in your table. The only drawback to specifying more than one LOB within a single LOB clause is that you cannot name the LOB segments. Where it becomes useful is when you need to specify the same storage clause or other attributes for all LOBs.

Using the LOB clause, we can now created our test_lob using a storage clause, tablespace, and other attributes as follows:


DROP TABLE test_lob; Table dropped. CREATE TABLE test_lob ( id NUMBER , xml_file CLOB , image BLOB , log_file BFILE ) LOB (xml_file) STORE AS xml_file_lob_seg ( TABLESPACE lob_data CHUNK 4096 CACHE STORAGE (MINEXTENTS 2) INDEX xml_file_lob_idx ( TABLESPACE lob_index STORAGE (MAXEXTENTS UNLIMITED) ) ) LOB (image) STORE AS image_lob_seg ( TABLESPACE lob_data ENABLE STORAGE IN ROW CHUNK 4096 CACHE STORAGE (MINEXTENTS 2) INDEX image_lob_idx ( TABLESPACE lob_index ) ); Table created.

Now lets run the same queries against user_lobs and user_segments:


SELECT table_name "Table" , column_name "Column" , segment_name "Segment" , index_name "Index" FROM user_lobs WHERE table_name = 'TEST_LOB'; Table Column Segment Index --------------- ------------------ -------------------------- -------------------------- TEST_LOB XML_FILE XML_FILE_LOB_SEG XML_FILE_LOB_IDX TEST_LOB IMAGE IMAGE_LOB_SEG IMAGE_LOB_IDX ========================================================================================= SELECT l.table_name "Table" , segment_name "Segment" , s.segment_type "Type" , s.tablespace_name "Tablespace" FROM user_segments s INNER JOIN user_lobs l USING (segment_name) WHERE segment_name like 'XML_%' OR segment_name like 'IMAGE_%' ORDER BY l.table_name , segment_name; Table Segment Type Tablespace --------------- -------------------------- ------------------ ------------------ TEST_LOB IMAGE_LOB_SEG LOBSEGMENT LOB_DATA TEST_LOB XML_FILE_LOB_SEG LOBSEGMENT LOB_DATA

Specifying Multiple LOB Columns in LOB Clause

Remember that it is possible to specify multiple LOB columns within a single LOB clause. The only drawback is that you cannot name the LOB segment or the LOB index. They will default to SYS_LOBxxxx and SYS_ILxxxx respectively.


CREATE TABLE test_lob ( id NUMBER , xml_file CLOB , image BLOB , log_file BFILE ) LOB (xml_file, image) STORE AS ( TABLESPACE lob_data CHUNK 4096 CACHE STORAGE (MINEXTENTS 2) INDEX ( TABLESPACE lob_index STORAGE (MAXEXTENTS UNLIMITED) ) ); Table created.

Altering LOB Columns in a Table

Once you have created a table containing LOB columns, it is possible to alter some parts of its definitions. Two important definitions that cannot be changed is the CHUNK size and the settings for ENABLE | DISABLE STORAGE IN ROW. Although, if you are moving the table using the MOVE method, it is possible to change these definitions.

Values that can be changes are PCTVERSION, CACHE, and LOGGING options as well as any of the storage clause values.

Most often though, the changes that you will need to make is to change tablespace information. For this method, simply use the MOVE clause of the ALTER TABLE... statement.

The examples below demonstrate how to use the ALTER STATEMENT to add LOB columns, modify an existing LOB column, and to change tablespace information.

 

If you want to make no other changes to the table containing a lob other than to rebuild it, use:

ALTER TABLE <table_name> MOVE;

This will rebuild the table segment. It does NOT affect any of the lob segments associated with the lob columns which is the desired optimization. If you want to change one or more of the physical attibutes of the table containing the lob, however no attributes of the lob columns are to be changed, use the following syntax:

ALTER TABLE <table_name>
MOVE TABLESPACE <new_tbsp>
STORAGE(new_storage);

This will rebuild the table segment. It does NOT rebuild any of the lob segments associated with the lob columns which is the desired optimization.

Here is the general syntax for the ALTER STATEMENT statement:


ALTER TABLE <table name> ADD (<lobcol> <LOBTYPE> <LOB_clause_same_as_for_create>) | MODIFY LOB (<lobcol>) ( [PCTVERSION <version_number>] [ { CACHE | NO CACHE [{LOGGING | NOLOGGING}] | CACHE READS [{LOGGING | NOLOGGING}] } ] ) | MOVE [ONLINE] [<physical_attributes>] [TABLESPACE <tablespace_name>] [LOGGING | NOLOGGING] [<LOB_clause_same_as_for_create>]

Add a New LOB Column


ALTER TABLE test_lob ADD (image2 BLOB) LOB (image2) STORE AS image2_lob_seg ( TABLESPACE lob_data CHUNK 4096 PCTVERSION 5 ENABLE STORAGE IN ROW INDEX image2_lob_idx ( TABLESPACE lob_index ) ); Table altered.

Alter an Existing LOB Column


ALTER TABLE test_lob MODIFY LOB (image) ( STORAGE (NEXT 1M) CACHE ); Table altered.

Change Tablespace Information

In the following example, the ALTER TABLE statement will MOVE the LOB segment and LOB index to the tbalespace lob_data2. This statement will also rebuild the table segment but will not change any of the table's physical attributes and it will rebuild it in its original tablespace, users in our example.


ALTER TABLE test_lob MOVE LOB(image) STORE AS ( TABLESPACE lob_data2 ); Table altered. ========================================================================================= SELECT l.table_name "Table" , segment_name "Segment" , s.segment_type "Type" , s.tablespace_name "Tablespace" FROM user_segments s INNER JOIN user_lobs l USING (segment_name) WHERE l.table_name = 'TEST_LOB' ORDER BY l.table_name , segment_name; Table Segment Type Tablespace --------------- -------------------------- ------------------ ------------------ TEST_LOB IMAGE2_LOB_SEG LOBSEGMENT LOB_DATA TEST_LOB IMAGE_LOB_SEG LOBSEGMENT LOB_DATA2 TEST_LOB XML_FILE_LOB_SEG LOBSEGMENT LOB_DATA

Change Tablespace Information For Both Table Segment and LOB Segment

In the following example, the ALTER TABLE statement will MOVE the LOB segment and LOB index to the tbalespace lob_data2 as well as change the tablespace for the table segment during its rebuild to the userss tablespace.


ALTER TABLE test_lob MOVE TABLESPACE users2 LOB (xml_file) STORE AS ( TABLESPACE lob_data2 ); Table altered. ========================================================================================= SELECT l.table_name "Table" , segment_name "Segment" , s.segment_type "Type" , s.tablespace_name "Tablespace" FROM user_segments s INNER JOIN user_lobs l USING (segment_name) WHERE l.table_name = 'TEST_LOB' ORDER BY l.table_name , segment_name; Table Segment Type Tablespace --------------- -------------------------- ------------------ ------------------ TEST_LOB IMAGE2_LOB_SEG LOBSEGMENT LOB_DATA TEST_LOB IMAGE_LOB_SEG LOBSEGMENT LOB_DATA2 TEST_LOB XML_FILE_LOB_SEG LOBSEGMENT LOB_DATA2

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Sunday, 11-Mar-2012 17:51:57 EDT
Page Count: 232928