PL/SQL Packages DBMS_UTILITY
Source : {ORACLE_HOME}/rdbms/admin/dbmsutil.sql
COMPILE_SCHEMA Compile all procedures, functions, packages, and triggers in the specified schema.
Syntax
DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);
Code examples
EXEC DBMS_UTILITY.compile_schema('SCOTT');
ANALYZE_SCHEMA Analyze all the tables, clusters, and indexes in a schema.
Syntax
DBMS_UTILITY.ANALYZE_SCHEMA (
schema VARCHAR2,
method VARCHAR2,
estimate_rows NUMBER DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
method_opt VARCHAR2 DEFAULT NULL);
Code examples
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE')
[N.B: Do not collect stats for either the SYS or SYSTEM schemas.] ANALYZE_DATABASE Analyze all the tables, clusters, and indexes in a database.
Syntax
DBMS_UTILITY.ANALYZE_DATABASE (
method VARCHAR2,
estimate_rows NUMBER DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
method_opt VARCHAR2 DEFAULT NULL);
Code examples
exec DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
ANALYZE_PART_OBJECT Equivalent to ANALYZE_DATABASE use for partition table.
Syntax
dbms_utility.analyze_part_object (
schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL,
object_type IN CHAR DEFAULT 'T',
command_type IN CHAR DEFAULT 'E',
command_opt IN VARCHAR2 DEFAULT NULL,
sample_clause IN VARCHAR2 DEFAULT 'sample 5 percent');
Code examples
--assumes a partitioned table named 'part_tab'
exec dbms_utility.analyze_part_object('UWCLASS', 'PART_TAB', 'T', 'E', 'V');
FORMAT_ERROR_STACK Function Format the current error stack.
Syntax
DBMS_UTILITY.FORMAT_CALL_STACK
RETURN VARCHAR2;
Code examples
CREATE TABLE error_log (
timestamp DATE,
username VARCHAR2(30),
instance NUMBER,
database_name VARCHAR2(50),
error_stack VARCHAR2(2000)
);
CREATE OR REPLACE TRIGGER LogErrors
AFTER SERVERERROR ON DATABASE
BEGIN
INSERT INTO error_log
VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
END LogErrors;
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE In Oracle Database 10g, Oracle added DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, which can and should be called in your exception handler. It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the exception was raised. Thus, you can call
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE within an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.
Syntax
DBMS_UTILITY. FORMAT_ERROR_BACKTRACE
RETURN VARCHAR2;
FORMAT_CALL_STACK Function Format the current call stack.
Code examples
CREATE OR REPLACE PROCEDURE error_test1 AS
BEGIN
RAISE VALUE_ERROR;
END error_test1;
/
CREATE OR REPLACE PROCEDURE error_test2 AS
BEGIN
error_test1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('FORMAT_CALL_STACK ->' || DBMS_UTILITY.FORMAT_CALL_STACK);
DBMS_OUTPUT.PUT_LINE('FORMAT_ERROR_STACK->' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END error_test2;
/
SQL> SET SERVEROUTPUT ON
SQL> exec error_test2
FORMAT_CALL_STACK ->----- PL/SQL Call Stack -----
object line object
handle number name
2F226BE0 6 procedure SYSTEM.ERROR_TEST2
2F222E7C 1 anonymous block
FORMAT_ERROR_STACK->ORA-06502: PL/SQL: numeric or value error
PL/SQL procedure successfully completed.
IS_CLUSTER_DATABASE Function Is this database running in cluster database mode.
Syntax
DBMS_UTILITY.IS_CLUSTER_DATABASE
RETURN NUMBER;
Code examples
SET SERVEROUTPUT ON
BEGIN
IF DBMS_UTILITY.is_cluster_database THEN
dbms_output.put_line('Clustered');
ELSE
dbms_output.put_line('Not Clustered');
END IF;
END;
/
GET_TIME Function Current time in 100th's of a second.
Syntax
DBMS_UTILITY.GET_TIME
RETURN NUMBER;
Code examples
BEGIN
dbms_output.put_line( 'Start Time: ' || dbms_utility.get_time );
dbms_lock.sleep(seconds => 10);
dbms_output.put_line( 'End Time: ' || dbms_utility.get_time );
END;
/
Start Time: 1119218
End Time: 1120218
PORT_STRING Function Return a string that uniquely identifies the version of Oracle and the operating system.
Syntax
DBMS_UTILITY.PORT_STRING
RETURN VARCHAR2;
Code examples
SELECT DBMS_UTILITY.port_string
FROM dual;
DB_VERSION Version information for the database.
Syntax
DBMS_UTILITY.DB_VERSION (
version OUT VARCHAR2,
compatibility OUT VARCHAR2);
Code examples
SET SERVEROUTPUT ON
DECLARE
v_version VARCHAR2(100);
v_compatibility VARCHAR2(100);
BEGIN
DBMS_UTILITY.db_version (version => v_version, compatibility => v_compatibility);
DBMS_OUTPUT.put_line('Version: ' || v_version || ' Compatibility: ' || v_compatibility);
END;
/
CURRENT_INSTANCE Function Return the current connected instance number.
Syntax
DBMS_UTILITY.CURRENT_INSTANCE
RETURN NUMBER;
Code examples
SELECT DBMS_UTILITY.current_instance
FROM dual;
ACTIVE_INSTANCES Active instance numbers and names
Syntax
DBMS_UTILITY.IS_CLUSTER_DATABASE
RETURN NUMBER;
Code examples
SET SERVEROUTPUT ON
DECLARE
v_instance_table DBMS_UTILITY.instance_table;
v_instance_count NUMBER;
BEGIN
DBMS_UTILITY.active_instances (instance_table => v_instance_table,instance_count => v_instance_count);
IF v_instance_count > 0 THEN
FOR i IN 1 .. v_instance_count LOOP
DBMS_OUTPUT.put_line(v_instance_table(i).inst_number || ' = ' || v_instance_table(i).inst_name);
END LOOP;
END IF;
END;
/
EXEC_DDL_STATEMENT Execute the DDL statement in parse_string.
Syntax
DBMS_UTILITY.EXEC_DDL_STATEMENT (
parse_string IN VARCHAR2);
Code examples
BEGIN
dbms_utility.exec_ddl_statement('create table emp_test as select * from scott.emp');
END;
/
GET_PARAMETER_VALUE Function Get an init.ora parameter.
Syntax
DBMS_UTILITY.GET_PARAMETER_VALUE (
parnam IN VARCHAR2,
intval IN OUT BINARY_INTEGER,
strval IN OUT VARCHAR2)
RETURN BINARY_INTEGER;
Code examples
DECLARE
parnam VARCHAR2(256);
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
BEGIN
partyp := dbms_utility.get_parameter_value('db_block_size',intval, strval);
dbms_output.put('parameter value is: ');
IF partyp = 1 THEN
dbms_output.put_line(strval);
ELSE
dbms_output.put_line(intval);
END IF;
IF partyp = 1 THEN
dbms_output.put('parameter value length is: ');
dbms_output.put_line(intval);
END IF;
dbms_output.put('parameter type is: ');
IF partyp = 1 THEN
dbms_output.put_line('string');
ELSE
dbms_output.put_line('integer');
END IF;
END;
/
parameter value is: 8192
parameter type is: integer
COMMA_TO_TABLE Convert a comma-separated list of names into a PL/SQL table of names.
Syntax
DBMS_UTILITY.COMMA_TO_TABLE (
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT UNCL_ARRAY);
Code examples
CREATE TABLE c2t_test (readline VARCHAR2(200));
INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;
SELECT * FROM c2t_test;
CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);
CREATE OR REPLACE PROCEDURE load_c2t_test IS
c_string VARCHAR2(250);
cnt BINARY_INTEGER;
my_table dbms_utility.lname_array;
BEGIN
FOR t_rec IN (SELECT * FROM c2t_test)
LOOP
dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);
my_table(1) := TRANSLATE(my_table(1), 'A"','A');
my_table(2) := TRANSLATE(my_table(2), 'A"','A');
my_table(3) := TRANSLATE(my_table(3), 'A"','A');
INSERT INTO test_import
(src_no, src_desc, load_date)
VALUES
(TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
END LOOP;
COMMIT;
END load_c2t_test;
/
exec load_c2t_test;
SELECT * FROM test_import;
TABLE_TO_COMMA Converts a PL/SQL table of names into a comma-separated list of names.
Syntax
DBMS_UTILITY.TABLE_TO_COMMA (
tab IN UNCL_ARRAY,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
NAME_RESOLVE Resolve the given name.
Syntax
DBMS_UTILITY.NAME_RESOLVE (
name IN VARCHAR2,
context IN NUMBER,
schema OUT VARCHAR2,
part1 OUT VARCHAR2,
part2 OUT VARCHAR2,
dblink OUT VARCHAR2,
part1_type OUT NUMBER,
object_number OUT NUMBER);
NAME_TOKENIZE Parse the given name.
Syntax
DBMS_UTILITY.NAME_TOKENIZE (
name IN VARCHAR2,
a OUT VARCHAR2,
b OUT VARCHAR2,
c OUT VARCHAR2,
dblink OUT VARCHAR2,
nextpos OUT BINARY_INTEGER);
Code examples
declare
v_a varchar2(30);
v_b varchar2(30);
v_c varchar2(30);
v_dblink varchar2(30);
v_next number;
type vcArray is table of varchar2(255);
v_names vcArray := vcArray( 'tamimdba@gmail.com','tamim@oracledba','tamim.xyz' );
begin
for i in 1 .. v_names.count
loop
begin
dbms_utility.name_tokenize(name => v_names(i),
a => v_a,
b => v_b,
c => v_c,
dblink => v_dblink,
nextpos=> v_next );
dbms_output.put_line( 'name ' || v_names(i) );
dbms_output.put_line( 'A ' || v_a );
dbms_output.put_line( 'B ' || v_b );
dbms_output.put_line( 'C ' || v_c );
dbms_output.put_line( 'dblink ' || v_dblink );
dbms_output.put_line( 'next ' || v_next || ' ' || length(v_names(i)));
exception
when others then
dbms_output.put_line( 'name ' || v_names(i) );
dbms_output.put_line( sqlerrm );
end;
end loop;
end;
GET_HASH_VALUE Function Compute a hash value for the given string.
Syntax
DBMS_UTILITY.GET_HASH_VALUE (
name VARCHAR2,
base NUMBER,
hash_size NUMBER)
RETURN NUMBER;
Code examples
begin
dbms_output.put_line ( dbms_utility.get_hash_value( to_char(1), 0, 1024 ) );
end;
DATA_BLOCK_ADDRESS_FILE Function Get the file number part of a data block address.
Syntax
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (
dba NUMBER)
RETURN NUMBER;
MAKE_DATA_BLOCK_ADDRESS Function Create a data block address given a file number and a block number.
Syntax
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS (
file NUMBER,
block NUMBER)
RETURN NUMBER;
Code examples
SELECT dbms_utility.make_data_block_address(4, 6)
FROM dual;
DATA_BLOCK_ADDRESS_BLOCK Function Get the block number part of a data block address.
Syntax
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (
dba NUMBER)
RETURN NUMBER;
Code examples
SELECT file_name, file_id
FROM dba_data_files;
SELECT dbms_utility.data_block_address_file(16777222)
FROM dual;
