DDL Commands for LOBs

最近遇到log字段的问题，查阅资源看到这篇文章不错，转载过来以学习。

文章链接地址：http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml

Contents

1. Creating Tables with LOB Columns
2. Altering LOB Columns in a Table

---

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_lobtable that creates a BLOB, CLOB, and BFILE column:

CREATE TABLE test_lobtable (
    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_lobtable created above:

SELECT
    table_name "Table"
  , column_name "Column"
  , segment_name "Segment"
  , index_name "Index"
FROM user_lobs
WHERE table_name = 'TEST_LOBTABLE';


Table         Column   Segment                   Index
------------- -------- ------------------------- ------------------------
TEST_LOBTABLE XML_FILE SYS_LOB0000149826C00002$$ SYS_IL0000149826C00002$$
TEST_LOBTABLE IMAGE    SYS_LOB0000149826C00003$$ SYS_IL0000149826C00003$$

Another view of interest are USER_SEGMENTS:

SELECT
    segment_name "Segment"
  , segment_type "Type"
  , tablespace_name "Tablespace"
FROM user_segments
WHERE segment_name like 'SYS_LOB%'
   OR segment_name like 'SYS_IL%';


Segment                   Type        Tablespace
------------------------- ----------- ----------
SYS_IL0000149826C00002$$  LOBINDEX    USERS
SYS_IL0000149826C00003$$  LOBINDEX    USERS
SYS_LOB0000149826C00002$$ LOBSEGMENT  USERS
SYS_LOB0000149826C00003$$ 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_lobtable using a storage clause, tablespace, and other attributes as follows:

CREATE TABLE test_lobtable (
      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
        )
    )
/

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_LOBTABLE';


Table          Column    Segment           Index
-------------- --------- ----------------- -----------------
TEST_LOBTABLE  XML_FILE  XML_FILE_LOB_SEG  XML_FILE_LOB_IDX
TEST_LOBTABLE  IMAGE     IMAGE_LOB_SEG     IMAGE_LOB_IDX



SELECT
    segment_name "Segment"
  , segment_type "Type"
  , tablespace_name "Tablespace"
FROM user_segments
WHERE segment_name like 'XML_%'
   OR segment_name like 'IMAGE_%'
ORDER BY segment_name DESC;


Segment            TS Type      Tablespace
------------------ ------------ -----------
XML_FILE_LOB_SEG   LOBSEGMENT   LOB_DATA
XML_FILE_LOB_IDX   LOBINDEX     LOB_DATA
IMAGE_LOB_SEG      LOBSEGMENT   LOB_DATA
IMAGE_LOB_IDX      LOBINDEX     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_lobtable (
      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)
        )
    )
/

---

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.

NOTE: 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_lobtable
    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_lobtable
    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_lobtable
    MOVE LOB(image) STORE AS (
        TABLESPACE lob_data2
    )
/

Table altered.


SELECT
    segment_name "Segment"
  , segment_type "Type"
  , tablespace_name "Tablespace"
FROM user_segments
ORDER BY segment_name DESC;

Segment                   TS Type      Tablespace
------------------------- ------------ ------------------------------
XML_FILE_LOB_SEG          LOBSEGMENT   LOB_DATA
XML_FILE_LOB_IDX          LOBINDEX     LOB_DATA
TEST_LOBTABLE             TABLE        USERS
IMAGE_LOB_SEG             LOBSEGMENT   LOB_DATA2
IMAGE_LOB_IDX             LOBINDEX     LOB_DATA2
IMAGE2_LOB_SEG            LOBSEGMENT   LOB_DATA
IMAGE2_LOB_IDX            LOBINDEX     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_lobtable
    MOVE TABLESPACE users2
    LOB (xml_file) STORE AS (
        TABLESPACE lob_data2
    )
/

Table altered.


SELECT
    segment_name "Segment"
  , segment_type "Type"
  , tablespace_name "Tablespace"
FROM user_segments
ORDER BY segment_name DESC;

Segment                   TS Type      Tablespace
------------------------- ------------ ------------------------------
XML_FILE_LOB_SEG          LOBSEGMENT   LOB_DATA2
XML_FILE_LOB_IDX          LOBINDEX     LOB_DATA2
TEST_LOBTABLE             TABLE        USERS2
IMAGE_LOB_SEG             LOBSEGMENT   LOB_DATA2
IMAGE_LOB_IDX             LOBINDEX     LOB_DATA2
IMAGE2_LOB_SEG            LOBSEGMENT   LOB_DATA
IMAGE2_LOB_IDX            LOBINDEX     LOB_DATA