Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Analyzes the raw profiler output and produces hierarchical profiler information in database tables
Overload 1
dbms_hprof.analyze(
location IN VARCHAR2,
filename IN VARCHAR2,
summary_mode IN BOOLEAN DEFAULT FALSE,
trace IN VARCHAR2 DEFAULT NULL,
skip IN PLS_INTEGER DEFAULT 0,
collect IN PLS_INTEGER DEFAULT NULL,
run_comment IN VARCHAR2 DEFAULT NULL,
profile_uga IN BOOLEAN DEFAULT NULL,
profile_pga IN BOOLEAN DEFAULT NULL)
RETURN NUMBER;
PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');
See DBMS_HPROF Demo Below
Overload 2
dbms_hprof.analyze(
location IN VARCHAR2,
filename IN VARCHAR2,
summary_mode IN BOOLEAN DEFAULT FALSE,
trace IN VARCHAR2 DEFAULT NULL,
skip IN PLS_INTEGER DEFAULT 0,
collect IN PLS_INTEGER DEFAULT NULL,
profile_uga IN BOOLEAN DEFAULT NULL,
profile_pga IN BOOLEAN DEFAULT NULL);
PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');
See DBMS_HPROF Demo Below
Overload 3
dbms_hprof.analyze(
location IN VARCHAR2,
filename IN VARCHAR2,
report_clob OUT CLOB,
trace IN VARCHAR2 DEFAULT NULL,
skip IN PLS_INTEGER DEFAULT 0,
collect IN PLS_INTEGER DEFAULT NULL,
profile_uga IN BOOLEAN DEFAULT NULL,
profile_pga IN BOOLEAN DEFAULT NULL)
RETURN NUMBER;
PRAGMA DEPRECATE(analyze, 'analyze file overload is deprecated.');
See DBMS_HPROF Demo Below
Overload 4
dbms_hprof.analyze(
trace_id IN NUMBER,
summary_mode IN BOOLEAN DEFAULT FALSE,
trace IN VARCHAR2 DEFAULT NULL,
skip IN PLS_INTEGER DEFAULT 0,
collect IN PLS_INTEGER DEFAULT NULL,
run_comment IN VARCHAR2 DEFAULT NULL,
profile_uga IN BOOLEAN DEFAULT NULL,
profile_pga IN BOOLEAN DEFAULT NULL)
RETURN NUMBER;
See DBMS_HPROF Demo Below
Overload 5
dbms_hprof.analyze(
trace_id IN NUMBER,
report_clob OUT CLOB,
trace IN VARCHAR2 DEFAULT NULL,
skip IN PLS_INTEGER DEFAULT 0,
collect IN PLS_INTEGER DEFAULT NULL,
profile_uga IN BOOLEAN DEFAULT NULL,
profile_pga IN BOOLEAN DEFAULT NULL);
INSERT INTO sources_import
(sourceno, sizeno, status, latitude, longitude, testfor)
VALUES
(Fld1, Fld2, Fld3, Fld4, Fld5, Fld6);
ELSE
vLineNo := 2;
END IF;
END LOOP;
COMMIT;
utl_file.fclose(v_InHandle);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Option');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid Path');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, 'Invalid operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
WHEN OTHERS THEN
RAISE;
END;
EXCEPTION
WHEN NoFileToLoad THEN
dbms_output.put_line('No File To Load Was Found');
WHEN OTHERS THEN
MyErrm := SQLERRM;
dbms_output.put_line(MyErrm);
END load_sources_import;
/
Comma To Table Procedure
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE c2t_demo IS
my_table dbms_utility.uncl_array;
cnt BINARY_INTEGER;
c_string VARCHAR2(250);
CURSOR t_cur IS
SELECT readline
FROM gtt_c2t;
t_rec t_cur%ROWTYPE;
BEGIN
OPEN t_cur;
LOOP
FETCH t_cur INTO t_rec;
EXIT WHEN t_cur%NOTFOUND;
-- move the value from the cursor to the VARCHAR2 variable
c_string := t_rec.readline;
-- use the built-in package to break it up
dbms_utility.comma_to_table(c_string, cnt, my_table);
-- use TRANSLATE to remove the single and double quotes
my_table(1) := TRANSLATE(my_table(1), '1"''', '1');
my_table(2) := TRANSLATE(my_table(2), '1"''', '1');
my_table(3) := TRANSLATE(my_table(3), '1"''', '1');
my_table(4) := TRANSLATE(my_table(4), '1"''', '1');
my_table(5) := TRANSLATE(my_table(5), '1"''', '1');
my_table(6) := TRANSLATE(my_table(6), '1"''', '1');
INSERT INTO sources_import
(sourceno, sizeno, status,
latitude, longitude, testfor)
VALUES
(my_table(1), my_table(2), my_table(3),
my_table(4), my_table(5), my_table(6));
END LOOP;
COMMIT;
CLOSE t_cur;
END c2t_demo;
/
First Procedure To Load Intermediary Table And Replace Single QuotesWith Double Quotes
vInHandle utl_file.file_type;
BEGIN
StartTime := dbms_utility.get_time;
vInHandle := utl_file.fopen(vLoc, vFileName, 'r');
LOOP
BEGIN
utl_file.get_line(vInHandle, vNewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
-- find location of the delimiting commas
BEGIN
IF vFirstLine <> 1 THEN
INSERT INTO gtt_c2t
(readline)
VALUES
(vNewLine);
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END LOOP;
-- close the text file
utl_file.fclose(vInHandle);
DELETE FROM gtt_c2t
WHERE readline LIKE '%SOURCENO%';
UPDATE gtt_c2t
SET readline = TRANSLATE(readline, 'A''', 'A"');
c2t_demo; -- 2nd procedure that parses record
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20051, 'Invalid Option');
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20052, 'Invalid Path');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20053, 'Invalid Filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20054, 'Invalid operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20055, 'Read Error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20057, 'Internal Error');
WHEN OTHERS THEN
RAISE;
END load_c2t_test;
/
TYPE profarray IS TABLE OF sources_import%ROWTYPE
INDEX BY BINARY_INTEGER;
l_data profarray;
BEGIN
v_InHandle := utl_file.fopen(vLoc, vFileName, 'r');
vLineNo := 1;
LOOP
BEGIN
utl_file.get_line(v_InHandle, vNewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
vLineNo := vLineNo+1;
END LOOP;
utl_file.fclose(v_InHandle);
FORALL i IN 1..l_data.COUNT
INSERT INTO sources_import VALUES l_data(i);
DELETE FROM sources_import WHERE sourceno = 'SOURCENO';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END blended;
/
Profiling Demo
Procedure To Empty Profiler Tables Between Runs
-- this procedure must be created after running dbmshptab.sql or it is
-- necessary to recompile the procedure after the script has been run.
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE profreset IS
BEGIN
DELETE FROM dbmshp_function_info;
DELETE FROM dbmshp_parent_child_info;
DELETE FROM dbmshp_runs;
COMMIT;
END profreset;
/
dbms_output.put_line('Profiler Run #: ' || TO_CHAR(i));
END;
/
set linesize 121
col run_timestamp format a30
col run_comment format a25
col namespace format a10
col function format a25
col module format a20
col owner format a10
col type format a15
desc dbmshp_runs
SELECT runid, run_timestamp, total_elapsed_time, run_comment
FROM dbmshp_runs;
desc dbmshp_function_info
SELECT symbolid, owner, module, type, function, line#, namespace
FROM dbmshp_function_info;
SELECT function, line#, namespace, subtree_elapsed_time, function_elapsed_time, calls
FROM dbmshp_function_info
WHERE runid = 1;
desc dbmshp_parent_child_info
-- symid values reference dbms_function_info.symbolid
SELECT parentsymid, childsymid, subtree_elapsed_time, function_elapsed_time, calls
FROM dbmshp_parent_child_info
WHERE runid = 1;
