Transcript Oracle Clustered File System (OCFS)

LOB Internals
Julian Dyke
Independent Consultant
Web Version - December 2008
1
© 2008 Julian Dyke
juliandyke.com
Objectives
2
1.
Understand how LOBs use storage in the
Oracle database
2.
Consider options to optimize LOB performance
in terms of:
 Reads
 Writes
 Impact on physical I/O
 Impact on buffer cache
© 2008 Julian Dyke
juliandyke.com
Agenda


3
© 2008 Julian Dyke
Basic Files
Secure Files
juliandyke.com
Basic
Files
4
© 2008 Julian Dyke
juliandyke.com
Basic Files
Overview
5

Introduced in Oracle 8.0
 Known as Basic Files in Oracle 11.1 and above

Intended to replace LONG columns

Can be used to store large blocks of unstructured data e.g.:
 text
 graphics
 video clips
 sound waveforms
 XML documents

Can store either character or binary data

Can be stored
 within database (internal)
 outside database (external)
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Types

6
There are four types of LOB:

BLOB - Binary Large Object
 Stored within database
 Contains raw data

CLOB - Character Large Object
 Stored within database
 Supports database character set

NCLOB - NLS Character Large Object
 Stored within database
 Supports NLS character set

BFILE - Binary File Large Object
 Stored in a binary file outside the database
 Pointed to by a file locator within the database
© 2008 Julian Dyke
juliandyke.com
Basic Files
Internal LOBs
7

Stored outside of database
 BLOB, CLOB or NCLOB

Maximum size is dependent on version and chunk size

Support features such as:
 concurrency
 redo logging and recovery
 transactions with commits and rollbacks

For each row with an internal LOB column, the LOB can:
 Be NULL
 Point to an empty BLOB, CLOB or NCLOB
 Point to a BLOB, CLOB or NCLOB value
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB columns versus LONG columns


8
LOBs are intended to replace LONG columns:
 Oracle recommends converting LONG columns to LOB columns
 LONG columns still used by many applications
 LONG columns still used in Oracle 11.1 data dictionary tables including:
 COL$, TABPART$, INDPART$, TRIGGER$, VIEW$ and CDEF$
 Therefore it is unlikely LONG columns will be desupported in the near
future
LONGs
 Single column per table
 Maximum size 2GB
 Data always stored in-line
 Cannot be an object attribute
 Cannot be partitioned
 Cannot be used in IOTs
 Cannot be replicated
 Access is sequential
 Partial PL/SQL support
© 2008 Julian Dyke

LOBs
 Multiple columns per table
 Maximum size 4GB or more
 Data stored in-line or out-of-line
 Can be an object attribute
 Can be partitioned
 Can be used in IOTs
 Can be replicated
 Access can be is random
 Full PL/SQL support
juliandyke.com
Basic Files
DBMS_LOB Package

BLOB and CLOB Subroutines
APPEND
 CLOSE
 COMPARE
 CONVERTTOBLOB
 CONVERTTOCLOB
 COPY
 CREATETEMPORARY
 ERASE
 FRAGMENT_DELETE (11.1+)
 FRAGMENT_INSERT (11.1+)
 FRAGMENT_MOVE (11.1+)
 FRAGMENT_REPLACE (11.1+)
 FREETEMPORARY
 GETCHUNKSIZE
 GET_DEDUPLICATE_REGIONS (11.1+)
 GETLENGTH

9
© 2008 Julian Dyke
GETOPTIONS (11.1+)
 GET_STORAGE_LIMIT
 INSTR ISSECUREFILE
 ISTEMPORARY
 ISOPEN
 LOADFROMFILE
 LOADBLOBFROMFILE
 LOADCLOBFROMFILE
 OPEN
 READ
 SETOPTIONS (11.1+)
 SUBSTR
 TRIM
 WRITE
 WRITEAPPEND

juliandyke.com
Basic Files
DBMS_LOB Package

BFILE Subroutines
CLOSE
 COMPARE
 FILECLOSE
 FILECLOSEALL
 FILEEXISTS
 FILEGETNAME
 FILEISOPEN
 FILEOPEN
 GETLENGTH
 INSTR
 ISOPEN
 OPEN
 READ
 SUBSTR

10
© 2008 Julian Dyke
juliandyke.com
Basic Files
DBA_LOBS View
11
Column Name
Data Type
OWNER
VARCHAR2(30)
TABLE_NAME
VARCHAR2(30)
COLUMN_NAME
VARCHAR2(4000)
SEGMENT_NAME
VARCHAR2(30)
TABLESPACE_NAME
VARCHAR2(30)
INDEX_NAME
VARCHAR2(30)
CHUNK
NUMBER
PCTVERSION
NUMBER
RETENTION
NUMBER
FREEPOOLS
NUMBER
CACHE
VARCHAR2(10)
LOGGING
VARCHAR2(7)
ENCRYPT
VARCHAR2(4)
Oracle 11.1 and above
COMPRESSION
VARCHAR2(6)
Oracle 11.1 and above
DEDUPLICATION
VARCHAR2(15)
Oracle 11.1 and above
IN_ROW
VARCHAR2(3)
FORMAT
VARCHAR2(15)
PARTITIONED
VARCHAR2(3)
SECUREFILE
VARCHAR2(3)
© 2008 Julian Dyke
Comments
Oracle 11.1 and above
juliandyke.com
Basic Files
LOB Objects

If a table includes an internal LOB column, the following objects will be
created
 LOB index
 LOB data

Each object has a separate segment

For example:
LOB
CREATE TABLE t1
(
c1 NUMBER,
c2 CLOB,
);
T1
TABLE
SYS_IL0000055604C00002$$
LOB
LOB INDEX
SYS_LOB0000055604C00002$$
LOB
LOB DATA
12
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Segments
13

Every LOB has a LOB data segment and a LOB index segment

LOB data segment is reported by DBA_OBJECTS
 Segment name is system generated e.g. SYS_LOB0000070479C00002$$
 where:
 0000070479 is the OBJECT_ID of the table
 00002 is the column number of the LOB column within the table

LOB index is not reported by DBA_OBJECTS
 Index is still defined in SYS.OBJ$ and SYS.IND$
 Index name is reported by DBA_LOBS e.g. SYS_IL0000070479C00002$$
 where:
 0000070479 is the OBJECT_ID of the table
 00002 is the column number of the LOB column within the table
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Objects

One LOB index segment and one LOB data segment will be created for each
LOB column

For example:
CREATE TABLE t2
(
c1 NUMBER,
c2 CLOB,
c3 CLOB
);
LOB
SYS_IL0000055607C00002$$
LOB
SYS_IL0000055607C00003$$
LOB
SYS_LOB0000055607C00002$$
LOB
LOB DATA
SYS_LOB0000055607C00003$$
LOB
LOB DATA
LOB INDEX
14
T2
TABLE
© 2008 Julian Dyke
LOB INDEX
juliandyke.com
Basic Files
Partitioned LOBs

Oracle supports partitioned and sub-partitioned LOBS in Oracle 8.1.5 and
above
 LOBs are supported in partitioned IOTs in Oracle 9.0.1 and above

LOB and and LOB index segments are equi-partitioned with base table

Partitioning key cannot contain a LOB column
CREATE TABLE t3
(
c1 NUMBER,
c2 CLOB
)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200)
);
15
© 2008 Julian Dyke
juliandyke.com
Basic Files
Partitioned LOBs
LOB
T3
TABLE
P1
TABLE PARTITION
P2
TABLE PARTITION
SYS_IL0000055612C00002$$
LOB
LOB INDEX
SYS_LOB_P23
INDEX PARTITION
SYS_LOB_P24
INDEX PARTITION
SYS_LOB0000055612C00002$$
LOB
LOB
SYS_LOB_P21
LOB PARTITION
16
© 2008 Julian Dyke
SYS_LOB_P22
LOB PARTITION
juliandyke.com
Basic Files
Sub Partitioned LOBs
CREATE TABLE t4
(
c1 NUMBER,
c2 NUMBER,
c3 CLOB
)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (100)
(
SUBPARTITION p1s1 VALUES LESS THAN
SUBPARTITION p1s2 VALUES LESS THAN
),
PARTITION p2 VALUES LESS THAN (200)
(
SUBPARTITION p2s1 VALUES LESS THAN
SUBPARTITION p2s2 VALUES LESS THAN
)
);
17
© 2008 Julian Dyke
(10),
(20)
(10),
(20)
juliandyke.com
Basic Files
Sub Partitioned LOBs
T4
TABLE
LOB
P1
TABLE PARTITION
P1S1
TABLE SUBPARTITION
P2
TABLE PARTITION
P1S2
TABLE SUBPARTITION
P2S1
TABLE SUBPARTITION
P2S2
TABLE SUBPARTITION
SYS_IL0000055633C00003$$
LOB
LOB INDEX
SYS_LOB_P41
SYS_IL_P23
INDEX PARTITION
SYS_LOB_SUBP32
INDEX SUBPARTITION
SYS_LOB_P44
SYS_IL_P24
INDEX
PARTITION
SYS_LOB_SUBP33
INDEX SUBPARTITION
SYS_LOB_SUBP35
INDEX SUBPARTITION
SYS_LOB_SUBP36
INDEX SUBPARTITION
SYS_LOB0000055633C00003$$
LOB
LOB
SYS_LOB_P31
LOB PARTITION
SYS_LOB_SUBP32
LOB SUBPARTITION
18
SYS_LOB_SUBP33
LOB SUBPARTITION
© 2008 Julian Dyke
SYS_LOB_P34
LOB PARTITION
SYS_LOB_SUBP35
LOB SUBPARTITION
SYS_LOB_SUBP36
LOB SUBPARTITION
juliandyke.com
Basic Files
In Row versus Out-of-Line Storage

Values can be stored
 in-line - in a row piece
 out-of-line - in a separate LOB segment
CREATE TABLE t1 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (ENABLE STORAGE IN ROW);
CREATE TABLE t2 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (DISABLE STORAGE IN ROW);


19
ENABLE STORAGE IN ROW (default)
 LOB value is stored within row if size is < 3964 bytes
 LOB value is stored out-of-line if size > 3964 bytes
 If in-line LOB grows to more than 3964 bytes
 LOB value is moved out-of-line to LOB segment
 If out-of-line LOB shrinks to less than 3964 bytes
 LOB value remains out-of-line in LOB segment
