MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle LOB Storage Syntax

with 4 comments

Somebody asked me to post some examples of Large Object syntax. I checked Chapter 9 of the Oracle Database SecureFiles and Large Object Developer’s Guide, and I agree an example or two would be helpful. I’ve provided CREATE and ALTER statements.

Here’s a sample CREATE TABLE statement for a CLOB, BLOB, and BFILE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
CREATE TABLE item
( item_id             NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_barcode        VARCHAR2(20)  CONSTRAINT nn_item_1  NOT NULL
, item_type           NUMBER        CONSTRAINT nn_item_2  NOT NULL
, item_title          VARCHAR2(60)  CONSTRAINT nn_item_3  NOT NULL
, item_subtitle       VARCHAR2(60) 
, item_desc           CLOB          CONSTRAINT nn_item_4  NOT NULL
, item_blob           BLOB
, item_photo          BFILE
, item_rating         VARCHAR2(8)   CONSTRAINT nn_item_5  NOT NULL
, item_rating_agency  VARCHAR2(4)   CONSTRAINT nn_item_6  NOT NULL
, item_release_date   DATE          CONSTRAINT nn_item_7  NOT NULL
, created_by          NUMBER        CONSTRAINT nn_item_8  NOT NULL
, creation_date       DATE          CONSTRAINT nn_item_9  NOT NULL
, last_updated_by     NUMBER        CONSTRAINT nn_item_10 NOT NULL
, last_update_date    DATE          CONSTRAINT nn_item_11 NOT NULL
, CONSTRAINT fk_item_1                      FOREIGN KEY(item_type)
  REFERENCES common_lookup(common_lookup_id)
, CONSTRAINT fk_item_2                      FOREIGN KEY(created_by)
  REFERENCES system_user(system_user_id)
, CONSTRAINT fk_item_3                      FOREIGN KEY(last_updated_by)
  REFERENCES system_user(system_user_id))
  LOB (item_desc) STORE AS BASICFILE item_desc
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645))  
, LOB (item_blob) STORE AS item_blob
  (TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
   PCTVERSION 10 NOCACHE LOGGING
   STORAGE (INITIAL 1048576
            NEXT 1048576
            MINEXTENTS 1
            MAXEXTENTS 2147483645));

If you attempt to make the CHUNK size greater than 32,768, it will raise an ORA-22851: invalid CHUNK LOB storage option value error.

Here’s a sample ALTER TABLE statement that adds a new large object column to a table. You can’t modify an existing large object column. You raise an ORA-22859: invalid modification of columns error when you try it. If you need to make modifications, check this post form last year on re-organizing LOB indexes.

1
2
3
4
5
6
7
8
ALTER TABLE item ADD (another CLOB)
LOB (another) STORE AS BASICFILE item_clob
(TABLESPACE users ENABLE STORAGE IN ROW CHUNK 32768
 PCTVERSION 10 NOCACHE LOGGING
 STORAGE (INITIAL 1048576
          NEXT 1048576
          MINEXTENTS 1
          MAXEXTENTS 2147483645));

This all makes sense, but hold on there’s a potential problem when you add a large object column to a table. I’m not sure it’s a bug but I’ll probably open a TAR on it later in the week (arghhhh! I once worked in Oracle Support. :-)). Anyway, here’s what I found:

If you drop and recreate the table, you can encounter an ORA-01430 error. It appears that the large object is really hanging out in the catalog. As soon as you try to re-add it, you get the error.

If you describe the table after recreating it, you’ll see the following:

SQL> DESCRIBE item
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ITEM_ID                                   NOT NULL NUMBER
 ITEM_BARCODE                              NOT NULL VARCHAR2(20)
 ITEM_TYPE                                 NOT NULL NUMBER
 ITEM_TITLE                                NOT NULL VARCHAR2(60)
 ITEM_SUBTITLE                                      VARCHAR2(60)
 ITEM_DESC                                 NOT NULL CLOB
 ITEM_BLOB                                          BLOB
 ITEM_PHOTO                                         BINARY FILE LOB
 ITEM_RATING                               NOT NULL VARCHAR2(8)
 ITEM_RATING_AGENCY                        NOT NULL VARCHAR2(4)
 ITEM_RELEASE_DATE                         NOT NULL DATE
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 LAST_UPDATED_BY                           NOT NULL NUMBER
 LAST_UPDATE_DATE                          NOT NULL DATE

Also, if you run the following query with the DBMS_METADATA package, the column doesn’t exist in the table defintion:

SELECT dbms_metadata.get_ddl('TABLE','ITEM') FROM dual;

If try to run the ALTER statement to add the column that doesn’t appear to exist, you’ll get the following message in Oracle 11.1.0.7.0:

ALTER TABLE item ADD (another CLOB)
                      *
ERROR at line 1:
ORA-01430: COLUMN being added already EXISTS IN TABLE

If you rerun the DBMS_METADATA.GET_DDL query, it’ll be there in the table definition. Also, in its non-existence but existent state (ghost-state), there’s nothing in the DBA_LOB, or DBA_SEGMENT views. You can find a query to check large object segments and indexes here. My questions is where has it gone, why is it there, and did I do something wrong because this looks like basic functionality.

If you know the mystery or the existence of a bug, please post a comment.

Written by maclochlainn

July 17th, 2009 at 12:40 am

Posted in clob,Oracle,sql

4 Responses to 'Oracle LOB Storage Syntax'

Subscribe to comments with RSS or TrackBack to 'Oracle LOB Storage Syntax'.

  1. I am seeing the same problem on our legacy Oracle 8 system and do not know how to recover. I would prefer not to restore from backup. Did you ever get any answers?

    Bonnie

    18 Apr 10 at 8:08 am

  2. Did you try this?

    maclochlainn

    18 Apr 10 at 9:38 am

  3. What is it’s use? How we can modify it?

    Archana

    17 Jan 16 at 11:36 am

  4. You can use it to modify how your objects are stored, generally by specifying the size of segments. All large objects are stored by segments.

    maclochlainn

    19 Jan 16 at 11:38 pm

Leave a Reply