/*
|| Oracle 11g SecureFiles Listing 1
||
|| Introduces Oracle Database 11g's SecureFiles features, including:
|| - Creation of sample tablespaces, directory objects, and application schema
|| - Creation of sample application tables
|| - Creation of package specification and body for loading and manipulation of 
||   BasicFile and SecureFile LOBs
|| - Loading of BasicFile LOBs table
|| - Population of SecureFile LOBs table from its BasicFile LOBs "brother"
|
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11gR1's
|| new SecureFiles features, and they should be carefully proofread before being 
|| executed against any existing Oracle database to avoid potential damage!
*/

/* 
|| Listing 1.1:
|| Create tablespaces, schema (TRBTKT), and directory objects for
|| storage of BasicFile and SecureFile LOBs
*/

-- Drop TRBTKT schema
DROP USER trbtkt CASCADE;

-----
-- Create a new tablespace for storage of BasicFiles
-----
DROP TABLESPACE basicfiles INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE basicfiles
    DATAFILE '/u01/app/oracle/oradata/ORCL/basicfiles01.dbf' 
    SIZE 128M REUSE
    EXTENT MANAGEMENT LOCAL
    UNIFORM SIZE 4M
    SEGMENT SPACE MANAGEMENT AUTO;

-----
-- Create a new tablespace for storage of SecureFiles. Note that SecureFile
-- LOBs absolutely require Automatic Segment Space Management (ASSM) to be 
-- activated for their storage!
-----
DROP TABLESPACE securefiles INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE securefiles
    DATAFILE '/u01/app/oracle/oradata/ORCL/securefiles01.dbf' 
    SIZE 256M REUSE
    EXTENT MANAGEMENT LOCAL
    UNIFORM SIZE 4M
    SEGMENT SPACE MANAGEMENT AUTO;

-----
-- Create a new user (TRBTKT) 
-----
CREATE USER trbtkt 
    IDENTIFIED BY trbtkt
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users
    QUOTA UNLIMITED ON basicfiles
    QUOTA UNLIMITED ON securefiles;

GRANT connect, resource TO trbtkt;
GRANT EXECUTE ANY PROCEDURE, CREATE ANY DIRECTORY TO trbtkt;
 
----
-- Create new directory objects in which all related trouble ticket documents 
-- and images will be stored.
-----
DROP DIRECTORY trbtkt_docs;
CREATE OR REPLACE DIRECTORY trbtkt_docs
    AS '/home/oracle/trbtkt/docs';
GRANT READ ON DIRECTORY trbtkt_docs TO trbtkt;

DROP DIRECTORY trbtkt_imgs;
CREATE OR REPLACE DIRECTORY trbtkt_imgs
    AS '/home/oracle/trbtkt/imgs';
GRANT READ ON DIRECTORY trbtkt_imgs TO trbtkt;

/* 
|| Listing 1.2:
|| Create tables for storage of Trouble Ticket application data,
|| including BasicFile and SecureFile LOBs
*/