DISABLE STORAGE IN ROW
 LOB value is always stored out-of-line in LOB segment
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Initialization


LOBs are initialized using built-in functions
 EMPTY_BLOB ()
 EMPTY_CLOB ()
Both functions create LOB locators
 Minimum size is 36 bytes
 Maximum size is 86 bytes
CREATE TABLE t1
(
c1 NUMBER,
c2 CLOB,
);
INSERT INTO t1 VALUES (c1,NULL);
C2 occupies 0 bytes
INSERT INTO t1 VALUES (c1,EMPTY_CLOB());
C2 occupies 37bytes
20
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Initialization


LOB locator is initialized using EMPTY_CLOB() or EMPTY_BLOB()
For example:
INSERT INTO t1 VALUES (c1,EMPTY_CLOB());

Block dump includes :
tab 0, row 151, @0x579
tl: 44 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 3] c2 08 53
col 1: [36]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 89 3d 00 10 09 00 00
00 00 00 00 00 00 00 00 00 00 00
Header
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
10
09
00
00
00
00
00
00
00
00
00
00
00
00
00
Body Length
21
LOBID
Flags
© 2008 Julian Dyke
LOB Length
00
00
89
3d
Version
juliandyke.com
Basic Files
LOB ID



LOB ID is a 10 byte number identifying individual instance of a LOB
 Allocated when LOB value is created including EMPTY_CLOB() etc
Format is <X><Y> where
 <X> is a currently unknown 4-byte number (always 1)
 <Y> is a 6-byte number generated from sequence SYS.IDGEN$
For example:
SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_size
FROM v$_sequences;
Sequence Owner
SYS
Sequence Name
IDGEN1$
Next Value
37401
Increment By
50
Cache Size
20
INSERT INTO t1 VALUES (0,EMPTY_CLOB)

LOB ID =
00
00
00
01
00
00
00
00
92
19
(0x9219 = 37401)
SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_size
FROM v$_sequences;
Sequence Owner
SYS
22
© 2008 Julian Dyke
Sequence Name
IDGEN1$
Next Value
37451
Increment By
50
Cache Size
20
juliandyke.com
Basic Files
LOB Inline Storage

Example 1 - 12 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1, LPAD ('X',12,'X'));
ab 0, row 0, @0x1f62
tl: 54 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [48]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8a 7b 00 1c 09 00 00
00 00 00 00 0c 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8a
7b
00
1c
09
00
00
00
00
00
00
0c
00
00
00
00
00
01
58
58
58
58
58
58
58
58
58
58
58
58
0x1C = 28
23
© 2008 Julian Dyke
0x0C = 12
juliandyke.com
Basic Files
LOB Inline Storage

Example 2 - 400 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1,LPAD ('X',400,'X'));
tab 0, row 0, @0x1ddc
tl: 444 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [436]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8a ad 01 a0 09 00 00
00 00 00 01 90 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
.......
24
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8a
ad
01
a0
09
00
00
00
00
00
01
90
00
00
00
00
00
01
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
580x01A0
58 58= 416
58 58
58
58
58
58 0x0190
58 58 = 58
40058
58
58
58
58
58
58
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Inline Storage

Example 3 - 3964 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1,LPAD ('X',3964,'X'));
tab 0, row 0, @0xff0
tl: 4008 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [4000]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8c a1 0f 8c 09 00 00
00 00 00 0f 7c 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 . .
.....
25
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8c
a1
0f
8c
09
00
00
00
00
00
0f
7c
00
00
00
00
00
01
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58
58 58
58 58
0x0F8C
= 3980
58
58
58
58 0x0F7C
58 58 =
583964
58 58
58
58
58
58
58
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Out-of-Line Storage

Example 4 - 3965 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1,LPAD ('X',3965,'X'));
tab 0, row 0, @0x1f6a
tl: 46 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [40]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8d 05 00 14 05 00 00
00 00 00 0f 7d 00 00 00 00 00 02 01 00 01 b5
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8d
05
00
14
05
00
00
00
00
00
0f
7d
00
00
00
00
00
02
01
00
01
b5
0x0014 = 20
0x0F7D bytes
Address of
first chunk
0x0F7D = 3965
26
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Out-of-Line Storage



Out-of-line LOB data is stored in a PAGETABLE MANAGED LOB BLOCK
Each block has
 a 56 byte header
 a 4 byte trailer
For example for a 8192 byte block
Header
56 bytes
LOB Data
8132 bytes
Trailer
4 bytes
27
© 2008 Julian Dyke
juliandyke.com
Basic Files
Out-of-Line Storage
CREATE DIRECTORY dir1 AS '/tmp';
CREATE TABLE t11
(
c1 NUMBER,
c2 CLOB
);
INSERT INTO t11
VALUES (0,EMPTY_CLOB());
DECLARE
l_bfile BFILE;
l_clob CLOB;
l_result NUMBER;
BEGIN
l_bfile := BFILENAME ('DIR1','dbms_stats.lst');
DBMS_LOB.OPEN (l_bfile);
SELECT c2 INTO l_clob FROM t11
WHERE c1 = 0 FOR UPDATE;
l_result := DBMS_LOB.GETLENGTH (l_bfile);
DBMS_LOB.LOADFROMFILE (l_clob,l_bfile,l_result);
DBMS_LOB.CLOSE (l_bfile);
END;
28
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Out-of-Line Storage

