/*
|| Oracle 10g PL/SQL and SQL Enhancements - Listing 1
||
|| Contains examples of new and improved Oracle 10g PL/SQL features, including:
||
|| - Encryption and decryption with DBMS_CRYPTO
|| - Improved database monitoring with DBMS_MONITOR and DBMS_SERVER_ALERT
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's
|| new and improved PL/SQL supplied packages DataPump features and should be
|| carefully proofread before executing it against any existing Oracle database
|| to insure that no potential damage can occur.
||
*/
-----
-- Listing 1.1: Using DBMS_CRYPTO to encrypt and decrypt data
-----
CREATE OR REPLACE PACKAGE hr.pkg_sensitive_data
/*
|| Describes public interface for sample functions to encrypt and decrypt
|| character and numeric data using DBMS_CRYPTO methods.
*/
IS
FUNCTION encryptor (
input_string IN VARCHAR2
) RETURN RAW;
FUNCTION decryptor (
INPUT_STRING IN VARCHAR2
) RETURN VARCHAR2;
END pkg_sensitive_data;
/
CREATE OR REPLACE PACKAGE BODY pkg_sensitive_data
/*
|| Implements public methods for sample functions to encrypt and decrypt
|| character and numeric data using DBMS_CRYPTO methods.
*/
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;
-----
-- Defined Encryption Methods:
-- DES_CBC_NONE: Data Encryption Standard Block Cipher, Cipher Block '
-- chaining, no padding
-- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher
-- chaining, pad with zeroes
-----
DES_CBC_NONE CONSTANT PLS_INTEGER :=
DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_NONE;
SH1_ECB_ZERO CONSTANT PLS_INTEGER :=
DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_ZERO;
FUNCTION encryptor (
input_string IN VARCHAR2
) RETURN RAW
IS
-- Local variables
seed VARCHAR2(64) :=
'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0';
converted_seed RAW(64);
converted_string RAW(64);
encrypted_string RAW(64);
BEGIN
-- Convert incoming string and supplied seed to RAW datatype using the
-- new UTLI18N package to convert the string to the AL32UTF8 character
-- set
converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
converted_seed := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8');
-- Encrypt the RAW value using the ENCRYPT function and the appropriate
-- encryption type
encrypted_string :=
DBMS_CRYPTO.ENCRYPT(
src => converted_string
,typ => SH1_ECB_ZERO
,key => converted_seed
,iv => NULL);
RETURN encrypted_string;
EXCEPTION
WHEN OTHERS THEN
SQLERRMSG := SQLERRM;
SQLERRCDE := SQLCODE;
RETURN NULL;
END encryptor;
FUNCTION decryptor (
input_string IN VARCHAR2
) RETURN VARCHAR2
IS
-- Local variables
converted_string VARCHAR2(64);
seed VARCHAR2(64) :=
'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0';
converted_seed RAW(64);
decrypted_string VARCHAR2(64);
BEGIN
-- Convert incoming string and supplied seed to RAW datatype using the
-- new UTLI18N package to convert the string to the AL32UTF8 character
-- set
converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
converted_seed := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8');
-- Encrypt the RAW value using the ENCRYPT function and the appropriate
-- encryption type
decrypted_string :=
DBMS_CRYPTO.DECRYPT(
src => input_string
,typ => SH1_ECB_ZERO
,key => converted_seed
,iv => NULL);
-- Convert incoming string to RAW datatype, using the UTLI18N package
-- to convert the string to the AL32UTF8 character set
converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');
RETURN converted_string;
EXCEPTION
WHEN OTHERS THEN
SQLERRMSG := SQLERRM;
SQLERRCDE := SQLCODE;
RETURN NULL;
END decryptor;
END pkg_sensitive_data;
/
-----
-- Listing 1.2: Demonstrate usage of DBMS_CRYPTO
-----
-- Create a table for storing sensitive information
CREATE TABLE hr.emp_secured_data (
employee_id NUMBER PRIMARY KEY
,ssn VARCHAR2(64) NOT NULL
,credit_card_nbr VARCHAR2(64) NOT NULL
);
-- Load some test data and encrypt it with ENCRYPTOR function
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr)
VALUES (
301
,HR.pkg_sensitive_data.encryptor('345678901')
,HR.pkg_sensitive_data.encryptor('372812345630100')
);
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr)
VALUES (
302
,HR.pkg_sensitive_data.encryptor('456789012')
,HR.pkg_sensitive_data.encryptor('372812345630200')
);
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr)
VALUES (
303
,HR.pkg_sensitive_data.encryptor('789012345')
,HR.pkg_sensitive_data.encryptor('372812345630300')
);
COMMIT;
-- Read sensitive values from table using DECRYPTOR function
SQL> COL employee_id FORMAT 9999999 HEADING 'EmpID'
SQL> COL dcr_ssn FORMAT A9 HEADING 'SocSecNbr'
SQL> COL dcr_ccn FORMAT A16 HEADING 'Credit Card'
SQL> SELECT
2 employee_id
3 ,HR.pkg_sensitive_data.decryptor(ssn) dcr_ssn
4 ,HR.pkg_sensitive_data.decryptor(credit_card_nbr) dcr_ccn
5 FROM HR.emp_secured_data;
EmpID SocSecNbr Credit Card
-------- --------- ----------------
301 345678901 372812345630100
302 456789012 372812345630200
303 789012345 372812345630300
-----
-- Listing 1.3: Create a new table and package to illustrate
-- DBMS_MONITOR features
-----
-- Create table
CREATE TABLE sh.orders
(
order_id NUMBER(10) NOT NULL,
product_id NUMBER(10) NOT NULL,
qty NUMBER(15,2) NOT NULL,
status CHAR(1) DEFAULT 'P' NOT NULL
)
TABLESPACE example
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
ALTER TABLE sh.orders
ADD CONSTRAINT orders_pk PRIMARY KEY (order_id)
USING INDEX
TABLESPACE EXAMPLE
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
ALTER TABLE sh.orders
ADD CONSTRAINT status_ck
CHECK (status IN ('C','P'));
-----
-- Create the new Order Entry package specification and body
-----
CREATE OR REPLACE PACKAGE sh.pkg_order_entry
/*
|| Specifies public interface for Order Entry functions
*/
IS
TYPE curQuery IS REF CURSOR
RETURN sh.orders%ROWTYPE;
PROCEDURE AddNewOrder (
OrderID IN sh.orders.order_id%TYPE
,ProductID IN sh.orders.product_id%TYPE
,Quantity IN sh.orders.qty%TYPE
);
PROCEDURE UpdateOrderStatus;
PROCEDURE DisplayOrders(
ProductID IN sh.orders.product_id%TYPE
,curResults OUT sh.pkg_order_entry.curquery
);
END;
/
CREATE OR REPLACE PACKAGE BODY sh.pkg_order_entry
/*
|| Implements public interface for Order Entry functions
*/
IS
PROCEDURE AddNewOrder (
OrderID IN sh.orders.order_id%TYPE
,ProductID IN sh.orders.product_id%TYPE
,Quantity IN sh.orders.qty%TYPE
)
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'AddNewOrder'
,action_name => 'Initialization'
);
INSERT INTO orders (order_id, product_id, qty, status)
VALUES (OrderID, ProductID, Quantity, 'P');
COMMIT;
END AddNewOrder;
PROCEDURE UpdateOrderStatus
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'UpdateOrderStatus'
,action_name => 'GetStatus'
);
UPDATE sh.orders
SET status = 'C'
WHERE status = 'P';
COMMIT;
END UpdateOrderStatus;
PROCEDURE DisplayOrders (
ProductID IN sh.orders.product_id%TYPE
,curResults OUT sh.pkg_order_entry.curquery
)
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'DisplayOrders'
,action_name => 'GetOrders'
);
OPEN curResults FOR
SELECT *
FROM sh.orders
WHERE product_id = ProductID;
END DisplayOrders;
END;
/
-----
-- Listing 1.4: Monitoring a client session's activity and statistics
-- with DBMS_MONITOR
-----
-- Enable SQL tracing for a given database session ID (SID)
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(161);
-- Disable SQL tracing for a given database session ID (SID)
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(161);
-- Start gathering statistics for specific Module(s) and Action(s)
BEGIN
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'AddNewOrder'
,action_name => 'Initialization'
);
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'UpdateOrderStatus'
,action_name => 'SetStatus'
);
DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'DisplayOrders'
,action_name => 'GetOrders'
);
END;
/
-- Stop gathering statistics
BEGIN
DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'AddNewOrder'
,action_name => 'Initialization'
);
DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'UpdateOrderStatus'
,action_name => 'SetStatus'
);
DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
service_name => 'zdcdb.zerodefect.com'
,module_name => 'DisplayOrders'
,action_name => 'GetOrders'
);
END;
/
-----
-- Listing 1.5: Reviewing statistics gathered for PKG_ORDER_ENTRY by querying
-- V$SERV_MOD_ACT_STATS dynamic view
-----
SQL> COL module FORMAT A18 HEADING 'Module'
SQL> COL action FORMAT A14 HEADING 'Action'
SQL> COL stat_name FORMAT A32 HEADING 'Statistic' WRAP
SQL> COL value FORMAT 9999999 HEADING 'Value'
SQL> SELECT
2 MODULE
3 ,action
4 ,stat_name
5 ,VALUE
6 FROM v$serv_mod_act_stats
7 WHERE value <> 0
8 ORDER BY
9 MODULE
10 ,action
11 ,stat_name;
Module Action Statistic Value
------------------ -------------- -------------------------------- --------
AddNewOrder Initialization DB CPU 1795
AddNewOrder Initialization DB time 1795
AddNewOrder Initialization execute count 10
AddNewOrder Initialization opened cursors cumulative 9
AddNewOrder Initialization parse count (total) 9
AddNewOrder Initialization parse time elapsed 345
AddNewOrder Initialization sql execute elapsed time 1161
AddNewOrder Initialization user calls 12
8 rows selected.
-----
-- Listing 1.6: Setting up an alert about a server's status with
-- DBMS_SERVER_ALERT
-----
-----
-- Set "tablespace full" warning and critical thresholds of 10% and 20%
-- respectively for USERS tablespace. (Note that these are absurdly low warning
-- and critical threshold values, but are set low for illustration purposes.)
-----
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL
,warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,warning_value => '10'
,critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE
,critical_value => '20'
,observation_period => 1
,consecutive_occurrences => 3
,instance_name => 'ZDCDB'
,object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE
,object_name => 'USERS'
);
END;
/
-----
-- Listing 1.7: Show threshold settings, including recent alerts
-----
-- Are there any outstanding alerts?
SELECT *
FROM dba_outstanding_alerts;
-- Display the arguments for alert thresholds
SELECT *
FROM sys.dba_alert_arguments;
-- Show threshold alert history for last 12 hours
SELECT
object_type
,object_name
,reason
,suggested_action
,time_suggested
,resolution
,advisor_name
,metric_value
,message_type
,message_group
,message_level
FROM dba_alert_history
WHERE creation_time >= SYSDATE - 12/24
AND resolution <> 'cleared'
ORDER BY time_suggested DESC;
-- Show selected thresholds, including default system thresholds
COL metrics_name FORMAT A24 HEADING 'Metrics Name'
COL warning FORMAT A08 HEADING 'Warning'
COL critical FORMAT A08 HEADING 'Danger!'
COL obs_per FORMAT 99999 HEADING 'Obsv|Per'
COL cons_occur FORMAT 99999 HEADING 'Cons|Occur'
COL object_type FORMAT A10 HEADING 'Object|Type'
COL object_name FORMAT A10 HEADING 'Object|Name'
SELECT
metrics_name
,warning_operator || ' ' || warning_value warning
,critical_operator || ' ' || critical_value critical
,observation_period obs_per
,consecutive_occurrences cons_occur
,object_type
,object_name
FROM sys.dba_thresholds
WHERE object_type = 'TABLESPACE';