DBMS_UTILITY, A Good Helping Hand For A DBA….

There are couple of stored packages which are not much popular but are immensely popular. If we use them in our daily routine tasks, there are many tasks which would be done very easily. Like this,there is a package called DBMS_UTILITY. I do talk about it a lot in a program of mine which talks about advanced usage of PL/SQL. This is an extremely useful package with many goodies coming along with it which can be handy. Just thought to share this package and some of the procedures that I use,

This is a very handy function and answers a question which is asked almost very often that on which operating system, oracle is running. Using this, it comes very easily like follow,
SQL> select dbms_utility.port_string from dual;



So I am running my Oracle 10201 on Windows NT based machine(Windows XP professional).


A very handy procedure to show the current database version and compatibility number. Surely, this can also come from V$version but the option is available from this procedure as well.
SQL>variable a varchar2(4000);
SQL>variable b varchar2(4000);
SQL> declare
  2  version varchar2(1000) default ‘Database Version’;
  3  compatible varchar2(1000) default ‘DB Compatibility’;
  4  begin
  5   dbms_utility.db_version(:a,:b);
  6  dbms_output.put_line(version||’====’||:a);
  7  dbms_output.put_line(compatible||’====’||:a);
  8  end;
  9  /
Database Version====
DB Compatibility====

PL/SQL procedure successfully completed.



This shows us the parameter value. Interestingly, this procedure doesn’t need DBA access to be avaiable to see the parameter values. Yuo can run it as a normal user as well and can see the parameter values.Note that this doesn’t apply to all the parameters that are present in the parameter file. So you need to play around a bit with it and check which one works and which doesn’t.Surely, this taks is not supposed to be for a normal user but just in case, if even a normal user needs to see the parameter values,this can come as useful.
SQL> conn new/new
SQL> set serveroutput on
SQL> declare
  2  intval number;
  3  strgval varchar2(400);
  4  begin
  5  intval:=dbms_utility.get_parameter_value(‘background_dump_dest’,intval,strgval);
  6  dbms_output.put_line(‘Value of the parameter Background Dump Dest is ‘||’==== ‘||strgval);
  7  end;
  8  /
Value of the parameter Session Cached Cursors is ====

PL/SQL procedure successfully completed.


Like the above, there are many procedures and functions which can be very useful depending on how and where you use them. Docs have a very good description of each of these so I suggest you refer to docs for the complete description and guidelines. Here is the link for the same(11g),

The idea of this blog post was just to give a "bump up" for this package which in my opinion can serve many purposes for a DBA. Hope it helps some where someone 🙂 .