Oracle DBMS_UTILITY package- How to use – learn with example
Oracle DBMS_UTILITY is a Built-In (Oracle supplied) Oracle package that provides a number of utility routine. The package helps to perform a number of taks which are very helpfull if you are required to do DBA related work. To be able to execute this package you will require execute grants from yuor DBA. If this is your own database and if your schema user does not have privilege to execute this package then you should login as sys user and grant execute privilege to this package to user application user schema.
You can run the following command syntax to give the grant to user SCOTT.
GRANT EXECUTE ON SYS.DBMS_UTILITY TO SCOTT;
Caution: Some of the commands that you can run are very long running processes – so take appropriate action if required i.e. inform other users or DBA if you want to recompile whole of your schema or analize the schema or database.
See the example how this package can be used to perform the following task:
.
How to find Oracle database object dependency using dbms_utility package
get_dependency: function returns the dependency of a given Oracle object. This is a procedure in Oracle built-in DBMS_UTILITY package.
Procedure signature:
procedure get_dependency (type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2);
Example:
This call returns the dependency of EMP table which is in SCOTT schema.
exec dbms_utility.get_dependency(‘TABLE’,'SCOTT’,'EMP’);
Learn more on dbms_utility in action:
How to get CPU time using Oracle dbms_utility package
get_cpu_time: functions returns the CPU time in 100th’s of a second. This is a function in Oracle built-in DBMS_UTILITY. Only available post Oracle8x versions.
Function signature:
function get_cpu_time return number;
Example:
declare
l_num number;
begin
l_num := dbms_utility.get_cpu_time;
dbms_output.put_line(l_num);
end;
Learn more on dbms_utility in action:
How to find Oracle Database Server cluster mode using dbms_utility package
is_cluster_database: functions returns boolean TRUE or FALSE to indicate whether the Oracle database server is running in cluster database server mode or not. This is a function is Oracle build-in DBMS_UTILITY package.
Function signature:
function is_cluster_database return boolean;
Example:
This anonymous pl/sql script will display message to tell you whether Oracle database server is running in CLUSTER mode or not.
set serverout on
declare
begin
if dbms_utility.is_cluster_database then
dbms_output.put_line(‘Your database is running in Parallel server mode’);
else
dbms_output.put_line(‘Your database is NOT running in Parallel server mode’);
end if;
end;
Learn more on dbms_utility in action:
How to find Oracle RAC Instance number using dbms_utility package
current_instance: function returns the instance number of the Oracle instance – applicable in RAC settings. This is a function ins Oracle built-in DBMS_UTILITY package.
Function signature:
function current_instance return number;
Example:
declare
l_num number;
begin
l_num := dbms_utility.current_instance;
dbms_output.put_line(l_num);
end;
/
Learn more on dbms_utility in action:
Recent Comments