-----
-- Create new table (TRBTKT.TICKETS) that uses BasicFile LOBS to store
-- Microsoft Word documents and screen prints as part of a trouble ticket
-- processing system
-----
DROP TABLE trbtkt.tickets PURGE;
CREATE TABLE trbtkt.tickets (
     tkt_id         NUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,status         VARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   STORE AS BASICFILE (TABLESPACE basicfiles)
   ,LOB(scrnimg)    STORE AS BASICFILE (TABLESPACE basicfiles)
;

-- Comments
COMMENT ON TABLE trbtkt.tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';

-- Create indexes and constraints
CREATE UNIQUE INDEX trbtkt.tickets_pk_idx
    ON trbtkt.tickets(tkt_id)
    TABLESPACE users;

ALTER TABLE trbtkt.tickets
    ADD CONSTRAINT tickets_pk
    PRIMARY KEY (tkt_id);

-----
-- Create new table (TRBTKT.SECURE_TICKETS) that uses SecureFile LOBS to store
-- Microsoft Word documents and screen prints as part of a trouble ticket
-- processing system
-----
DROP TABLE trbtkt.secure_tickets PURGE;
CREATE TABLE trbtkt.secure_tickets (
     tkt_id         NUMBER
    ,description    VARCHAR2(30)
    ,submit_dtm     TIMESTAMP
    ,status         VARCHAR2(8)
    ,document       BLOB
    ,scrnimg        BLOB
)
    LOB(document)   
        STORE AS SECUREFILE (
            TABLESPACE securefiles
            DISABLE STORAGE IN ROW
            KEEP_DUPLICATES
            COMPRESS HIGH
            CACHE
        )
   ,LOB(scrnimg)    
        STORE AS SECUREFILE (
            TABLESPACE securefiles
            DISABLE STORAGE IN ROW
            DEDUPLICATE
            COMPRESS HIGH
            CACHE READS
        )
;

-- Comments
COMMENT ON TABLE trbtkt.secure_tickets
    IS 'Contains Trouble Ticket transaction data';
COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id
    IS 'Unique identifier for a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.description
    IS 'Trouble Ticket Description';
COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm
    IS 'Trouble Ticket Submission Time Stamp';
COMMENT ON COLUMN trbtkt.secure_tickets.status
    IS 'Trouble Ticket Status';
COMMENT ON COLUMN trbtkt.secure_tickets.document
    IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket';
COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg
    IS 'Large Object (LOB) that contains a screen print or other image that helps a problem solver identify how to act on a Trouble Ticket';

-- Create indexes and constraints
CREATE UNIQUE INDEX trbtkt.secure_tickets_pk_idx
    ON trbtkt.secure_tickets(tkt_id)
    TABLESPACE users;

ALTER TABLE trbtkt.secure_tickets
    ADD CONSTRAINT secure_tickets_pk
    PRIMARY KEY (tkt_id);

/* 
|| Listing 1.3:
|| Creating package TRBTKT.PKG_SECUREFILES for administration of BasicFile and
|| SecureFile LOBs
*/

CREATE OR REPLACE PACKAGE trbtkt.pkg_securefiles
AS
    PROCEDURE AddTroubleTicket (
         tkt_id         IN trbtkt.tickets.tkt_id%TYPE
        ,description    IN trbtkt.tickets.description%TYPE
        ,submit_dts     IN VARCHAR2
        ,status         IN trbtkt.tickets.status%TYPE
        ,docFileName    IN VARCHAR2
        ,imgFileName    IN VARCHAR2
    );
    
    PROCEDURE DisplayTroubleTickets;

    PROCEDURE calc_space_basicfiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
    );

    PROCEDURE calc_space_basicfiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
        ,partname         IN  VARCHAR2
    );

    PROCEDURE calc_space_securefiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
    );

    PROCEDURE calc_space_securefiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
        ,partname         IN  VARCHAR2
    );

END pkg_securefiles;
/

