最近遇到log字段的问题,查阅资源看到这篇文章不错,转载过来以学习。
文章链接地址:http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml
Contents
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
, wherexxxx
is a hexadecimal number. The default name of the LOB index isSYS_ILxxx
wherexxxx
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 thetest_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 areUSER_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 ExampleWe 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 theLOB clause
of theCREATE TABLE...
statement. Although, with Oracle8i, the ability to specify details for the LOB index have been deprecated. Here is the general syntax for theCREATE 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 againstuser_lobs
anduser_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 ClauseRemember 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
andSYS_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 theCHUNK
size and the settings forENABLE | DISABLE STORAGE IN ROW
. Although, if you are moving the table using theMOVE
method, it is possible to change these definitions.Values that can be changes are
PCTVERSION
,CACHE
, andLOGGING
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 theALTER 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