Pro Oracle Database 11g Administration (47 page)

/

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).