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:
.
DBMS_LOCK – Sleep functionality (Procedure) in Oracle
HTML clipboardDBMS_LOCK is an Oracle supplied in-built package which provides interface to Oracle’s Lock management services. Using this package you can lock an object in specific mode, give a name to the lock and release it when lock is no more required.
However the subject of this posting is not locks but Seep procedure in the DBMS_LOCK package.
In most of the programming languages you will find some form of technique where you can wait/sleep for some specified amount of time. This SLEEP/WAIT functionality may be required for different number of reason. For example, suppose you want to insert a record into a table every 5 seconds. In Microsoft SQL Server you will just use “waitfor” command which is a part of the T-SQL programming language. But how will you do that in Oracle. Here is how:.
Example: This example will insert a record in table t_sleep_test. Then wait for 5 seconds. Then insert another record.
1. Create table t_sleep_test
DBMS_SCHEDULER: Learn with Example
This is very useful Oracle supplied package which is used to run scheduled job in Oracle databases. Released with Oracle 10g this package replaces DBMS_JOB which was available with previous version of Oracle databases. DBMS_JOB still runs in Oracle 10g and even in 11g but that is used only for backward compatibility and should not be used in developing new solutions. Where possible it is advisable to convert legacy jobs which uses DBMS_JOB to use more powerful and robust DBMS_SCHEDULER.
Like DBMS_JOB package, the DBMS_SCHEDULER is a collection of functions and procedures intended to make job scheduling in Oracle more robust and easy to use.
PRIVILEGES:
===========================
To create and runs job in Oracle database you will need CREATE JOB role. To perform administrative tasks you will need SCHEDULER_ADMIN role.
Depending on the requirement and your installation environment you may like to grant the following privileges:
CREATE ANY JOBS
CREATE EXTERNAL JOBS
CREATING A SCHEDULED JOB:
===========================
The package is supplied to help perform some database tasks such as running a stored procedure or package at some given time and at some given interval.
And example is here below:
dbms_scheduler.create_job( job_name=>’Myschema.Daily_Emp_Report’,
job_type=>’STORED_PROCEDURE’,
job_action=>’Myschema.p_daily_emp_teport’,
number_of_arguments=>0,
start_date=>TRUNC(SYSDATE), repeat_interval=>’FREQ=WEEKLY;BYDAY=SUN;BYHOUR=07;BYMINUTE=00; BYSECOND=00′,
end_date=>NULL,
job_class=> ‘DEFAULT_JOB_CLASS’,
enabled=>TRUE,
auto_drop=>FALSE,
comments=>NULL);
In this example I am creating a scheduled Oracle job named Myschema.Daily_Emp_Report. When run, this job will execute a STORED PROCEDURE called p_daily_emp_teport. The stored procedure p_daily_emp_teport resides in a schema called Myschema. The job will run on every week Sunday at 07:00.
UTL_FILE – Writing to an Operating System File
UTL_FILE
========
This package is supplied by Oracle and is used to read an operating system file or write to
an operating system file.
So if you are thinking of reading and processing data fro man operating system file or writing
to a file to generate a report or something like that then you will definitely need to use this
handy Oracle supplied package.
ACCESSING OPERATING SYSTEM FILE STRUCTURE
=========================================
To access an operating system directory structure for read/write the directory need to be defined first.
In earlier versions of Oracle (i.e. Oracle 8.0.4) the init.ora parameter UTL_FILE_DIR is used define
the directories to be used in UTL_FILE. While using that parameter it is necessary to re-start Oracle
database so that your Oracle instance understands this parameter.
The parameter is defined as:
UTL_FILE_DIR=’/home/oracle/dave/’ — for single directory
or
UTL_FILE_DIR=’/home/oracle/dave/’, ‘/home/oracle/tom/’, ‘/home/oracle/jim/’ –for multiple directories
UTL_FILE – How to read data from a file
UTL_FILE
========
This package is supplied by Oracle and is used to read an operating system file or write to
an operating system file.
So if you are thinking of reading and processing data fro man operating system file or writing
to a file to generate a report or something like that then you will definitely need to use this
handy Oracle supplied package.
ACCESSING OPERATING SYSTEM FILE STRUCTURE
=========================================
To access an operating system directory structure for read/write the directory need to be defined first.
In earlier versions of Oracle (i.e. Oracle 8.0.4) the init.ora parameter UTL_FILE_DIR is used define
the directories to be used in UTL_FILE. While using that parameter it is necessary to re-start Oracle
database so that your Oracle instance understands this parameter.
The parameter is defined as:
UTL_FILE_DIR=’/home/oracle/dave/’ — for single directory
or
UTL_FILE_DIR=’/home/oracle/dave/’, ‘/home/oracle/tom/’, ‘/home/oracle/jim/’ –for multiple directories
Or a command line facility is also available such as
alter system set utl_file_dir=’/home/oracle/dave/’ scope=spfile
However later versions of Oracle introduced CREATE DIRECTORY command where you can simply create a
directory and use it in UTL_FILE. For example to create such a directory run command which will look
like:
CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS ‘/home/oracle/Dave/’;
Note: To be able to create a directory you will need CREATE ANY DIRECTORY privilege.
Then you can use the DIR_MYTEMP directory in your code with UTL_FILE:
Recent Comments