/*
|| Oracle 11g SecureFiles Listing 2
||
|| Demonstrates Oracle Database 11g's SecureFiles features, including:
|| - Creation of sample objects and data
|| - Translating a table from BasicFile to SecureFile LOBs via DBMS_REDEFINITION
|| - Querying SecureFile LOB metadata
|| - Modifying SecureFile LOB attributes
|
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various examples 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 2.1:
|| Recreating tables with list partitioning to simulate a data warehousing environment
*/

-----
-- Recreate table TRBTKT.TICKETS, this time including a STATUS column
-----
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)
    PARTITION BY LIST (status) (
        PARTITION sts_open 
            VALUES ('OPEN')
       ,PARTITION sts_pending
            VALUES ('PENDING')
       ,PARTITION sts_closed
            VALUES ('CLOSED')
       ,PARTITION sts_other
            VALUES (DEFAULT)
    )
;

-- 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 an improved version of table TRBTKT.SECURE_TICKETS that uses 
-- partitioning to divide up the storage of SecureFile LOBS as appropriate
-- to their expected retention period based on the value of STATUS
-----
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
            CACHE
        )
   ,LOB(scrnimg)    
        STORE AS SECUREFILE (
            TABLESPACE securefiles
            DISABLE STORAGE IN ROW
            CACHE READS
        )
    PARTITION BY LIST (status) (
        PARTITION sts_open 
            VALUES ('OPEN')
                LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
               ,LOB (scrnimg)  STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS)
       ,PARTITION sts_pending
            VALUES ('PENDING')
                LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)
               ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
       ,PARTITION sts_closed
            VALUES ('CLOSED')
                LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)
               ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
       ,PARTITION sts_other
            VALUES (DEFAULT)
                LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
               ,LOB (scrnimg)  STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)
    )
;

-- 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';

/* 
|| Listing 2.2:
|| Reloading table TRBTKT.TICKETS with additional data
*/

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'
   );

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

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

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

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

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 205
        ,description => 'Trouble Ticket 205'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 206
        ,description => 'Trouble Ticket 206'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 207
        ,description => 'Trouble Ticket 207'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 208
        ,description => 'Trouble Ticket 208'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 209
        ,description => 'Trouble Ticket 209'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 210
        ,description => 'Trouble Ticket 210'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'OPEN'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );

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

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

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

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

    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 305
        ,description => 'Trouble Ticket 305'
        ,submit_dts => '2009-01-09 00:00:00'
        ,status => 'PENDING'
        ,docFileName => 'New_105.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 306
        ,description => 'Trouble Ticket 306'
        ,submit_dts => '2009-01-11 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_106.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 307
        ,description => 'Trouble Ticket 307'
        ,submit_dts => '2009-01-16 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_107.doc'
        ,imgFileName => 'DBRIssues.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 308
        ,description => 'Trouble Ticket 308'
        ,submit_dts => '2009-01-12 00:00:00'
        ,status => 'OPEN'
        ,docFileName => 'New_108.doc'
        ,imgFileName => 'Unresolved.jpg'
    );
    
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 309
        ,description => 'Trouble Ticket 309'
        ,submit_dts => '2009-01-02 00:00:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_109.doc'
        ,imgFileName => 'Unresolved.jpg'
     );
 
    trbtkt.pkg_securefiles.AddTroubleTicket (
         tkt_id => 310
        ,description => 'Trouble Ticket 310'
        ,submit_dts => '2009-01-14 12:45:00'
        ,status => 'CLOSED'
        ,docFileName => 'New_110.doc'
        ,imgFileName => 'DBRIssues.jpg'
   );

    COMMIT;
   
END;
/

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

/* 
|| Listing 2.3:
|| Translating table TRBTKT.TICKETS into TRBTKT.SECURE_TICKETS 
|| via DBMS_REDEFINTION
*/

DECLARE
    redefinition_errors PLS_INTEGER := 0;