Example 5 - 69237 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1,BFILE...);
tl: 78 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [72]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8d ff 00 34 05 00 00
00 00 08 10 55 00 00 00 00 00 02 01 00 02 5f 01 00 02 60 01 00 02 5c 01 00
02 5d 01 00 02 5e 01 00 02 6e 01 00 02 6f 01 00 02 70 01 00 02 6a
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8d
ff
00
34
05
00
00
00
00
08
10
55
00
00
00
00
00
02
01
00
02
5f
01
00
02
60
01
00
02
5c
01
00
02
5d
01
00
02
5e
01
00
02
6e
01
00
02
6f
01
00
02
70
01
00
02
6a
0x34 = 52
8 chunks
8 x 8132 = 65056
29
© 2008 Julian Dyke
0x1055 bytes
0x1055 = 4181
DBA of LOB
chunks
65056 + 4181+ 69237
juliandyke.com
Basic Files
LOB Out-of-Line Storage
Example 6 - 97584 byte LOB - ENABLE STORAGE IN ROW

INSERT INTO t1 VALUES (c1,BFILE...);
tab 0, row 0, @0x1f14
tl: 90 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [84]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 25 00 40 05 00 00
00 00 0c 00 00 00 00 00 00 00 02 01 00 02 3d 01 00 02 3e 01 00 02 3f 01 00
02 40 01 00 02 3c 01 00 02 37 01 00 02 38 01 00 02 32 01 00 02 33 01 00 02
34 01 00 02 35 01 00 02 36
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
90
25
00
40
05
00
00
00
00
0c
00
00
00
00
00
00
00
02
01
00
02
3d
01
00
02
3e
01
00
02
3f
01
00
02
40
01
00
02
3c
01
00
02
37
01
00
02
38
01
00
02
32
01
00
02
33
01
00
02
34
01
00
02
35
01
00
02
36
0x40 = 64
30
12 chunks
© 2008 Julian Dyke
0 bytes
12 x 8132 = 97584
LOB Index exists
but is empty
juliandyke.com
Basic Files
LOB Out-of-Line Storage

Example 7- 97585 byte LOB - ENABLE STORAGE IN ROW
INSERT INTO t1 VALUES (c1,BFILE...);
tab 0, row 0, @0x1f14
tl: 90 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [84]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 89 00 40 05 00 00
00 00 0c 00 01 00 00 00 00 00 02 01 00 03 2d 01 00 03 2e 01 00 03 2f 01 00
03 30 01 00 03 2c 01 00 03 27 01 00 03 28 01 00 03 22 01 00 03 23 01 00 03
24 01 00 03 25 01 00 03 26
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
90
89
00
40
05
00
00
00
00
0c
00
01
00
00
00
00
00
02
01
00
03
2d
01
00
03
2e
01
00
03
2f
01
00
03
30
01
00
03
2c
01
00
03
27
01
00
03
28
01
00
03
22
01
00
03
23
01
00
03
24
01
00
03
25
01
00
03
26
0x40 = 64
31
12 chunks
© 2008 Julian Dyke
1 byte
LOB Index is used
for 13th chunk
juliandyke.com
Basic Files
LOB Out-of-Line Storage


Example 7 continued - 97585 byte LOB - ENABLE STORAGE IN ROW
LOB Index
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
01 00 03 3f 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 89
col 1; len 4; (4): 00 00 00 0c
LOBID


32
First chunk number
Key
00
00
00
01
00
00
00
00
90
89
00
00
00
0c
Data
01
00
03
3f
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
LOB Index is always unique
Data is always 32 bytes
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Out-of-Line Storage
Example 8 - 187264 byte LOB - ENABLE STORAGE IN ROW

INSERT INTO t1 VALUES (c1,BFILE...);
tab 0, row 0, @0x1f14
tl: 90 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [84]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 ed 00 40 05 00 00
00 00 17 00 e4 00 00 00 00 00 02 01 00 03 35 01 00 03 36 01 00 03 37 01 00
03 38 01 00 03 34 01 00 03 5f 01 00 03 60 01 00 03 5a 01 00 03 5b 01 00 03
5c 01 00 03 5d 01 00 03 5e
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
90
ed
00
40
05
00
00
00
00
17
00
e4
00
00
00
00
00
02
01
00
03
35
01
00
03
36
01
00
03
37
01
00
03
38
01
00
03
34
01
00
03
5f
01
00
03
60
01
00
03
5a
01
00
03
5b
01
00
03
5c
01
00
03
5d
01
00
03
5e
0x17 = 23
chunks
33
© 2008 Julian Dyke
0xe4 = 228
bytes
12 chunks in row
11 chunks in index
juliandyke.com
Basic Files
LOB Out-of-Line Storage


