Oracle DBMS_LOBUTIL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
Purpose A container for diagnostic and utility functions and procedures specific to LOBs. Since diagnostic operations are not part of the standard programmatic APIs in DBMS_LOB, they are provided in a separate namespace to avoid clutter. The diagnostic API is also not quite as critical to document for end-users; its main use is for internal developer, QA, and DDR use (especially since it peeks into the internal structure of LOB inodes and lobmaps).
AUTHID DEFINER
Data Types -- dbms_lobutil_inode_t: inode information
CREATE OR REPLACE TYPE dbms_lobutil_inode_t AS OBJECT (
lobid   RAW(10), -- lobid
flags   NUMBER,  -- inode flags
length  NUMBER,  -- lob length
version NUMBER,  -- lob version
extents NUMBER,  -- #extents in inode
lhb     NUMBER   -- lhb dba);
/

-- dbms_lobutil_lobmap_t: lobmap information
CREATE OR REPLACE TYPE dbms_lobutil_lobmap_t AS OBJECT (
lobid  RAW(10), -- lobid
eflag  NUMBER,  -- extent flags
rdba   NUMBER,  -- extent header rdba
nblks  NUMBER,  -- #blocks in extent
offset NUMBER,  -- offset of extent header
length NUMBER   -- logical length of extent);

-- dbms_lobutil_lobextent_t: extent information
CREATE OR REPLACE TYPE dbms_lobutil_lobextent_t AS OBJECT (
rid     VARCHAR(32), -- rowid proxy
row#    NUMBER,      -- rownum proxy
lobid   RAW(10),     -- lobid
extent# NUMBER,      -- extent# [0 .. ] for a lobmap
hole    VARCHAR(1),  -- is the extent a hole? (y/n)
cont    VARCHAR(1),  -- is the extent a superchunk continuation (y/n)
over    VARCHAR(1),  -- is the chunk an overallocation? (y/n)
rdba    NUMBER,      -- rdba of extent start
nblks   NUMBER,      -- #blocks in extent
offset  NUMBER,      -- logical offset of extent start
length  NUMBER       -- logical length of extent);
/

-- dbms_lobutil_lobextents_t: expanded extent map information
CREATE OR REPLACE TYPE dbms_lobutil_lobextents_t AS
TABLE OF dbms_lobutil_lobextent_t;
/
Dependencies
ANYDATA DBMS_LOBUTIL_INODE_T DBMS_LOBUTIL_LOBEXTENT_T
DBMS_LOBUTIL_DEDUPSET_T DBMS_LOBUTIL_LOBEXTENTS_T DBMS_LOBUTIL_LOBMAP_T
Documented No
First Available 11.1.0.6
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmslobu.sql
Subprograms
 
COPY_PRIMARY_DEDUP
Copy the primary LOB of a dedup set

Overload 1
dbms_lobutil.copy_primary_dedup(
lob_loc IN OUT NOCOPY BLOB,
phash   IN            RAW,
fhash   IN            RAW,
scn     IN            NUMBER DEFAULT 0,
par     IN            NUMBER);
TBD
Overload 2 dbms_lobutil.copy_primary_dedup(
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
phash   IN            RAW, fhash IN RAW,
scn     IN            NUMBER DEFAULT 0,
par     IN            NUMBER);
TBD
 
GETDEDUPSET
Undocumented deduplication set query
Overload 1
dbms_lobutil.getdedupset(lob_loc IN BLOB)
RETURN dbms_lobutil_dedupset_t DETERMINISTIC;
TBD
Overload 2 dbms_lobutil.getdedupset(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN dbms_lobutil_dedupset_t DETERMINISTIC;
TBD
 
GETEXTENTS
Undocumented extent expansion dbms_lobutil.getextents(crs IN sys_refcursor)
RETURN dbms_lobutil_lobextents_t DETERMINISTIC PIPELINED;
TBD
 
GETINODE
Undocumented inode query

Overload 1
dbms_lobutil.getinode(lob_loc IN BLOB)
RETURN dbms_lobutil_inode_t DETERMINISTIC;
TBD
Overload 2 dbms_lobutil.getinode(lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN dbms_lobutil_inode_t DETERMINISTIC;
TBD
 
GETLOBMAP
Undocumented LOB map query. Works only with securefiles.

Overload 1

This demo is built to run on the tables created in the SECUREFILES demo
dbms_lobutil.getlobmap(lob_loc IN BLOB, n IN NUMBER)
RETURN dbms_lobutil_lobmap_t DETERMINISTIC;
set serveroutput on

DECLARE
 bvar BLOB;
 x    dbms_lobutil_lobmap_t;
BEGIN
  SELECT bcol
  INTO bvar
  FROM reg_tab
  WHERE rownum = 1;

  x := dbms_lobutil.getlobmap(bvar, 0);
END;
/

DECLARE
 bvar BLOB;
 luty dbms_lobutil_lobmap_t;
BEGIN
  SELECT bcol
  INTO bvar
  FROM sec_tab
  WHERE rownum = 1;

  luty := dbms_lobutil.getlobmap(bvar, 0);

  dbms_output.put_line('LOBID: ' || luty.lobid);
  dbms_output.put_line('Extent Flag: ' || luty.eflag);
  dbms_output.put_line('Extent Hdr: ' || luty.rdba);
  dbms_output.put_line('blocks: ' || luty.nblks);
  dbms_output.put_line('offset: ' || luty.offset);
  dbms_output.put_line('length: ' || luty.length);
END;
/
Overload 2 dbms_lobutil.getlobmap(lob_loc IN CLOB CHARACTER SET ANY_CS, n IN NUMBER)
RETURN dbms_lobutil_lobmap_t DETERMINISTIC;
TBD

Related Topics
Built-in Functions
Built-in Packages
DBMS_LOB
LOBs
SECUREFILES
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved