Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Managing LOBs , 10 of 12
LOB columns are subject to the following restrictions:
SELECT
or WHERE
clauses of queries or in functions of the DBMS_LOB
package.
The following syntax is not supported for LOBs:
SELECT lobcol FROM table1@remote_site; INSERT INTO lobtable SELECT type1.lobattr FROM table1@remote_ site; SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;
However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:
CREATE TABLE t AS SELECT * FROM table1@remote_site; INSERT INTO t SELECT * FROM table1@remote_site; UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site); INSERT INTO table1@remote_site ... UPDATE table1@remote_site ... DELETE table1@remote_site ...
For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or DBMS_LOB
APIs on LOBs are not supported. For example, the following statement is supported:
CREATE TABLE AS SELECT clob_col FROM tab@dbs2;
However, the following statement is not supported:
CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2;
ORDER
BY
clause of a query, or in the GROUP
BY
clause of a query or in an aggregate function.
SELECT
... DISTINCT
or SELECT
... UNIQUE
statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT
... DISTINCT
statement or in a query that uses the UNION
or MINUS
set operator if the column's object type has a MAP
or ORDER
function defined on it.
NCLOB
as an attribute of an object type when creating a table. However, you can specify NCLOB
parameters in methods.
ANALYZE
... COMPUTE
or ANALYZE
... ESTIMATE
statements.
AUTO
segment-managed tablespaces.
BEFORE
ROW
DML trigger, you can read the :old
value of the LOB, but you cannot read the :new
value. However, for AFTER
ROW
and INSTEAD
OF
DML triggers, you can read both the :new
and :old
values.
UPDATE
DML trigger on a LOB column.
See Oracle9i Data Cartridge Developer's Guide for more information about defining triggers on domain indexes
See Also:
INSERT
or UPDATE
operation, you can bind data of any size to a LOB column, but you cannot bind data to a LOB attribute of an object type. In an INSERT
... AS
SELECT
operation, you can bind up to 4000 bytes of data to LOB columns.
See Also:
LONG
and LOB columns, you cannot bind more than 4000 bytes of data to both the LONG
and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG
or the LOB column.
The first extent of any segment requries at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, it will still work because it is possible for segments in permanent dictionary managed tablespaces to override tablespaces' default storage setting.
But if uniform locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and tablespaces' default storage setting is not ignored.
You will get a message on trying to create the LOB segment: ORA-3237 "initial extent of specified size cannot be allocated". You could be confused about this especially if your tablespace has lots of free space!
Notes:
|
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|