Example 8 continued - 187264 byte LOB - ENABLE STORAGE IN ROW
Lob Index
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
01 00 03 6f 01 00 03 70 01 00 03 6a 01 00 03 6b 01 00 03 6c 01 00 03 6d 01
00 03 6e 01 00 03 57
col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 ed
col 1; len 4; (4): 00 00 00 0c
row#1[7932] flag: ------, lock: 2, len=50, data:(32):
01 00 03 58 01 00 03 52 01 00 03 53 01 00 03 54 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 ed
col 1; len 4; (4): 00 00 00 14
34
Key
00
00
00
01
00
00
00
00
90
ed
00
00
00
0c
Data
01
00
03
6f
01
00
03
70
01
00
03
6a
01
00
03
6b
01
00
03
6c
01
00
03
6d
01
00
03
6e
01
00
03
57
Key
00
00
00
01
00
00
00
00
90
ed
00
00
00
14
Data
01
00
03
58
01
00
03
52
01
00
03
53
01
00
03
54
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
© 2008 Julian Dyke
juliandyke.com
Basic Files
Chunk Size





Each internal LOB has a chunk size
Chunks can be manipulated individually
Default chunk size is block size
Maximum chunk size is 32768 for all tablespace block sizes
Chunk size is:
 specified in bytes
 rounded up to nearest block size
CREATE TABLE t3 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CHUNK 32768);


35
Chunk size is reported in DBA_LOB.CHUNK
Chunk size is stored in SYS.LOB$.CHUNK
 Although chunk size is specified and reported in bytes, it is stored in
LOB$ in terms of tablespace blocks
© 2008 Julian Dyke
juliandyke.com
Basic Files
Chunk Size



Example 12 - 69237 byte LOB
CHUNK = 8192 (default)
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8d
ff
00
34
05
00
00
00
00
08
10
55
00
00
00
00
00
02
01
00
02
5f
01
00
02
60
01
00
02
5c
01
00
02
5d
01
00
02
5e
01
00
02
6e
01
00
02
6f
01
00
02
70
01
00
02
6a
CHUNK = 32768 (maximum)
tab 0, row 0, @0x1f38
tl: 54 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [48]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 94 d5 00 1c 05 00 00
00 00 08 10 55 00 00 00 00 00 02 01 00 04 45 01 00 04 55 01 00 04 5d
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
94
d5
00
1c
05
00
00
00
00
08
10
55
00
00
00
00
00
02
01
00
04
45
01
00
04
55
01
00
04
5d
4 x 8132 = 32528
36
© 2008 Julian Dyke
2 x 32528 = 65056
0x1055 = 4181
65056 + 4181+ 69237
juliandyke.com
Basic Files
Chunk Size

In Oracle 11.1 maximum LOB size is determined by chunk size:
CREATE TABLE t1 (
c1 NUMBER,
c2 CLOB,
c3 CLOB,
c4 CLOB
)
LOB (c2) STORE AS (CHUNK 8192),
LOB (c3) STORE AS (CHUNK 16384),
LOB (c4) STORE AS (CHUNK 32768);
SELECT
dbms_lob.getchunksize (c2), dbms_lob.get_storage_limit (c2),
dbms_lob.getchunksize (c3), dbms_lob.get_storage_limit (c3),
dbms_lob.getchunksize (c4), dbms_lob.get_storage_limit (c4)
FROM t1;
Chunk Size
37
© 2008 Julian Dyke
Storage Limit
# Chunks
8132
34,926,674,042,940
4,294,967,295
16264
69,853,348,085,880
4,294,967,295
32528
139,706,696,171,760
4,294,967,295
juliandyke.com
Basic Files
Read Consistency



PCTVERSION clause
 Specifies the amount of space that will be used for the LOB consistentread mechanism
 Affects reclamation of old copies of LOB data
 Affects the ability to perform consistent read
Range is 0 to 100
Default value is 10
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (PCTVERSION 20);




38
Reported in DBA_LOB.PCTVERSION
Can be set to 0 for read-only LOBs
PCTVERSION should be set to a low value if:
 updates and reads are not concurrent
 the LOB is written once and then read-only
PCTVERSION should be set to a high value if:
 there are large numbers of queries
 there is heavy read and write activity
© 2008 Julian Dyke
juliandyke.com
Basic Files
Read Consistency

Example 9 - 32768 byte LOB - update 4000 bytes starting at offset 10000
 PCTVERSION = 10 (default)
DECLARE
l_clob CLOB;
l_str VARCHAR2(4000);
BEGIN
l_str := LPAD ('Y',4000,'Y');
SELECT c2 INTO l_clob
FROM t19
WHERE c1 = 0;
FOR UPDATE;
DBMS_LOB.WRITE (l_clob,4000,10000,l_str);
END;
/
39
© 2008 Julian Dyke
juliandyke.com
Basic Files
Read Consistency



Example 9 (continued)
Before Update
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
93
45
00
24
05
00
00
00
00
04
00
f0
00
00
00
00
00
02
01
00
03
fd
01
00
03
fe
01
00
03
ff
01
00
04
00
01
00
03
fc
After Update
tl: 62 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [56]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 93 45 00 24 05 00 00
00 00 04 00 f0 00 00 00 00 00 03 01 00 03 fd 01 00 04 0f 01 00 03 ff 01 00
04 00 01 00 03 fc
40
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
93
45
00
24
05
00
00
00
00
04
00
f0
00
00
00
00
00
02
01
00
03
fd
01
00
04
0f
01
00
03
ff
01
00
04
00
01
00
03
fc
© 2008 Julian Dyke
Updated Chunk
juliandyke.com
Basic Files
Read Consistency