CREATE OR REPLACE PACKAGE BODY trbtkt.pkg_securefiles
AS
    PROCEDURE LoadBFILEIntoLOB (
     src_dir    IN      VARCHAR2
    ,src_file   IN      VARCHAR2
    ,target_lob IN OUT  BLOB
    )
    /*
    || Procedure:   LoadBFILEIntoLOB
    || Purpose:     Loads an external LOB (BFILE) into an internal BasicFile 
    ||              or SecureFile LOB
    || Scope:       Private
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    ||
    */
    IS
        src_loc     BFILE   := BFILENAME(src_dir, src_file);
        load_amt    INTEGER := 4000;
    BEGIN
        -- Open the source document file in read-only mode
        DBMS_LOB.OPEN(
             file_loc => src_loc
            ,open_mode => DBMS_LOB.LOB_READONLY
        );

        -- Calculate the size of the external BFILE 
        load_amt := DBMS_LOB.GETLENGTH(file_loc => src_loc);
        
        -- Load the LOB from the source file
        DBMS_LOB.LOADFROMFILE(target_lob, src_loc, load_amt);

		-- Close the opened BFILE external LOB
        DBMS_LOB.FILECLOSE(file_loc => src_loc);

    EXCEPTION
        WHEN OTHERS THEN 
             DBMS_OUTPUT.PUT_LINE('LoadLOBFromFILE Error: ' || SQLCODE || ' - ' || SQLERRM);

    END LoadBFILEIntoLob;

    PROCEDURE AddTroubleTicket (
         tkt_id         IN trbtkt.tickets.tkt_id%TYPE
        ,description    IN trbtkt.tickets.description%TYPE
        ,submit_dts     IN VARCHAR2
        ,status         IN trbtkt.tickets.status%TYPE
        ,docFileName    IN VARCHAR2
        ,imgFileName    IN VARCHAR2
    )
    /*
    || Procedure:   AddTroubleTickets
    || Purpose:     Adds a new Trouble Ticket entry into TRBTKT.TICKETS, including processing 
    ||              of both LOB columns.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
        submit_dtm  TIMESTAMP;
        docBlob     BLOB;
        imgBlob     BLOB;
    BEGIN

        -- Calculate timestamp value
        submit_dtm := TO_TIMESTAMP(submit_dts, 'yyyy-mm-dd hh24:mi:ss');

        -- Add new row, returning references to the document and image BLOBs
        INSERT INTO trbtkt.tickets
        VALUES (tkt_id, description, submit_dtm, status, EMPTY_BLOB(), EMPTY_BLOB())
        RETURNING document, scrnimg INTO docBlob, imgBlob;
        
        -- Build the document LOB from the supplied file name
        LoadBFILEIntoLOB('TRBTKT_DOCS', docFileName, docBlob);

        -- Build the document LOB from the supplied file name
        LoadBFILEIntoLOB('TRBTKT_IMGS', imgFileName, imgBlob);

    EXCEPTION
        WHEN OTHERS THEN 
             DBMS_OUTPUT.PUT_LINE('Severe error! ' || SQLCODE || ' - ' || SQLERRM);

    END AddTroubleTicket;

    PROCEDURE DisplayTroubleTickets
    /*
    || Procedure:   DisplayTroubleTickets
    || Purpose:     Displays the first few characters from the documentation stored within
    ||              the trouble ticket document LOB.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
        lob_loc     BLOB;
        CURSOR cur_trbtkt IS
            SELECT 
                 tkt_id
                ,description
                ,submit_dtm
                ,document
                ,scrnimg
              FROM trbtkt.tickets;
        rcd_trbtkt  cur_trbtkt%ROWTYPE;
    BEGIN
        -- Open the Trouble Tickets cursor
        OPEN cur_trbtkt;
        LOOP
            -- Get the next cursor entry
            FETCH cur_trbtkt INTO rcd_trbtkt;

            -- Translate the document column contents into a temporary LOB
            lob_loc := rcd_trbtkt.document;

            -- Display some basic information about the Trouble Ticket
            DBMS_OUTPUT.PUT_LINE(
                'Trouble Ticket #' || rcd_trbtkt.tkt_id ||  ' has documentation that is ' || 
                DBMS_LOB.GETLENGTH(lob_loc) || ' bytes in length.');
            DBMS_OUTPUT.PUT_LINE(
                'First 200 characters: '||
                UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lob_loc, 200, 1))
            );

            -- Exit the loop when no more cursor entries are present
            EXIT WHEN cur_trbtkt%NOTFOUND;

        END LOOP;

        -- Finally, close the cursor    
        CLOSE cur_trbtkt;
        
    END DisplayTroubleTickets;

    PROCEDURE calc_space_basicfiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
    )
    /*
    || Procedure:   calc_space_basicfiles
    || Purpose:     Displays space utilization for a SecureFile LOB.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
       segname      VARCHAR2(500);
       KBC          NUMBER;
       fs1_byts     NUMBER(15,2);   fs1_blks     NUMBER;
       fs2_byts     NUMBER(15,2);   fs2_blks     NUMBER;
       fs3_byts     NUMBER(15,2);   fs3_blks     NUMBER;
       fs4_byts     NUMBER(15,2);   fs4_blks     NUMBER;
       full_byts    NUMBER(15,2);   full_blks    NUMBER;
       unfm_byts    NUMBER(15,2);   unfm_blks    NUMBER;
    BEGIN 
        KBC := (1024 * 1024);

        SELECT segment_name
          INTO segname 
          FROM all_lobs 
         WHERE owner = ownname
           AND table_name = tabname
           AND column_name = colname
		;

        DBMS_SPACE.SPACE_USAGE( 
             segment_owner      => ownname
            ,segment_name       => segname
            ,segment_type       => 'LOB' 
            ,fs1_bytes          => fs1_byts
            ,fs1_blocks         => fs1_blks
            ,fs2_bytes          => fs2_byts
            ,fs2_blocks         => fs2_blks
            ,fs3_bytes          => fs3_byts
            ,fs3_blocks         => fs3_blks
            ,fs4_bytes          => fs4_byts
            ,fs4_blocks         => fs4_blks
            ,full_bytes         => full_byts
            ,full_blocks        => full_blks
            ,unformatted_bytes  => unfm_byts
            ,unformatted_blocks => unfm_blks
       );
       DBMS_OUTPUT.ENABLE;
       DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE('Space Usage for BasicFile LOB ' || UPPER(ownname) || '.' || UPPER(tabname) || '.' || UPPER(colname) );
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
       DBMS_OUTPUT.PUT_LINE('        FS1 Blocks: ' || fs1_blks    || ' KB: ' || ROUND((fs1_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('        FS2 Blocks: ' || fs2_blks    || ' KB: ' || ROUND((fs2_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('        FS3 Blocks: ' || fs3_blks    || ' KB: ' || ROUND((fs3_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('        FS4 Blocks: ' || fs4_blks    || ' KB: ' || ROUND((fs4_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('       Full Blocks: ' || full_blks   || ' KB: ' || ROUND((full_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('Unformatted Blocks: ' || unfm_blks   || ' KB: ' || ROUND((unfm_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE(
            'Total Blocks: ' || TO_CHAR(fs1_blks + fs2_blks + fs3_blks + fs4_blks + full_blks)
         || ' Total KB: ' || TO_CHAR(ROUND(((fs1_byts + fs2_byts + fs3_byts + fs4_byts + full_byts) / KBC),2)) );
       DBMS_OUTPUT.PUT_LINE('    ');

    END calc_space_basicfiles;
    
    PROCEDURE calc_space_basicfiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
        ,partname         IN  VARCHAR2
    )
    /*
    || Procedure:   calc_space_basicfiles
    || Purpose:     Displays space utilization for a SecureFile LOB.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
       KBC          NUMBER;
       lobname      VARCHAR2(30);
       lobpartname  VARCHAR2(30);
       fs1_byts     NUMBER(15,2);   fs1_blks     NUMBER;
       fs2_byts     NUMBER(15,2);   fs2_blks     NUMBER;
       fs3_byts     NUMBER(15,2);   fs3_blks     NUMBER;
       fs4_byts     NUMBER(15,2);   fs4_blks     NUMBER;
       full_byts    NUMBER(15,2);   full_blks    NUMBER;
       unfm_byts    NUMBER(15,2);   unfm_blks    NUMBER;
    BEGIN 
        KBC := (1024 * 1024);

        -- Gather all partition names for a partitioned LOB    
        SELECT lob_name, lob_partition_name
          INTO lobname, lobpartname
          FROM all_lob_partitions 
         WHERE table_owner = ownname
           AND table_name = tabname
           AND column_name = colname
		   AND partition_name = partname
        ;

        DBMS_SPACE.SPACE_USAGE( 
             segment_owner      => ownname
            ,segment_name       => lobname
            ,segment_type       => 'LOB PARTITION' 
            ,fs1_bytes          => fs1_byts
            ,fs1_blocks         => fs1_blks
            ,fs2_bytes          => fs2_byts
            ,fs2_blocks         => fs2_blks
            ,fs3_bytes          => fs3_byts
            ,fs3_blocks         => fs3_blks
            ,fs4_bytes          => fs4_byts
            ,fs4_blocks         => fs4_blks
            ,full_bytes         => full_byts
            ,full_blocks        => full_blks
            ,unformatted_bytes  => unfm_byts
            ,unformatted_blocks => unfm_blks
			,partition_name     => lobpartname
       );
       DBMS_OUTPUT.ENABLE;
       DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE('Space Usage for BasicFile LOB ' || UPPER(ownname) || '.' || UPPER(tabname) || '.' || UPPER(colname) );
       DBMS_OUTPUT.PUT_LINE('Partition Name: ' || UPPER(partname) );
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
--     DBMS_OUTPUT.PUT_LINE('        FS1 Blocks: ' || fs1_blks    || ' KB: ' || ROUND((fs1_byts / KBC),2));
--     DBMS_OUTPUT.PUT_LINE('        FS2 Blocks: ' || fs2_blks    || ' KB: ' || ROUND((fs2_byts / KBC),2));
--     DBMS_OUTPUT.PUT_LINE('        FS3 Blocks: ' || fs3_blks    || ' KB: ' || ROUND((fs3_byts / KBC),2));
--     DBMS_OUTPUT.PUT_LINE('        FS4 Blocks: ' || fs4_blks    || ' KB: ' || ROUND((fs4_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('       Full Blocks: ' || full_blks   || ' KB: ' || ROUND((full_byts / KBC),2));
       DBMS_OUTPUT.PUT_LINE('Unformatted Blocks: ' || unfm_blks   || ' KB: ' || ROUND((unfm_byts / KBC),2));
--     DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE(
            'Total Blocks: ' || TO_CHAR(fs1_blks + fs2_blks + fs3_blks + fs4_blks + full_blks)
         || ' Total KB: ' || TO_CHAR(ROUND(((fs1_byts + fs2_byts + fs3_byts + fs4_byts + full_byts) / KBC),2)) );
       DBMS_OUTPUT.PUT_LINE('============================================================');

    END calc_space_basicfiles;
    
    PROCEDURE calc_space_securefiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
    )
    /*
    || Procedure:   calc_space_securefiles
    || Purpose:     Displays space utilization for a SecureFile LOB.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
       segname      VARCHAR2(500);
       sgmt_byts    NUMBER(15,2);
       sgmt_blks    NUMBER;
       used_byts    NUMBER(15,2);
       used_blks    NUMBER;
       expd_byts    NUMBER(15,2);
       expd_blks    NUMBER;
       unxp_byts    NUMBER(15,2);
       unxp_blks    NUMBER;
       KBC          NUMBER;

    BEGIN 
        KBC := (1024 * 1024);

        SELECT segment_name 
          INTO segname 
          FROM all_lobs 
         WHERE owner = ownname
           AND table_name = tabname
           AND column_name = colname
		 ;
    
        DBMS_SPACE.SPACE_USAGE(
             segment_owner          => ownname
            ,segment_name           => segname
            ,segment_type           => 'LOB' 
            ,segment_size_blocks    => sgmt_blks
            ,segment_size_bytes     => sgmt_byts
            ,used_blocks            => used_blks
            ,used_bytes             => used_byts
            ,expired_blocks         => expd_blks 
            ,expired_bytes          => expd_byts
            ,unexpired_blocks       => unxp_blks
            ,unexpired_bytes        => unxp_byts
			,partition_name         => NULL
       );
       DBMS_OUTPUT.ENABLE;
       DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE('Space Usage for SecureFile LOB ' || UPPER(ownname) || '.' || UPPER(tabname) || '.' || UPPER(colname) );
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
       DBMS_OUTPUT.PUT_LINE('  Segment Blocks: ' || sgmt_blks || ' KB: ' || ROUND((sgmt_byts / KBC),2) );
       DBMS_OUTPUT.PUT_LINE('     Used Blocks: ' || used_blks || ' KB: ' || ROUND((used_byts / KBC),2) ); 
       DBMS_OUTPUT.PUT_LINE('  Expired Blocks: ' || expd_blks || ' KB: ' || ROUND((expd_byts / KBC),2) );
       DBMS_OUTPUT.PUT_LINE('Unexpired Blocks: ' || unxp_blks || ' KB: ' || ROUND((unxp_byts / KBC),2) ); 
       DBMS_OUTPUT.PUT_LINE(' ');
       DBMS_OUTPUT.PUT_LINE('============================================================');
       
    END calc_space_securefiles;

    PROCEDURE calc_space_securefiles (
         ownname          IN  VARCHAR2
        ,tabname          IN  VARCHAR2
        ,colname          IN  VARCHAR2
        ,partname         IN  VARCHAR2
    )
    /*
    || Procedure:   calc_space_securefiles
    || Purpose:     Displays space utilization for a SecureFile LOB.
    || Scope:       Public
    || Author:      Jim Czuprynski (Fujitsu Consulting)
    */
    IS
       lobname      VARCHAR2(30);
       lobpartname  VARCHAR2(30);
       sgmt_byts    NUMBER(15,2);
       sgmt_blks    NUMBER;
       used_byts    NUMBER(15,2);
       used_blks    NUMBER;
       expd_byts    NUMBER(15,2);
       expd_blks    NUMBER;
       unxp_byts    NUMBER(15,2);
       unxp_blks    NUMBER;
       KBC          NUMBER;

    BEGIN 
        KBC := (1024);

        -- Gather all partition names for a partitioned LOB    
        SELECT lob_name, lob_partition_name
          INTO lobname, lobpartname
          FROM all_lob_partitions 
         WHERE table_owner = ownname
           AND table_name = tabname
           AND column_name = colname
		   AND partition_name = partname
        ;

        DBMS_SPACE.SPACE_USAGE(
             segment_owner          => ownname
            ,segment_name           => lobname
            ,segment_type           => 'LOB PARTITION' 
            ,segment_size_blocks    => sgmt_blks
            ,segment_size_bytes     => sgmt_byts
            ,used_blocks            => used_blks
            ,used_bytes             => used_byts
            ,expired_blocks         => expd_blks 
            ,expired_bytes          => expd_byts
            ,unexpired_blocks       => unxp_blks
            ,unexpired_bytes        => unxp_byts
			,partition_name         => lobpartname
       );
       DBMS_OUTPUT.ENABLE;
       DBMS_OUTPUT.PUT_LINE('============================================================');
       DBMS_OUTPUT.PUT_LINE('Space Usage for SecureFile LOB ' || UPPER(ownname) || '.' || UPPER(tabname) || '.' || UPPER(colname) );
       DBMS_OUTPUT.PUT_LINE('Partition Name: ' || UPPER(partname) );
       DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------');
       DBMS_OUTPUT.PUT_LINE('  Segment Blocks: ' || sgmt_blks || ' KB: ' || ROUND((sgmt_byts / KBC),2) );
       DBMS_OUTPUT.PUT_LINE('     Used Blocks: ' || used_blks || ' KB: ' || ROUND((used_byts / KBC),2) ); 
       DBMS_OUTPUT.PUT_LINE('  Expired Blocks: ' || expd_blks || ' KB: ' || ROUND((expd_byts / KBC),2) );
       DBMS_OUTPUT.PUT_LINE('Unexpired Blocks: ' || unxp_blks || ' KB: ' || ROUND((unxp_byts / KBC),2) ); 
       DBMS_OUTPUT.PUT_LINE(' ');
       DBMS_OUTPUT.PUT_LINE('============================================================');
       
    END calc_space_securefiles;

