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