Example 9 (continued)
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
01 00 03 fe 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 01 49 14 f1 b7 00 00 00 00
col 1; len 4; (4): 01 00 03 fe
Key
00
01
49
14
fe
c0
00
00
00
00
01
00
03
0f
Data
01
00
03
fe
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
LOB Index
Free List
3FD
3FE
40F
3FF
400
3FC
3FE
LOB Data
41
STOP
© 2008 Julian Dyke
juliandyke.com
Basic Files
Read Consistency

Example 10 - 32768 byte LOB - update 4000 bytes starting at offset 30000
 PCTVERSION = 10
DECLARE
l_clob CLOB;
l_str VARCHAR2(4000);
BEGIN
l_str := LPAD ('Y',4000,'Y');
SELECT c2 INTO l_clob
FROM t19
WHERE c1 = 0;
FOR UPDATE;
DBMS_LOB.WRITE (l_clob,4000,30000,l_str);
END;
/
42
© 2008 Julian Dyke
juliandyke.com
Basic Files
Read Consistency



Example 10 (continued)
Before Update
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
93
45
00
24
05
00
00
00
00
04
00
f0
00
00
00
00
00
02
01
00
03
fd
01
00
04
0f
01
00
03
ff
01
00
04
00
01
00
03
fc
After Update
tl: 62 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [56]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 93 45 00 24 05 00 00
00 00 04 05 bf 00 00 00 00 00 04 01 00 03 fd 01 00 04 0f 01 00 03 ff 01 00
03 fe 01 00 04 10
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
93
45
00
24
05
00
00
00
00
04
05
bf
00
00
00
00
00
04
01
00
03
fd
01
00
04
0f
01
00
03
ff
01
00
03
fe
01
00
04
10
Updated Chunk
43
© 2008 Julian Dyke
Updated Chunk
juliandyke.com
Basic Files
Read Consistency

Example 10 (continued) - Lob Index
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
01 00 03 fc 01 00 04 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 01 49 14 fe c0 00 00 00 00
col 1; len 4; (4): 01 00 03 fc
Key
00
01
49
14
fe
c0
00
00
00
00
01
00
03
0f
Data
01
00
03
fc
01
00
04
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
LOB Index
Free List
3FD
3FE
40F
3FF
400
3FE
3FC
410
3FC
3FE
400
LOB Data
44
STOP
© 2008 Julian Dyke
juliandyke.com
Basic Files
Partial Deletion

Example 11 - 69237 byte LOB - Erase 18000 bytes from offset 12000
DECLARE
l_clob CLOB;
l_amount NUMBER := 18000;
BEGIN
SELECT c2 INTO l_clob
FROM t22
WHERE c1 = 0
FOR UPDATE;
DBMS_LOB.ERASE (l_clob,l_amount,12000);
END;
45
© 2008 Julian Dyke
juliandyke.com
Basic Files
Partial Deletion



Example 11 continued - 69237 byte LOB - Erase 18000 bytes from offset 12000
Before ERASE
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
95
6b
00
34
05
00
00
00
00
08
10
55
00
00
00
00
00
02
01
00
04
6d
01
00
04
6e
01
00
04
6f
01
00
04
70
01
00
04
6c
01
00
04
7f
01
00
04
80
01
00
04
7a
01
00
04
7b
After ERASE
tab 0, row 0, @0x1f20
tl: 78 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [72]
00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 95 6b 00 34 05 00 00
00 00 08 10 55 00 00 00 00 00 03 01 00 04 6d 01 00 04 7d 00 00 00 00 01 00
04 7e 01 00 04 6c 01 00 04 7f 01 00 04 80 01 00 04 7a 01 00 04 7b
46
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
95
6b
00
34
05
00
00
00
00
08
10
55
00
00
00
00
00
03
01
00
04
6d
01
00
04
7d
00
00
00
00
01
00
04
7e
01
00
04
6c
01
00
04
7f
01
00
04
80
01
00
04
7a
01
00
04
7b
© 2008 Julian Dyke
juliandyke.com
Basic Files
Partial Deletion


Example 11 continued - 69237 byte LOB - Erase 18000 bytes from offset 12000
After ERASE - Lob Index
row#0[7982] flag: ------, lock: 2, len=50, data:(32):
01 00 04 6e 01 00 04 6f 01 00 04 70 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00
col 0; len 10; (10): 00 01 49 15 3a 62 00 00 00 0
col 1; len 4; (4): 01 00 04 6e
Data
01
00
04
6e
01
00
04
6f
01
00
04
70
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
LOB Index
46D
46E
47D
46F
470
47E
46C
Free List
47F
480
47A
47B
46E
46F
470
LOB Data
47
STOP
© 2008 Julian Dyke
juliandyke.com
Basic Files
CACHE Clause



Specifies whether a LOB will be stored in the database buffer cache when
values are read and written.
 Allows LOB values to be shared amongst users.
Possible values are CACHE, CACHE READS and NOCACHE
Default value is NOCACHE
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CACHE);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CACHE READS);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (NOCACHE);


48
Reported in DBA_LOB.CACHE
Can be NO, CACHEREADS or YES
© 2008 Julian Dyke
juliandyke.com
Basic Files
CACHE versus NOCACHE

