DBA Tips Archive for Oracle
No Title[an error occurred while processing this directive]
by Michael New, MichaelNew@earthlink.net, Gradation LLC
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
is a hexadecimal number. The default name of the LOB index is
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
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:
Another view of interest is
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
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
a storage clause, tablespace, and other attributes as follows:
Now lets run the same queries against
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
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
Values that can be changes are
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
to add LOB columns, modify an existing LOB column, and to change tablespace
Here is the general syntax for the
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.
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.
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-2019 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 email@example.com.
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: 238166