BEGIN
    -----
    -- Attempt a redefinition mapping. Note that the COL_MAPPING parameter is set to
    -- NULL because all columns will be remapped and none are renamed
    -----
    DBMS_REDEFINITION.START_REDEF_TABLE (
         uname => 'TRBTKT'
        ,orig_table => 'TICKETS'
        ,int_table => 'SECURE_TICKETS'
        ,col_mapping => NULL
        ,options_flag => DBMS_REDEFINITION.CONS_USE_PK
    );

    -----
    -- Initiate the copy of rows from TRBTKT.TICKETS to TRBTKT.SECURE_TICKETS.
    -- Note that since a primary key constraint and index are already defined,
    -- the COPY_INDEXES and COPY_CONSTRAINTS directives are deactivated
    -----
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (
         uname => 'TRBTKT'
        ,orig_table => 'TICKETS'
        ,int_table => 'SECURE_TICKETS'
        ,copy_indexes => 0
        ,copy_triggers => TRUE
        ,copy_constraints => FALSE
        ,copy_privileges => TRUE
        ,ignore_errors => FALSE
        ,num_errors => redefinition_errors
        ,copy_statistics => FALSE
        ,copy_mvlog => FALSE
    );

    IF (redefinition_errors > 0) THEN
        DBMS_OUTPUT.PUT_LINE('>>> BasicFile to SecureFile Migration Errors: ' || TO_CHAR(redefinition_errors));
    END IF;

    -----
    -- Complete the redefinition process
    -----
    DBMS_REDEFINITION.FINISH_REDEF_TABLE (
         uname => 'TRBTKT'
        ,orig_table => 'TICKETS'
        ,int_table => 'SECURE_TICKETS'
    );

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_REDEFINITION.ABORT_REDEF_TABLE (
                 uname => 'TRBTKT'
                ,orig_table => 'TICKETS'
                ,int_table => 'SECURE_TICKETS'
            );

END;
/

/* 
|| Listing 2.4:
|| Querying metadata for BasicFile and SecureFile LOBs
*/

SET PAGESIZE 1000
SET LINESIZE 140 

-----
-- View: DBA_SEGMENTS
-- Shows metadata about individual BasicFile and SecureFile segments
-----
TTITLE 'LOB Segment Information|(from DBA_SEGMENTS)'
COL segment_name        FORMAT A30      HEADING 'Segment Name'
COL segment_type        FORMAT A20      HEADING 'Segment|Type'
COL segment_subtype     FORMAT A20      HEADING 'Segment|SubType'
COL partition_name      FORMAT A12      HEADING 'Partition|Name'
COL tablespace_name     FORMAT A12      HEADING 'Tablespace'
SELECT 
    segment_name
   ,segment_type
   ,segment_subtype
   ,partition_name
   ,tablespace_name
  FROM dba_segments
 WHERE owner = 'TRBTKT'
 ORDER BY segment_name
;
TTITLE OFF

-----
-- View: DBA_LOBS
-- Shows metadata about individual BasicFile and SecureFile LOBs
-----
TTITLE 'BasicFile and SecureFile LOBs Metadata|(from DBA_LOBS)'
COL table_name      FORMAT A14      HEADING 'Table'
COL segment_name    FORMAT A26      HEADING 'Segment'
COL column_name     FORMAT A10      HEADING 'Column'
COL tablespace_name FORMAT A12      HEADING 'Tablespace'
COL logging         FORMAT A08      HEADING 'Logging'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A07      HEADING 'Stored|In Row'
COL encrypt         FORMAT A07      HEADING 'Encryp-|tion'
COL compression     FORMAT A07      HEADING 'Compre-|ssion'
COL deduplication   FORMAT A08      HEADING 'DeDupli-|cation'
COL securefile      FORMAT A07      HEADING 'Secure|File?'
COL partitioned     FORMAT A07      HEADING 'Parti-|tioned'
SELECT
    table_name
   ,column_name
   ,segment_name
   ,tablespace_name
   ,logging
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
   ,partitioned
  FROM dba_lobs
 WHERE owner = 'TRBTKT'
 ORDER BY table_name, column_name
;
TTITLE OFF