CACHE LOGGING
Top 5 Timed Events
Avg %Total
~~~~~~~~~~~~~~~~~~
wait
Call
Event
Waits
Time (s)
(ms)
Time Wait Class
------------------------------ -------- ----------- ------ ------ ---------CPU time
558
72.6
log file sync
79,335
210
3
27.2
Commit
log file parallel write
66,613
102
2
13.4 System I/O
SQL*Net more data from client
37,857
19
1
2.6
Network
db file parallel write
5,281
11
2
1.4 System I/O

NOCACHE NOLOGGING
Top 5 Timed Events
Avg %Total
~~~~~~~~~~~~~~~~~~
wait
Call
Event
Waits
Time (s)
(ms)
Time Wait Class
------------------------------ -------- ----------- ------ ------ ---------enq: CF - contention
6,211
877
141
22.1
Other
CPU time
300
7.6
log file sync
69,297
171
2
4.3
Commit
control file parallel write
31,126
154
5
3.9 System I/O
log file parallel write
60,083
94
2
2.4 System I/O
49
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOGGING



Possible values are LOGGING (default) and NOLOGGING
If CACHE option is specified then LOGGING is mandatory
LOGGING/NOLOGGING cannot be specified without CACHE clause
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CACHE LOGGING);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CACHE READS LOGGING);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (CACHE READS NOLOGGING);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (NOCACHE LOGGING);
CREATE TABLE t4 (c1 NUMBER, c2 CLOB)
LOB (c2) STORE AS (NOCACHE NOLOGGING);


50
Reported in DBA_LOB.LOGGING
Can be NO or YES
© 2008 Julian Dyke
juliandyke.com
Basic Files
LOB Compression

LOBs can be compressed using the LZ_COMPRESS procedure in the
UTL_COMPRESS package

A quality can be specified
 1 - Fastest
 9 - Slowest
The default quality is 6


51
Compression achieved is
not very consistent
© 2008 Julian Dyke
File size in bytes
Quality
dbms_stats.lst
alert.log
Uncompressed
69237
1889203
1
14408
868037
2
14121
694521
3
14067
500580
4
14718
1185502
5
14574
1175025
6
14383
1167914
7
14347
1167769
8
14303
1167240
9
14303
1167238
juliandyke.com
Basic Files
LOB Compression

Example DECLARE
CREATE DIRECTORY dir1 AS '/tmp';
CREATE TABLE t34 (c1 NUMBER, c2 CLOB);
l_bfile BFILE;
INSERT INTO t34 VALUES (0,EMPTY_CLOB ());
l_blob BLOB;
l_clob CLOB;
l_length NUMBER;
l_quality NUMBER := 1;
l_dest_offset NUMBER := 1;
l_source_offset NUMBER := 1;
l_blob_csid NUMBER := DBMS_LOB.DEFAULT_CSID;
l_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
l_warning NUMBER;
BEGIN
l_bfile := BFILENAME ('DIR1','alert.log');
DBMS_LOB.OPEN (l_bfile);
DBMS_LOB.CREATETEMPORARY (l_blob,FALSE);
l_length := DBMS_LOB.GETLENGTH (l_bfile);
UTL_COMPRESS.LZ_COMPRESS (l_bfile,l_blob,l_quality);
SELECT c2 INTO l_clob FROM t34 WHERE c1 = 0 FOR UPDATE;
DBMS_LOB.CONVERTTOCLOB (l_clob,l_blob,DBMS_LOB.LOBMAXSIZE,
l_dest_offset,l_source_offset,l_blob_csid,l_lang_context,l_warning);
END;
52
© 2008 Julian Dyke
juliandyke.com
Secure
Files
53
© 2008 Julian Dyke
juliandyke.com
Secure Files
Overview
54

In Oracle 11.1 and above there are two types of LOB
 STORE AS BASICFILE (default)
 STORE AS SECUREFILE

STORE AS BASICFILE
 Default
 Behaves as Oracle 10.2 and below

STORE AS SECUREFILE
 Oracle 11.1 and above
 Supports several new features including
 LOB compression
 LOB encryption
 LOB deduplication
 Metadata only logging
 Configurable retention time
© 2008 Julian Dyke
juliandyke.com
SecureFiles
Storage

Can only be created in an ASSM tablespace

New block types supporting SecureFiles include:
Description
55
Block Type
NGLOB: Extent Map
0x3c
NGLOB: Hash Bucket
0x3d
NGLOB: Committed Free Space
0x3e
NGLOB: Segment Header
0x3f
NGLOB: Lob Extent Header
0x40
NGLOB: Persistent Undo
0x41

LOB Data Segments are still stored in blocks of type trans_data (block type 6)

Note
 CHUNK is advisory - only maintained for backward compatibility
 PCTVERSION not supported - use RETENTION instead
© 2008 Julian Dyke
juliandyke.com
Secure File
Initialization



EMPTY_CLOB ()
Basic File
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
10
09
00
00
00
00
00
00
00
00
00
00
00
00
00
00
00
89
3d
Secure File
tl: 36 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 1] 80
col 1: [30]
00 54 00 01 02 0c 00 80 00 01 00 00 00 01 00 00 00 00 99 53 00 0a 48 90 00
04 00 00 00 00
56
00
54
00
01
02
0c
00
80
00
01
00
0a
48
90
00
04
00
00
00
00
© 2008 Julian Dyke
00
00
00
01
00
00
00
00
99
53
juliandyke.com
Secure File
LOB Locator