BEGIN
    NULL;
END pkg_securefiles;
/

/* 
|| Listing 1.4:
|| Populating table TRBTKT.TICKETS with BasicFile LOBs 
*/

-----
-- Create new Trouble Ticket entries, each using a different
-- document but each using the same image
-----
SET SERVEROUTPUT ON
TRUNCATE TABLE trbtkt.tickets;

BEGIN

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 101
        ,description => 'Trouble Ticket 101'
        ,submit_dts => '2008-12-31 23:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_101.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 102
        ,description => 'Trouble Ticket 102'
        ,submit_dts => '2009-01-04 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_102.doc'
        ,imgFileName => 'Unresolved.jpg'
    );

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 103
        ,description => 'Trouble Ticket 103'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_103.doc'
        ,imgFileName => 'Unresolved.jpg'
     );

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 104
        ,description => 'Trouble Ticket 104'
        ,submit_dts => '2009-01-14 12:30:00'
        ,status => 'OPEN'
        ,docFileName => 'New_104.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 105
        ,description => 'Trouble Ticket 105'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 106
        ,description => 'Trouble Ticket 106'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 107
        ,description => 'Trouble Ticket 107'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 108
        ,description => 'Trouble Ticket 108'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 109
        ,description => 'Trouble Ticket 109'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 110
        ,description => 'Trouble Ticket 110'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );
    COMMIT;
   
END;
/

-----
-- Gather optimizer statistics
-----
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'TRBTKT', CASCADE => TRUE);
END;
/

/* 
|| Listing 1.5:
|| Populating table TRBTKT.SECURE_TICKETS with SecureFile LOBs
*/

INSERT INTO trbtkt.secure_tickets
SELECT * FROM trbtkt.tickets;

COMMIT;