Pro Oracle Database 11g Administration (45 page)
ALWAYS
The LOB is created as a SecureFile type unless the underlying tablespace isn’t using ASSM.
IGNORE
The SECUREFILE option is ignored, along with any SecureFile settings.
247
CHAPTER 11 ■ LARGE OBJECTS
Creating a Table with a LOB Column
By default, when you create a LOB, it’s a BasicFile LOB. The next subsection covers BasicFiles and is followed by a subsection on creating a SecureFile LOB. If you’re using Oracle Database 11
g
and higher, I recommend that you always create a LOB as a SecureFile LOB. As discussed previously, SecureFiles allow you to use features such as compression and encryption.
Creating a BasicFile LOB Column
To create a LOB column, you have to specify a LOB data type. Listed next is a basic example of creating a table with a CLOB data type:
create table patchmain(
patch_id number
,patch_desc clob);
When you create a table with a LOB column, you must be aware of some technical underpinnings.
Review the following bulleted list and be sure you understand each point:
• LOBs by default are created as BasicFiles.
• Oracle creates a LOB segment and a LOB index for each LOB column.
• The LOB segment has a name of this format: SYS_LOB
• The LOB index has a name of this format: SYS_IL
• The
• The LOB segment and index are created in the same tablespace as the table unless you specify a different tablespace.
• By default, nearly 4000 bytes of a LOB are stored in the table row (inline).
• With Oracle Database 11
g
release 2 and higher, a LOB segment and a LOB index aren’t created until a record is inserted into the table (the so-called
deferred
segment creation
feature). This means DBA/ALL/USER_SEGMENTS and DBA/ALL/USER_EXTENTS have no information in them until a row is inserted into the table.
Oracle creates a LOB segment and a LOB index for each LOB column. The LOB segment stores the data. The LOB index keeps track of where the chunks of LOB data are physically stored and in what order the LOB chunks should be accessed.
You can query the DBA/ALL/USER_LOBS view to display the LOB segment and LOB index names: select
table_name
,segment_name
,index_name
,securefile
,in_row
from user_lobs;
248
CHAPTER 11 ■ LARGE OBJECTS
Here’s the output for this example:
TABLE_NAME SEGMENT_NAME INDEX_NAME SECUREFILE IN_ROW
------------ ------------------------- ------------------------- ---------- ------
PATCHMAIN SYS_LOB0000024169C00002$$ SYS_IL0000024169C00002$$ NO YES
You can also query DBA/USER/ALL_SEGMENTS to view information regarding LOB segments. As mentioned earlier, if you create a table in Oracle Database 11
g
release 2 and higher, an initial segment isn’t created until you insert a row into the table (deferred segment creation). This can be confusing because you may expect a row to be present in DBA/ALL/USER_SEGMENTS immediately after you create the table:
select
segment_name
,segment_type
,segment_subtype
,bytes/1024/1024 meg_bytes
from user_segments
where segment_name IN ('&&table_just_created',
'&&lob_segment_just_created',
'&&lob_index_just_created');
The prior query prompts for the segment names. The output shows no rows: no rows selected
Next, insert a record into the table that contains the LOB column:
SQL> insert into patchmain values(1,'clob text');
Rerunning the query against USER_SEGMENTS shows that three segments have been created—one for the table, one for the LOB segment, and one for the LOB index:
SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU MEG_BYTES
------------------------------ --------------- ---------- ----------
PATCHMAIN TABLE ASSM .0625
SYS_IL0000024169C00002$$ LOBINDEX ASSM .0625
SYS_LOB0000024169C00002$$ LOBSEGMENT ASSM .0625
Creating a LOB in a Specific Tablespace
By default, the LOB segment is stored in the same tablespace as its table. You can specify a separate tablespace for a LOB segment by using the LOB...STORE AS clause of the CREATE TABLE statement. The next table-creation script creates the table in a tablespace, and creates separate tablespaces for the CLOB
and BLOB columns:
create table patchmain
(patch_id number
,patch_desc clob
,patch blob
) tablespace users
lob (patch_desc) store as (tablespace clob_data)
,lob (patch) store as (tablespace blob_data);
You need to modify this query so that the tablespace names match your environment (or you can explicitly create CLOB_DATA and BLOB_DATA tablespaces). The following query verifies that three tablespaces are utilized for this table:
249
CHAPTER 11 ■ LARGE OBJECTS
select table_name, tablespace_name, 'N/A' column_name
from user_tables
where table_name='PATCHMAIN'
union
select table_name, tablespace_name, column_name
from user_lobs
where table_name='PATCHMAIN';
Here’s the output:
TABLE_NAME TABLESPACE_NAME COLUMN_NAME
-------------------- -------------------- --------------------
PATCHMAIN BLOB_DATA PATCH
PATCHMAIN CLOB_DATA PATCH_DESC
PATCHMAIN USERS N/A
I recommend that you always create a LOB with its storage specified in a separate tablespace from the table data. This is because LOBs have different growth patterns and require different storage characteristics (than the table data).
Creating a SecureFile LOB Column
If you don’t specify the SECUREFILE clause when creating a table with a LOB column, then by default the LOB is created as a BasicFile LOB. This next example shows how to create a SecureFile LOB and place it in a tablespace separate from the table. As mentioned earlier, the tablespace that contains the Securefile LOB must be an ASSM-managed tablespace:
create table patchmain(
patch_id number
,patch_desc clob)
lob(patch_desc) store as securefile (tablespace lob_data);
■
Tip
Oracle allows you to create a table with the STORE AS SECUREFILE clause in a non-ASSM tablespace.
However, if you attempt to insert data into this table, the following error is displayed: “ORA-43853: SECUREFILE
lobs can’t be used in non-ASSM tablespace.”
Before viewing the data dictionary, you can insert a record into the table to ensure that segment information is available (due to the deferred-segment-allocation feature in Oracle Database 11
g
release 2
and higher). For example:
SQL> insert into patchmain values(1,'clob text');
You can now verify a LOB’s architecture by querying the USER_SEGMENTS view: select
segment_name
,segment_type
,segment_subtype
from user_segments;
250
CHAPTER 11 ■ LARGE OBJECTS
Here’s some sample output indicating that a LOB segment is a SecureFile: SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU
-------------------------- -------------------- ----------
PATCHMAIN TABLE MSSM
SYS_IL0000023963C00002$$ LOBINDEX ASSM
SYS_LOB0000023963C00002$$ LOBSEGMENT SECUREFILE
You can also query the USER_LOBS view to verify the SecureFile LOB architecture: select
table_name
,segment_name
,index_name
,securefile
,in_row
from user_lobs;
Here’s the output:
TABLE_NAME SEGMENT_NAME INDEX_NAME SEC IN_
------------ -------------------------- ------------------------- --- ---
PATCHMAIN SYS_LOB0000023963C00002$$ SYS_IL0000023963C00002$$ YES YES
■
Note
With SecureFiles, you no longer need to specify the following options: CHUNK, PCTVERSION, FREEPOOLS, FREELIST, and FREELIST GROUPS.
Creating a Partitioned LOB
You can create a partitioned table that has a LOB column. Doing so lets you spread a LOB across multiple tablespaces. Such partitioning helps with balancing I/O, maintenance, and backup and recovery operations.
You can partition LOBs by RANGE, LIST, or HASH. The next example creates a LIST-partitioned table in which LOB column data is stored in tablespaces separate from the table data: CREATE TABLE patchmain(
patch_id NUMBER
,region VARCHAR2(16)
,patch_desc CLOB)
LOB(patch_desc) STORE AS (TABLESPACE patch1)
PARTITION BY LIST (REGION) (
PARTITION p1 VALUES ('EAST')
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch1 COMPRESS HIGH)
TABLESPACE inv_data1
,
PARTITION p2 VALUES ('WEST')
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch2 DEDUPLICATE NOCOMPRESS)
TABLESPACE inv_data2
,
251
CHAPTER 11 ■ LARGE OBJECTS
PARTITION p3 VALUES (DEFAULT)
LOB(patch_desc) STORE AS SECUREFILE
(TABLESPACE patch3 COMPRESS LOW)
TABLESPACE inv_data3
);
Notice that each LOB partition is created with its own storage options (the SecureFile features are covered a bit later in this chapter). You can view the details about the LOB partitions as shown: select
table_name
,column_name
,partition_name
,tablespace_name
,compression
,deduplication
from user_lob_partitions;
Here’s some sample output:
TABLE_NAME COLUMN_NAME PARTITION_ TABLESPACE_NAME COMPRE DEDUPLICATION
------------ --------------- ---------- --------------- ------ --------------
PATCHMAIN PATCH_DESC P1 PATCH1 HIGH NO
PATCHMAIN PATCH_DESC P2 PATCH2 NO LOB
PATCHMAIN PATCH_DESC P3 PATCH3 LOW NO
■
Tip
You can also view DBA/ALL_USER_PART_LOBS for information about partitioned LOBs.
You can change the storage characteristics of a partitioned LOB column after it’s been created. To do so, use the ALTER TABLE ... MODIFY PARTITION statement. This example alters a LOB partition to have a high degree of compression:
alter table patchmain modify partition p1
lob (patch_desc) (compress high);
The next example modifies a partitioned LOB so it doesn’t keep duplicate values (via the DEDUPLICATE clause):
alter table patchmain modify partition p2
lob (patch_desc) (deduplicate lob);
■
Note
Partitioning is an extra-cost option that is available only with the Oracle Enterprise Edition.
252
CHAPTER 11 ■ LARGE OBJECTS
Maintaining LOB Columns
The following sections describe some common maintenance tasks that are performed on LOB columns or that involve LOB columns. You learn to move columns between tablespaces, to add new LOB columns to a table, and so forth.
Moving a LOB Column
As mentioned previously, if you create a table with a LOB column and don’t specify a tablespace, then by default the LOB is created in the same tablespace as its table. This happens sometimes in environments where the DBAs don’t plan ahead very well; only after the LOB column has consumed large amounts of disk space does the DBA wonder why the table has grown so big.
You can use the ALTER TABLE...MOVE...STORE AS statement to move a LOB column to a separate tablespace (from the table’s tablespace). Here’s the basic syntax:
alter table
alter table patchmain
move lob(patch_desc)
store as basicfile (tablespace inv_clob);
You can verify that the LOB was moved by querying USER_LOBS:
SQL> select table_name, column_name, tablespace_name from user_lobs; If the LOB column is populated with large amounts of data, you almost always want to store the LOB
in a tablespace separate from the rest of the table data. In these scenarios, the LOB data has different growth and storage requirements and is best maintained in its own tablespace.
Adding a LOB Column
If you have an existing table to which you want to add a LOB column, use the ALTER TABLE...ADD
statement. The next statement adds the INV_IMAGE column to a table:
SQL> alter table inv add(inv_image blob);
This statement is fine for quickly adding a LOB column to a development environment. For anything else, you should specify the storage characteristics. For example, this specifies that a SecureFile LOB is created in the LOB_DATA tablespace:
alter table inv add(inv_image blob)
lob(inv_image) store as securefile(tablespace lob_data);
Removing a LOB Column
You may have a scenario where your business requirements change and you no longer need a column.
Before you remove a column, consider renaming it so that you can better identify whether any applications or users are still accessing it:
SQL> alter table patchmain rename column patch_desc to patch_desc_old; 253
CHAPTER 11 ■ LARGE OBJECTS
After you determine that nobody is using the column, use the ALTER TABLE...DROP statement to drop it:
SQL> alter table patchmain drop(patch_desc_old);
You can also remove a LOB column by dropping and re-creating a table (without the LOB column).
This of course permanently removes any data as well.
Also keep in mind that in Oracle Database 10
g
or higher, if your recycle bin is enabled, then when you don’t drop a table with the PURGE clause, space is still consumed by the dropped table. If you want to remove the space associated with the table, use the PURGE clause or purge the recycle bin after dropping the table.