Example DBMS_STATS.LST - 69237 bytes
Basic File
00
54
00
01
02
0c
00
00
00
01
00
00
00
01
00
00
00
00
8d
ff
00
34
05
00
00
00
00
08
10
55
00
00
00
00
00
02
01
00
02
5f
01
00
02
60
01
00
02
5c
01
00
02
5d
01
00
02
5e
01
00
02
6e
01
00
02
6f
01
00
02
70
01
00
02
6a
Secure File
tl: 57 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 1] 80
col 1: [51]
00 54 00 01 02 0c 00 80 00 01 00 00 00 01 00 00 00 00 98 8b 00 1f 40 90 00
19 22 00 01 0e 75 01 02 01 01 00 05 6d 04 01 01 00 05 80 01 01 01 00 05 82
04
57
00
54
00
01
02
0c
00
80
00
01
00
00
00
00
1f
40
90
00
19
22
00
01
0e
75
01
02
01
01
00
05
6d
04
01
01
00
05
80
01
01
01
00
05
82
04
© 2008 Julian Dyke
01
00
00
00
00
98
8b
0x10E75 = 69237
# Blocks in extent
DBA of first block in extent
juliandyke.com
Secure Files
Lob Blocks



Out-of-line LOB data is stored in a trans data block (block type 6)
Each block has
 a 128 byte header
 a 4 byte trailer
For example for a 8192 byte block
Header
128 bytes
LOB Data
8060 bytes
Trailer
4 bytes
58
© 2008 Julian Dyke
juliandyke.com
Secure Files
LOB Compression

Oracle 11.1 and above

Enables server-side LOB compression
 Random read/write access is still possible
 Independent of table compression / index compression
 Only valid for SecureFile LOBs

Valid values are
 COMPRESS <defaults to MEDIUM>
 COMPRESS MEDIUM
 COMPRESS HIGH
 NOCOMPRESS <default>

COMPRESS MEDIUM
 Faster (less resource)
COMPRESS HIGH
 Better compression (less storage)


59
Individual LOBs can be compressed using DBMS_LOB.SETOPTIONS ()
© 2008 Julian Dyke
juliandyke.com
Secure Files
LOB Compression

Compressed size of LOB is not reported in Oracle 11.1

To calculate compressed size count number of extents
 In block dump
 Using DBMS_LOBUTIL.GETINODE

Size will be an (over) estimate

Example - alert.log - sizes in bytes
Uncompressed Size

60
1899203
COMPRESS MEDIUM
72540
COMPRESS HIGH
40300
SecureFile LOB Compression much better than UTLCOMPRESS package
 For example best compression achieved for same file using
LZ_COMPRESS was 500580 bytes
© 2008 Julian Dyke
juliandyke.com
Secure Files
DBMS_LOBUTIL

Unsupported package that describes SecureFile LOBs

Defined in $ORACLE_HOME/rdbms/admin/dbmslobu.sql

Subroutines include
 GETINODE
 GETLOBMAP
 GETEXTENTS

GETINODE
 Reports LOB header
GETLOBMAP
 Reports LOB extent header
 Numeric parameter is extent number (0..N-1)
GETEXTENTS
 Reports LOB extents in more detail
 Pipelined function - takes REF CURSOR as parameter


61
© 2008 Julian Dyke
juliandyke.com
Secure Files
DBMS_LOBUTIL
DECLARE
l_clob CLOB;
l_inode DBMS_LOBUTIL_INODE_T;
l_lobmap DBMS_LOBUTIL_LOBMAP_T;
BEGIN
SELECT c2 INTO l_clob FROM t29
WHERE c1 = 0 FOR UPDATE;
l_inode := DBMS_LOBUTIL.GETINODE (l_clob);
DBMS_OUTPUT.PUT_LINE ('LOBID '||RAWTOHEX (l_inode.lobid));
DBMS_OUTPUT.PUT_LINE (' flags '||l_inode.flags);
DBMS_OUTPUT.PUT_LINE (' length '||l_inode.length);
DBMS_OUTPUT.PUT_LINE (' version '||l_inode.version);
DBMS_OUTPUT.PUT_LINE (' extents '||l_inode.extents);
FOR i IN 0..l_inode.extents - 1
LOOP
l_res := DBMS_LOBUTIL.GETLOBMAP (l_clob,i);
DBMS_OUTPUT.PUT_LINE ('Extent# '||i);
DBMS_OUTPUT.PUT_LINE (' rdba '||l_res.rdba);
DBMS_OUTPUT.PUT_LINE (' nblks '||l_res.nblks);
DBMS_OUTPUT.PUT_LINE (' offset '||l_res.offset);
DBMS_OUTPUT.PUT_LINE (' length '||l_res.length);
END LOOP;
END;
62
© 2008 Julian Dyke
juliandyke.com
References
63

Oracle Database Application Developer's Guide - Large Objects
10g Release 2 (10.2) - Part Number B14249-01

Oracle Database SecureFiles and Large Objects Developers Guide
11g Release 1 (11.1) - Part Number B28393-03
© 2008 Julian Dyke
juliandyke.com
Any Questions ?
[email protected]
64
© 2008 Julian Dyke
juliandyke.com