-----
-- View: DBA_PART_LOBS
-- Shows default values for BasicFile and SecureFile LOBs
-----
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name          FORMAT A20      HEADING 'Table'
COL column_name         FORMAT A12      HEADING 'Column'
COL def_cache           FORMAT A12      HEADING 'Cached'
COL def_tablespace_name FORMAT A12      HEADING 'Tablespace'
COL def_securefile      FORMAT A12      HEADING 'SecureFile'
COL def_encrypt         FORMAT A12      HEADING 'Encrypted'
COL def_compress        FORMAT A12      HEADING 'Compressed'
COL def_deduplicate     FORMAT A12      HEADING 'DeDuplicated'
SELECT
    table_name
   ,column_name
   ,def_cache
   ,def_tablespace_name
   ,def_securefile
   ,def_compress
   ,def_deduplicate
   ,def_encrypt
  FROM dba_part_lobs
 WHERE table_owner = 'TRBTKT'
 ORDER BY table_name, column_name
;
TTITLE OFF

-----
-- View: DBA_LOB_PARTITIONS
-- Describes BasicFile and SecureFile settings at individual LOB level
-----
TTITLE 'BasicFile and SecureFile LOB Partitions|(from DBA_LOB_PARTITIONS)'
COL table_name      FORMAT A16      HEADING 'Table'
COL column_name     FORMAT A12      HEADING 'Column'
COL partition_name  FORMAT A12      HEADING 'Stored in|Partition'
COL cache           FORMAT A10      HEADING 'Cacheing'
COL in_row          FORMAT A10      HEADING 'Stored|In Row'
COL encrypt         FORMAT A10      HEADING 'Encrypted'
COL compression     FORMAT A10      HEADING 'Compressed'
COL deduplication   FORMAT A10      HEADING 'DeDupli-|cated'
COL securefile      FORMAT A10      HEADING 'SecureFile?'
SELECT
    table_name
   ,column_name
   ,partition_name
   ,cache
   ,in_row
   ,encrypt
   ,compression
   ,deduplication
   ,securefile
  FROM dba_lob_partitions
 WHERE table_owner = 'TRBTKT'
 ORDER BY table_name, column_name
;
TTITLE OFF

/* 
|| Listing 2.5:
|| Determining space utilization of BasicFile and SecureFile LOBs
*/

SET SERVEROUTPUT ON

-----
-- BasicFiles storage utilization:
-----
BEGIN
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'DOCUMENT'
        ,partname => 'STS_OTHER'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OPEN'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_PENDING'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_CLOSED'
    );
    trbtkt.pkg_securefiles.calc_space_basicfiles (
         ownname => 'TRBTKT'
        ,tabname => 'SECURE_TICKETS'
        ,colname => 'SCRNIMG'
        ,partname => 'STS_OTHER'
    );
