/*
|| 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