Database Reference
In-Depth Information
The extended datatype is internally implemented as a LOB. Assuming that the
T
table is created in a schema not
containing any other objects, you'll get the following when querying
USER_OBJECTS
:
EODA@O12CE> select object_name, object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
---------------------------- ---------------
SYS_LOB0000019479C00001$$ LOB
SYS_IL0000019479C00001$$ INDEX
T TABLE
You can further verify the LOB segment details by querying
USER_LOBS
:
EODA@O12CE> select table_name, column_name, segment_name, tablespace_name, in_row
2 from user_lobs where table_name='T';
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME IN_
----------- ------------ ---------------------------- ------------------ ---
T ET SYS_LOB0000019479C00001$$ USERS YES
You have no direct control over the LOB associated with the extended column. This means that you cannot
manipulate the underlying LOB column with the
DBMS_LOB
package. Also, the internal LOB associated with the
extended datatype column is not visible to you via
DBA_TAB_COLUMNS
or
COL$
.
The LOB segment and associated LOB index are always stored in the tablespace of the table that the extended
datatype was created in. Following normal LOB storage rules, Oracle stores the first 4,000 bytes inline within the table.
Anything greater than 4,000 bytes is stored in the LOB segment. If the tablespace that the LOB is created in is using
Automatic Segment Space Management (ASSM) then the LOB is created as a SecureFiles LOB, otherwise it is created
as a BasicFiles LOB.
■
see the “LOB types” section later in this chapter for a discussion on in row storage and the technical aspects
of secureFiles and BasicFiles.
Note
Your SQL access to any data stored in the extended-column LOB segment is transparently handled by Oracle.
This has some interesting implications. For example, you can successfully select data stored in an extended column via
a database link. This bit of code selects (via a database link) from a table named
T
in a remote database named
O12CE
:
EODA@ORA12CR1> select substr(et, 9000,10) from t@O12CE;
SUBSTR(ET,9000,10)
----------------------------------------
cabcabcabc
Why is that important? Consider what happens when a table is created in the remote O12CE database with a
column defined with a LOB datatype:
EODA@O12CE> create table c(ct clob);
Table created.