END;
/
============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT Partition Name: STS_OPEN ------------------------------------------------------------ Full Blocks: 123 KB: .96 Unformatted Blocks: 379 KB: 2.96 Total Blocks: 123 Total KB: .96 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT Partition Name: STS_PENDING ------------------------------------------------------------ Full Blocks: 20 KB: .16 Unformatted Blocks: 482 KB: 3.77 Total Blocks: 20 Total KB: .16 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT Partition Name: STS_CLOSED ------------------------------------------------------------ Full Blocks: 37 KB: .29 Unformatted Blocks: 465 KB: 3.63 Total Blocks: 37 Total KB: .29 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT Partition Name: STS_OTHER ------------------------------------------------------------ Full Blocks: 0 KB: 0 Unformatted Blocks: 0 KB: 0 Total Blocks: 0 Total KB: 0 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_OPEN ------------------------------------------------------------ Full Blocks: 420 KB: 3.28 Unformatted Blocks: 82 KB: .64 Total Blocks: 420 Total KB: 3.28 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_PENDING ------------------------------------------------------------ Full Blocks: 66 KB: .52 Unformatted Blocks: 436 KB: 3.41 Total Blocks: 66 Total KB: .52 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_CLOSED ------------------------------------------------------------ Full Blocks: 144 KB: 1.13 Unformatted Blocks: 358 KB: 2.8 Total Blocks: 144 Total KB: 1.13 ============================================================ ============================================================ Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG Partition Name: STS_OTHER ------------------------------------------------------------ Full Blocks: 0 KB: 0 Unformatted Blocks: 0 KB: 0 Total Blocks: 0 Total KB: 0 ============================================================
----- -- SecureFiles storage utilization: ----- BEGIN trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'DOCUMENT' ,partname => 'STS_OPEN' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'DOCUMENT' ,partname => 'STS_PENDING' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'DOCUMENT' ,partname => 'STS_CLOSED' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'DOCUMENT' ,partname => 'STS_OTHER' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'SCRNIMG' ,partname => 'STS_OPEN' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'SCRNIMG' ,partname => 'STS_PENDING' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'SCRNIMG' ,partname => 'STS_CLOSED' ); trbtkt.pkg_securefiles.calc_space_securefiles ( ownname => 'TRBTKT' ,tabname => 'TICKETS' ,colname => 'SCRNIMG' ,partname => 'STS_OTHER' ); END; /
============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_OPEN ------------------------------------------------------------ Segment Blocks: 1024 KB: 8192 Used Blocks: 124 KB: 992 Expired Blocks: 882 KB: 7056 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_PENDING ------------------------------------------------------------ Segment Blocks: 1024 KB: 8192 Used Blocks: 21 KB: 168 Expired Blocks: 985 KB: 7880 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_CLOSED ------------------------------------------------------------ Segment Blocks: 1024 KB: 8192 Used Blocks: 13 KB: 104 Expired Blocks: 993 KB: 7944 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT Partition Name: STS_OTHER ------------------------------------------------------------ Segment Blocks: 512 KB: 4096 Used Blocks: 501 KB: 4008 Expired Blocks: 0 KB: 0 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_OPEN ------------------------------------------------------------ Segment Blocks: 2560 KB: 20480 Used Blocks: 405 KB: 3240 Expired Blocks: 2134 KB: 17072 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_PENDING ------------------------------------------------------------ Segment Blocks: 1024 KB: 8192 Used Blocks: 62 KB: 496 Expired Blocks: 944 KB: 7552 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_CLOSED ------------------------------------------------------------ Segment Blocks: 1024 KB: 8192 Used Blocks: 142 KB: 1136 Expired Blocks: 864 KB: 6912 Unexpired Blocks: 0 KB: 0 ============================================================ ============================================================ Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG Partition Name: STS_OTHER ------------------------------------------------------------ Segment Blocks: 512 KB: 4096 Used Blocks: 501 KB: 4008 Expired Blocks: 0 KB: 0 Unexpired Blocks: 0 KB: 0 ============================================================
SET SERVEROUTPUT ON /* || Listing 2.6: || Managing SecureFile LOBs attributes */
SQL> ALTER TABLE TRBTKT.TICKETS SQL> MODIFY LOB(DOCUMENT) (NOCOMPRESS); Table altered. SQL> ALTER TABLE TRBTKT.TICKETS SQL> MODIFY LOB(SCRNIMG) (KEEP_DUPLICATES); Table altered. SQL> ALTER TABLE TRBTKT.TICKETS SQL> MODIFY PARTITION STS_PENDING LOB(SCRNIMG) (COMPRESS HIGH); Table altered. SQL> ALTER TABLE TRBTKT.TICKETS SQL> MODIFY PARTITION STS_PENDING LOB(DOCUMENT) (DEDUPLICATE); Table altered.
/* || Report 2.1: || Results from BasicFIle and SecureFile LOBs metadata reports */
LOB Segment Information (from DBA_SEGMENTS) Segment Segment Partition Segment Name Type SubType Name Tablespace ------------------------------ -------------------- -------------------- ------------ ------------ SECURE_TICKETS TABLE PARTITION ASSM STS_PENDING USERS SECURE_TICKETS TABLE PARTITION ASSM STS_CLOSED USERS SECURE_TICKETS TABLE PARTITION ASSM STS_OPEN USERS SECURE_TICKETS TABLE PARTITION ASSM STS_OTHER USERS SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P180 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P179 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P178 BASICFILES SYS_IL0000072118C00005$$ INDEX PARTITION ASSM SYS_IL_P177 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P185 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P188 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P187 BASICFILES SYS_IL0000072118C00006$$ INDEX PARTITION ASSM SYS_IL_P186 BASICFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P194 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P193 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P195 SECUREFILES SYS_IL0000072144C00005$$ INDEX PARTITION ASSM SYS_IL_P196 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P204 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P203 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P202 SECUREFILES SYS_IL0000072144C00006$$ INDEX PARTITION ASSM SYS_IL_P201 SECUREFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P173 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P176 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P175 BASICFILES SYS_LOB0000072118C00005$$ LOB PARTITION ASSM SYS_LOB_P174 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P184 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P183 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P181 BASICFILES SYS_LOB0000072118C00006$$ LOB PARTITION ASSM SYS_LOB_P182 BASICFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P191 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P192 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P189 SECUREFILES SYS_LOB0000072144C00005$$ LOB PARTITION SECUREFILE SYS_LOB_P190 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P198 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P199 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P197 SECUREFILES SYS_LOB0000072144C00006$$ LOB PARTITION SECUREFILE SYS_LOB_P200 SECUREFILES TICKETS TABLE PARTITION ASSM STS_PENDING USERS TICKETS TABLE PARTITION ASSM STS_OTHER USERS TICKETS TABLE PARTITION ASSM STS_OPEN USERS TICKETS TABLE PARTITION ASSM STS_CLOSED USERS TICKETS_PK_IDX INDEX ASSM USERS
BasicFile and SecureFile LOBs Metadata (from DBA_LOBS) Stored Encryp- Compre- DeDupli- Secure Parti- Table Column Segment Tablespace Logging Cacheing In Row tion ssion cation File? tioned -------------- ---------- -------------------------- ------------ -------- ---------- ------- ------- ------- -------- ------- ------- SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005$$ BASICFILES NONE NO YES NONE NONE NONE NO YES SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006$$ BASICFILES NONE NO YES NONE NONE NONE NO YES TICKETS DOCUMENT SYS_LOB0000072144C00005$$ SECUREFILES NONE YES NO NO NO NO YES YES TICKETS SCRNIMG SYS_LOB0000072144C00006$$ SECUREFILES NONE CACHEREADS NO NO NO NO YES YES
BasicFile and SecureFile Partitioned LOB Default Settings (from DBA_PART_LOBS) Table Column Cached Tablespace SecureFile Compressed DeDuplicated Encrypted -------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ SECURE_TICKETS DOCUMENT NO BASICFILES NO NONE NONE NONE SECURE_TICKETS SCRNIMG NO BASICFILES NO NONE NONE NONE TICKETS DOCUMENT YES SECUREFILES YES NO NO NO TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO
BasicFile and SecureFile LOB Partitions (from DBA_LOB_PARTITIONS) Stored in Stored DeDupli- Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile ---------------- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ---------- SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO TICKETS DOCUMENT STS_OTHER YES NO NO HIGH LOB YES TICKETS DOCUMENT STS_PENDING YES NO NO NO NO YES TICKETS DOCUMENT STS_CLOSED YES NO NO MEDIUM LOB YES TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO MEDIUM LOB YES TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH LOB YES TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH LOB YES
/* || Report 2.2: || Query results against DBA_LOB_PARTITIONS after modifications to various || SecureFile LOBs have been applied */
BasicFile and SecureFile LOB Partitions (from DBA_LOB_PARTITIONS) Stored in Stored DeDupli- Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile ---------------- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ---------- SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO TICKETS DOCUMENT STS_OTHER YES NO NO NO LOB YES TICKETS DOCUMENT STS_PENDING YES NO NO NO LOB YES TICKETS DOCUMENT STS_CLOSED YES NO NO NO LOB YES TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO HIGH NO YES TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH NO YES TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH NO YES