/
261
CHAPTER 11 ■ LARGE OBJECTS
You can confirm that the table has been redefined via this query:
SQL> select table_name, column_name, securefile from user_lobs;
Here’s the output for this example:
TABLE_NAME COLUMN_NAME SECUREFILE
-------------------- -------------------- --------------------
PATCHMAIN_NEW PATCH_DESC NO
PATCHMAIN PATCH_DESC YES
Viewing LOB Metadata
You can use any of the DBA/ALL/USER_LOBS views to display information about LOBs in your database: select
table_name
,column_name
,index_name
,tablespace_name
from all_lobs
order by table_name;
Table 11–5 describes the columns available with DBA/ALL_LOBS:
Table 11–5.
ALL_LOBS Column Descriptions
Column Description
OWNER
Owner of the object that contains the LOB.
TABLE_NAME
Table name that contains the LOB.
COLUMN_NAME
Column name of the LOB.
SEGMENT_NAME
Segment name of the LOB.
TABLESPACE_NAME
Tablespace name that contains the LOB.
INDEX_NAME
Index name of the LOB.
CHUNK
Size in bytes of the LOB chunk.
PCTVERSION
Maximum percentage of LOB space used for versioning.
RETENTION
Maximum time duration for versioning of LOB space.
FREEPOOLS
Number of free pools for the LOB.
CACHE
YES indicates that the LOB data is placed in the buffer cache. NO indicates that the LOB data isn’t placed in the buffer cache. CACHEREADS indicates that the LOB is brought into the cache for read operations only.
LOGGING
Whether changes to the LOB are logged in the redo stream.
262
CHAPTER 11 ■ LARGE OBJECTS
Column Description
ENCRYPT
Whether the LOB is encrypted.
COMPRESSION
Degree of compression,
DEDUPLICATION
Whether deduplication is used for the LOB.
IN_ROW
Whether some of the LOB is stored in line with the row.
FORMAT
Whether the LOB storage format depends on the endianness of the platform.
PARTITIONED
Whether the LOB is in a partitioned table.
SECUREFILE
Whether the LOB architecture is SecureFile.
SEGMENT_CREATED
Whether the LOB segment has been created.
Also keep in mind that a LOB segment has a corresponding index segment. Thus you can query both the segment and the index in the DBA/ALL/USER_SEGMENTS views for LOB information: select
segment_name
,segment_type
,tablespace_name
from user_segments
where segment_name like 'SYS_LOB%'
or segment_name like 'SYS_IL%';
Loading LOBs
Loading LOB data isn’t typically the DBA’s job, but you should be familiar with techniques used to populate LOB columns. Developers may come to you for help with troubleshooting, performance, or space-related issues.
Loading a CLOB
First, create an Oracle database directory object that points to the operating system directory in which the CLOB file is stored. This directory object is used when loading the CLOB. In this example, the Oracle directory object is named LOAD_LOB and the operating system directory is /home/oracle/scripts: SQL> create or replace directory load_lob as '/home/oracle/scripts'; For reference, listed next is the DDL used to create the table in which the CLOB file is loaded: create table patchmain(
patch_id number primary key
,patch_desc clob
,patch_file blob)
lob(patch_desc, patch_file)
store as securefile (compress low) tablespace lob_data;
263
CHAPTER 11 ■ LARGE OBJECTS
This example also uses a sequence named PATCH_SEQ. Here’s the sequence-creation script: SQL> create sequence patch_seq;
The following bit of code uses the DBMS_LOB package to load a text file (named patch.txt) into a CLOB
column. In this example, the table name is PATCHMAIN and the CLOB column is PATCH_DESC: declare
src_clb bfile; -- point to source CLOB on file system
dst_clb clob; -- destination CLOB in table
src_doc_name varchar2(300) := 'patch.txt';
src_offset integer := 1; -- where to start in the source CLOB
dst_offset integer := 1; -- where to start in the target CLOB
lang_ctx integer := dbms_lob.default_lang_ctx;
warning_msg number; -- returns warning value if bad chars
begin
src_clb := bfilename('LOAD_LOB',src_doc_name); -- assign pointer to file
--
insert into patchmain(patch_id, patch_desc) -- create LOB placeholder values(patch_seq.nextval, empty_clob())
returning patch_desc into dst_clb;
--
dbms_lob.open(src_clb, dbms_lob.lob_readonly); -- open file
--
-- load the file into the LOB
dbms_lob.loadclobfromfile(
dest_lob => dst_clb,
src_bfile => src_clb,
amount => dbms_lob.lobmaxsize,
dest_offset => dst_offset,
src_offset => src_offset,
bfile_csid => dbms_lob.default_csid,
lang_context => lang_ctx,
warning => warning_msg
);
dbms_lob.close(src_clb); -- close file
--
dbms_output.put_line('Wrote CLOB: ' || src_doc_name);
end;
/
You can place this code in a file and execute it from the SQL command prompt. In this example, the file that contains the code is named clob.sql:
SQL> set serverout on size 1000000
SQL> @clob.sql
Here’s the expected output:
Wrote CLOB: patch.txt
PL/SQL procedure successfully completed.
264
CHAPTER 11 ■ LARGE OBJECTS
Loading a BLOB
Loading a BLOB is similar to loading a CLOB. This example uses the same directory object, table, and sequence from the previous example, which loaded a CLOB. Loading a BLOB is simpler than loading a CLOB
because you don’t have to specify character-set information.
This example loads a file named patch.zip into the PATCH_FILE BLOB column: declare
src_blb bfile; -- point to source BLOB on file system
dst_blb blob; -- destination BLOB in table
src_doc_name varchar2(300) := 'patch.zip';
src_offset integer := 1; -- where to start in the source BLOB
dst_offset integer := 1; -- where to start in the target BLOB
begin
src_blb := bfilename('LOAD_LOB',src_doc_name); -- assign pointer to file
--
insert into patchmain(patch_id, patch_file)
values(patch_seq.nextval, empty_blob())
returning patch_file into dst_blb; -- create LOB placeholder column first dbms_lob.open(src_blb, dbms_lob.lob_readonly);
--
dbms_lob.loadblobfromfile(
dest_lob => dst_blb,
src_bfile => src_blb,
amount => dbms_lob.lobmaxsize,
dest_offset => dst_offset,
src_offset => src_offset
);
dbms_lob.close(src_blb);
dbms_output.put_line('Wrote BLOB: ' || src_doc_name);
end;
/
You can place this code in a file and run it from the SQL command prompt. In this example, the file that contains the code is named blob.sql:
SQL> set serverout on size 1000000
SQL> @blob.sql
Here’s the expected output:
Wrote BLOB: patch.zip
PL/SQL procedure successfully completed.
Measuring LOB Space Consumed
As mentioned previously in this chapter, a LOB consists of an in-row lob locator, a LOB index, and a LOB
segment that consists of one or more chunks. The space used by the LOB index is usually negligible compared to the space used by the LOB segment. You can view the space consumed by a segment by querying the BYTES column of DBA/ALL/USER_SEGMENTS (just like any other segment in the database).
Here’s a sample query:
select
segment_name
265
CHAPTER 11 ■ LARGE OBJECTS
,segment_type
,segment_subtype
,bytes/1024/1024 meg_bytes
from user_segments;
You can modify the query to specifically report on only LOBs by joining to the USER_LOBS view: select
a.table_name
,a.column_name
,a.segment_name
,a.index_name
,b.bytes/1024/1024 meg_bytes
from user_lobs a
,user_segments b
where a.segment_name = b.segment_name;
You can use the DBMS_SPACE.SPACE_USAGE package and procedure to report on the blocks being used by a LOB. This package only works on objects that have been created in an ASSM-managed tablespace.
There are two different forms of the SPACE_USAGE procedure: one form reports on BasicFile LOBs, and the other form reports on SecureFile LOBs.
BasicFile Space Used
Here’s an example of how to call DBMS_SPACE.SPACE_USAGE for a BasicFile LOB: declare
p_fs1_bytes number;
p_fs2_bytes number;
p_fs3_bytes number;
p_fs4_bytes number;
p_fs1_blocks number;
p_fs2_blocks number;
p_fs3_blocks number;
p_fs4_blocks number;
p_full_bytes number;
p_full_blocks number;
p_unformatted_bytes number;
p_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'SYS_LOB0000024082C00002$$',
segment_type => 'LOB',
fs1_bytes => p_fs1_bytes,
fs1_blocks => p_fs1_blocks,
fs2_bytes => p_fs2_bytes,
fs2_blocks => p_fs2_blocks,
fs3_bytes => p_fs3_bytes,
fs3_blocks => p_fs3_blocks,
fs4_bytes => p_fs4_bytes,
fs4_blocks => p_fs4_blocks,
full_bytes => p_full_bytes,
full_blocks => p_full_blocks,
266
CHAPTER 11 ■ LARGE OBJECTS
unformatted_blocks => p_unformatted_blocks,
unformatted_bytes => p_unformatted_bytes
);
dbms_output.put_line('Full bytes = '||p_full_bytes);
dbms_output.put_line('Full blocks = '||p_full_blocks);
dbms_output.put_line('UF bytes = '||p_unformatted_bytes);
dbms_output.put_line('UF blocks = '||p_unformatted_blocks);
end;
/
In this PL/SQL, you need to modify the code so that it reports on the LOB segment in your environment.
SecureFile Space Used
Here’s an example of how to call DBMS_SPACE.SPACE_USAGE for a SecureFile LOB: DECLARE
l_segment_owner varchar2(40);
l_table_name varchar2(40);
l_segment_name varchar2(40);
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;
--
CURSOR c1 IS
SELECT owner, table_name, segment_name
FROM dba_lobs
WHERE table_name = 'PATCHMAIN2';
BEGIN
FOR r1 IN c1 LOOP
l_segment_owner := r1.owner;
l_table_name := r1.table_name;
l_segment_name := r1.segment_name;
--
dbms_output.put_line('-----------------------------');
dbms_output.put_line('Table Name : ' || l_table_name);
dbms_output.put_line('Segment Name : ' || l_segment_name);
--
dbms_space.space_usage(
segment_owner => l_segment_owner,
segment_name => l_segment_name,
segment_type => 'LOB',
partition_name => NULL,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
267
CHAPTER 11 ■ LARGE OBJECTS
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
--
dbms_output.put_line('segment_size_blocks: '|| l_segment_size_blocks); dbms_output.put_line('segment_size_bytes : '|| l_segment_size_bytes); dbms_output.put_line('used_blocks : '|| l_used_blocks);
dbms_output.put_line('used_bytes : '|| l_used_bytes);
dbms_output.put_line('expired_blocks : '|| l_expired_blocks);
dbms_output.put_line('expired_bytes : '|| l_expired_bytes);
dbms_output.put_line('unexpired_blocks : '|| l_unexpired_blocks);
dbms_output.put_line('unexpired_bytes : '|| l_unexpired_bytes);
END LOOP;
END;
/
Again, in this PL/SQL, you need to modify the code so that it reports on the table with the LOB
segment in your environment.
Summary
Oracle lets you store large objects in databases via various LOB data types. LOBs facilitate the storage, management, and retrieval of video clips, images, movies, word-processing documents, large text files, and so on. Oracle can store these files in the database and thus provide backup and recovery and security protection (just as it does for any other data type). Oracle’s CLOB data type is used to store large character text files that exceed 4000 characters (the maximum length of a VARCHAR2 column). BLOBs are used to store binary files such as images (JPEG or MPEG), movie files, sound files, and so on. If it’s not feasible to store the file in the database, you can use a BFILE LOB.
Oracle provides two underlying architectures for LOBS: BasicFile and SecureFile. BasicFile is the LOB architecture that has been available since Oracle version 8. The SecureFile feature was introduced in Oracle Database 11
g
. SecureFile has many advanced options such as compression, deduplication, and encryption (these specific features require an extra license from Oracle).