Archive

Archive for the ‘DBMS_PACKEGE’ Category

utl_match package for string matching

February 15, 2010 1 comment

There are four functions included in the package utl_match use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source string into the destination string.

Source: $ORACLE_HOME/rdbms/admin/utlmatch.sql

Oracle added the UTL_MATCH package in Version 10gRelease 2 to compare strings. The four functions included in the package use different methods to compare a source string and destination string, and return an assessment of what it would take to turn the source into the destination string. The functions are broken down into two categories. The categories are actually the algorithms employed to analyze the strings.

Levenshtein Distance

The Levenshtein Distance (LD) algorithm, commonly called the Edit Distance (ED) algorithm, is the older of the two supported methods. It measures the distance between the source and destination strings. By distance, we’re referring to the number of changes required to turn the source string into the destination string.

Jaro-Winkler

The Jaro-Winkler algorithm is the second category of algorithms used in UTL_ MATCH. These functions take the same two arguments, but instead of simply calculating the number of steps required to change the source string to the destination string, it determines how closely the two strings agree with each other. The algorithm also tries to take into account the possibility of a data entry error when determining similarity.

Procedures/Functions

edit_distance

Returns the number of changes required to turn the source string into the destination string using the Levenshtein Distance algorithm.

function edit_distance returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

edit_distance_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match.

function edit_distance_similarity returns binary_integer (
 s1    in        varchar2,
 s2    in        varchar2       
);

jaro_winkler

Instead of simply calculating the number of steps required to change the source string to the destination string returns similarity based on Jaro-Winkler distance algorithm, determines how closely the two strings agree with each other and tries to take into account the possibility of a data entry error.

function jaro_winkler returns binary_double (
 s1    in       varchar2,
 s2    in       varchar2       
);

jaro_winkler_similarity

Returns an integer between 0 and 100, where 0 indicates no similarity at all and 100 indicates a perfect match but tries to take into account possible data entry errors.

function jaro_winkler_similarity returns binary_integer (
 s1    in                 varchar2,
 s2    in                 varchar2       
);

Example 1:

SQL> SELECT UTL_MATCH.EDIT_DISTANCE('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
         1

SQL> SELECT UTL_MATCH.EDIT_DISTANCE_SIMILARITY('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        88

SQL> SELECT UTL_MATCH.JARO_WINKLER('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
 9.25E-001

SQL> SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY ('espresso', 'expresso')
  2    AS DISTANCE
  3  FROM DUAL;

  DISTANCE
----------
        92

Example 2:

CREATE TABLE TEST
( 
        test_id        number primary key, 
        test_name      varchar2(20), 
        test_date      date
);  

INSERT INTO TEST values(1, 'Sony CD Player', '20-FEB-2010');
INSERT INTO TEST values(2, 'Sony CD Player', '24-FEB-2010');
INSERT INTO TEST values(3, 'Pioneer DVD Player', '25-FEB-2010');
INSERT INTO TEST values(4, 'Sony CD Player', '25-FEB-2010');
INSERT INTO TEST values(5, 'Bose Speaker', '22-FEB-2010');
INSERT INTO TEST values(6, 'Tascam CD Burner', '25-FEB-2010');
INSERT INTO TEST values(7, 'Nikon digital camera', '22-FEB-2010');
INSERT INTO TEST values(8, 'Canon digital camera', '26-FEB-2010');

Commit;

Select TEST_ID,TEST_NAME,TEST_DATE
FROM TEST
WHERE  UTL_MATCH.JARO_WINKLER_SIMILARITY(test_name,'dogotal') > 60;

TEST_ID                TEST_NAME            TEST_DATE
---------------------- -------------------- -------------------------
7                      Nikon digital camera 22-FEB-00
8                      Canon digital camera 26-FEB-00

Advertisements
Categories: DBMS_PACKEGE, UTL_MATCH Tags:

DBMS_JOB package

December 13, 2009 1 comment

The DBMS_JOB package allows a user to schedule a job to run at a specified time.  A job is submitted to a job queue and runs at the specified time.  The user can also input a parameter that specifies how often the job should run.  A job can consist of any PL/SQL code.

Source: {ORACLE_HOME}/rdbms/admin/dbmsjob.sq

SNP Background Processes

SNP processes run in the background and implement database snapshots and job queues.  If an SNP process fails, Oracle restarts it without affecting the rest of the database.  An SNP process can run one job at a time with a maximum of ten SNP processes running simultaneously.  The INIT.ORA initialization file contains three parameters that control the behavior of the SNP processes:

Parameters Description
JOB_QUEUE_PROCESSES How many processes to start.  If set to zero, no jobs are executed. Default is 0.  Range is 0..1000.
JOB_QUEUE_INTERVAL (obsolete in 9i) How long an interval the process will sleep before checking for a new job. Default is 60 sec.  Range is 1..3600 sec
JOB_QUEUE_KEEP_CONNECTIONS Controls whether an SNP process closes any remote database connections. Default is False.  Range is True/False.

Job Owner

The person who submits a job to the job queue is considered the job’s owner. The job’s owner is the only one who can alter the job, force the job to run, or remove the job from the queue.  If you need to look up any information regarding a submitted job, DBA_JOBS and USER_JOBS contain a lot of information such as the job number, user, status, etc.

Dependencies

 job$            
·         dba_jobs      
·         all_jobs      
·         user_jobs
·         dba_jobs_running      
·         all_jobs_running      
·         user_jobs_running

Job Intervals

·         Execute daily          'SYSDATE + 1'
·         Execute once per week  'SYSDATE + 7'
·         Execute hourly         'SYSDATE + 1/24'
·         Execute every 10 min.  'SYSDATE + 10/1440'
·         Execute every 30 sec.  'SYSDATE + 30/86400'
·         Do not re-execute      NULL

Changing Scheduled Jobs

The DBMS_JOB.CHANGE procedure alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.

Syntax:

DBMS_JOB.CHANGE (
 job       IN  BINARY_INTEGER,
 what      IN  VARCHAR2,
 next_date IN  DATE,
 interval  IN  VARCHAR2,
 instance  IN  BINARY_INTEGER DEFAULT NULL,
 force     IN  BOOLEAN DEFAULT FALSE);
job- Number of the job being run.
What – PL/SQL procedure to run.
next_date – Date of the next refresh.
interval – Date function evaluated immediately before the job starts running.
instance – When a job is submitted, specifies which instance can run the job.
This defaults to NULL, which indicates that instance affinity is not changed.
force – If this is FALSE, then the specified instance must be running.
Otherwise, the routine raises an exception.
If this is TRUE, then any positive integer is acceptable as the job instance.

SQL Code:
EXECUTE DBMS_JOB.CHANGE(1, null, sysdate+3, null);

Change the WHAT

The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.

Syntax:

 DBMS_JOB.CHANGE (
 job    IN  BINARY_INTEGER,
 what   IN  VARCHAR2);

SQL Code:

execute DBMS_JOB.WHAT(3,Begin SEND_MESSAGE(); End;');

Changing the Next Date

The DBMS_JOB.NEXT_DATE procedure alters the next execution time for a specified job.

Syntax:

DBMS_JOB.NEXT_DATE (JOB IN BINARY_INTEGER,
 NEXT_DATE  IN DATE);

SQL Code:

execute DBMS_JOB.NEXT_DATE(4,SYSDATE+3);

Changing the Interval

The DBMS_JOB.INTERVAL procedure alters the interval between executions for a specified job. 

Syntax:
DBMS_JOB.INTERVAL (job         IN  BINARY_INTEGER,
                   interval  IN  VARCHAR2); 

Assign job to RAC instance

Assign a specific RAC instance to execute a job:

Syntax:
dbms_job.instance(
job      IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force    IN BOOLEAN DEFAULT FALSE); 

SELECT instance_number FROM gv$instance;

SQL Code:
EXECUTE  DBMS_JOB.INSTANCE(job=>123, instance=>1);
 
SQL Code:
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE, NEXT_SEC,INTERVAL
FROM DBA_JOBS;

ISUBMIT

Submit a job with a user specified job number

Syntax:
dbms_job.isubmit (
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2 DEFAULT 'NULL',
no_parse  IN BOOLEAN DEFAULT FALSE);

N.B: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)

exec dbms_job.isubmit(4242, 'MYPROC', SYSDATE);
 
IS_JOBQ
dbms_job.is_jobq RETURN BOOLEAN;

SQL Code:
set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_job.is_jobq THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

BROKEN Jobs

How a job becomes ‘broken’

  • Oracle has failed to successfully execute the job after 16 attempts.
    or
  • You have marked the job as broken, using the procedure DBMS_JOB.BROKEN

N.B:  Once a job has been marked as broken, Oracle will not attempt to execute the job until it is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN.
Syntax:
dbms_job.broken (
job       IN BINARY_INTEGER,
broken    IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE);

Run a job now

To force immediate job execution:

EXEC dbms_job.run(job_no);

Mark a job as not broken

If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset the job use following command

EXEC dbms_job.broken(jobno, FALSE);

Monitor User jobs

See created jobs:

SQL Code:
SELECT  job, next_date,
        next_sec,
        failures,
        broken,
        SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs; 

Jobs that are currently running:

SQL Code:
select
job                               j,
to_char ( last_date, 'hh24:mi:ss' ) last,
to_char ( this_date, 'hh24:mi:ss' ) this,
to_char ( next_date, 'hh24:mi:ss' ) next,
broken                            b,
failures                          f,
interval,
what
from user_jobs;

Remove a submitted job

Syntax: 
dbms_job.remove(job IN BINARY_INTEGER);
EXECUTE DBMS_JOB.REMOVE(jobno);

Example of DBMS_JOBS

--Job For Email 
declare
   jobno integer;
begin
   dbms_job.submit(
      job => jobno, 
           what => 'begin PROC_SEND_EMAIL_AT_11_45(); end;',
         --       provide resolution to the second
         --                midnight        hour of   minute of         units per day
         --             of current day    day to run         hr to run         ( 1 second )
      next_date => trunc(sysdate) + ((23 * (60*60) + ( 60*45)) * ( 1/(60*60*24))),
      interval =>  'trunc(sysdate + 1) + ((23 * (60*60) + ( 60*50)) * ( 1/(60*60*24)))'
   );
   commit;
end;
/

What Sessions are Running the Jobs

set linesize 250
col sid            for 9999     head 'Session|ID'
col spid                        head 'O/S|Process|ID'
col serial#        for 9999999  head 'Session|Serial#'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
s.spid,
s.serial#,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j,
     (select p.spid, s.sid, s.serial#
          from v$process p, v$session s
         where p.addr  = s.paddr ) s
 where j.sid = s.sid;

Viewing scheduled dbms_jobs

set linesize 250
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60

select j.log_user,
     j.job,
     j.broken,
     j.failures,
     j.last_date||':'||j.last_sec last_date,
     j.this_date||':'||j.this_sec this_date,
     j.next_date||':'||j.next_sec next_date,
     j.next_date - j.last_date interval,
     j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj) j;

What Jobs are Actually Running

set linesize 250
col sid            for 9999     head 'Session|ID'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j;
Categories: DBMS_JOB Tags:

Oracle UTL_SMTP And UTIL_MAIL Service

December 10, 2009 6 comments

The UTL_MAIL or UTL_SMPT package is introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP.  In order to use Oracle UTL_MAIL package we have to set a new init.ora parameter or spinit.ora if database running in spfile “SMTP_OUT_SERVER” set to outgoing mail server. Make sure that there is a getaway for the outgoing mail.

Step 1: Send Mail in Linux Server

[root@vasdbsrv mail]# rpm -qa |grep sendmail
sendmail-8.13.1-3.RHEL4.5
sendmail-cf-8.13.1-3.RHEL4.5

To verify the mail service running in the local host

[oracle@vasdbsrv ~]$ telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
220 vasdbsrv.ring.com.bd ESMTP Sendmail 8.13.1/8.13.1; Thu, 10 Dec 2009 14:54:51 +0600

If sendmail not installed then install it by using following command.

$ rpm –ivh sendmail

Step 2: Make sure the send mail service run on kick start.

[root@vasdbsrv mail]# chkconfig –list |grep sendmail

sendmail        0:off   1:off   2:on    3:on    4:on    5:on    6:off

Add service in checkcofig:

$ chkconfig  --add sendmail
$ chkconfig  --level 345 sendmail on

Step 3: Configure the /etc/mail/sendmail.cf

If you are running mail server in private IP

[oracle@vasdbsrv ~]$ vi /etc/mail/sendmail.cf
# "Smart" relay host (may be null)
DSmail.dnsgroup.net

Step 4: Configure /etc/resolv.conf

[root@vasdbsrv mail]# vi /etc/resolv.conf
search ring.com.bd
nameserver 203.188.191.5

Follow the simple steps to send an email using UTL_MAIL package

Step 5: Install UTL_MAIL or UTL_SMTP package

To install the UTL_MAIL and UTL_SMTP package, run the below files as user “SYS”

Source:

$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 6: Grant permissions

Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package.

Run the beow command as user “SYS”

 SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
 -or-
 SQL> GRANT EXECUTE ON utl_smtp TO <USER NAME>;

Step 7: Set SMTP_OUT_SERVER parameter

 SQL> ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=both;

Step 8: Create procedure to send email

 create or replace PROCEDURE send_mail_smtp
 (   sender     IN VARCHAR2,
 recipient  IN VARCHAR2,
 subject    IN VARCHAR2,
 message    IN LONG
 )
IS
   mailhost     VARCHAR2(30) := 'localhost';  -- -- host mail address
   mail_conn    utl_smtp.connection ;
   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
   mesg long;
BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
          'From:  <'||sender||'>' || crlf ||
          'Subject: '||subject || crlf ||
          'To: '||recipient || crlf ||
          '' || crlf || message;
   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, sender);
   utl_smtp.rcpt(mail_conn, recipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn);

   EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
       dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
       dbms_output.put_line(' Temporary e-mail issue - try again');
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
       dbms_output.put_line(' Permanent Error Encountered.'); 
END;

To Run the SEND_MAIL_SMTP run the following code.

BEGIN
SEND_MAIL_SMTP(
'tamimdba@yahoo.com',           --Sender
'tamimdba@gmail.com',           --Recipient
'Test Mail',                    --Subject
'Send From Oracle10g Database'  --Message
);
END;
PL/SQL procedure successfully completed.

GATHER Schema, Table, Index STATS

December 4, 2009 Leave a comment

PL/SQL Packages DBMS_STATS Gather, View, Modify or Delete optimizer statistics for database objects. From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers.

Oracle Cost Based Optimizer (CBO) requires statistics about data storage and distribution to generate accurate execution plans for queries. These statistics are generated using the DBMS_STATS package. Also, while using the CBO, histograms are used to store detailed information about data distributions which are non-uniform. This information helps the optimizer better estimate the selectivity of predicates which will result in more efficient execution plans. It is useful to create histograms when the application uses queries having:

Procedure Name Description
GATHER_TABLE_STATS Gathers statistics for a table and its indexes
GATHER_INDEX_STATS Gathers statistics for an index
GATHER_SCHEMA_STATS Gathers statistics for all objects in a schema
GATHER_DATABASE_STATS Gather statistics for all objects in the database

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(

        ownname,
        estimate_percent, 
        block_sample , 
        method_opt,
        degree,
        granularity,
        cascade,stattab, 
        statid,options,
        statown ,
        no_invalidate, 
        gather_temp,
        gather_fixed); 
 
 
Code examples:
EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent  =>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent => 25);

To delete the schema stat information of Scott schema run following query

EXEC DBMS_STATS.delete_schema_stats('SCOTT');
 

To get Table Stats run the following query.

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
select table_name,column_name,num_distinct,last_analyzed,num_buckets
from user_tab_cols
where table_name like ('EMP');
 
[N.B: you can also take information from user_tables data dictionary table]

To get Index Stats run the following query

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'PK_EMP');
select table_name,index_name,num_rows,last_analyzed
from user_indexes
where table_name like ('EMP');

Oracle’s PL/SQL Lock Timer

December 1, 2009 Leave a comment

The amount of time a user or application has slept through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures.
When investigating Oracle Wait-Events we often look at those events where applications consume precious system resources or are in direct contention with other users or applications. These resource waits revolve around excessive CPU, memory, or disk usage. Events that spin for Oracle internal structures such as latches or enquires can also be of concern. As their name suggests, Wait-Events DO ‘wait’ on some event to complete before a user or application can continue with its work. Contention for system resources will overwhelm Oracle’s ability to immediately process the SQL and ultimately cause applications to accumulate time in resource specific Wait-Events.

On the flip-side of resource waits there are what are called idle events. These events do not wait for any specific resource but record the time Oracle is waiting for a work request from the application. Many practitioners of Oracle performance will not even look at these idle events because they do not consume resources and are not limiting what Oracle can do at any given point in time. Some will even go so far as to state that the idle events have no meaningful information. This is not necessarily true. Take for instance the idle event ‘SQL*Net message from client’. This idle event is not inhibiting the database server from performing work but, as many agree, is an indication of poor response from client to database server. While idle events are not the result of direct resource contention, they are an accurate measure of accumulated delays in the application imposed by Oracle.

The ‘idle’ event ‘PL/SQL lock timer’ is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.

While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user’s perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event. How to look at the PL/SQL lock timer event To initiate a sleep for the current session for five seconds.

Oracle has four separate ways to induce a sleep into PL/SQL.

Source : {ORACLE_HOME}/rdbms/admin/dbmslock.sql
 

Name             Description

nl_mode          NuLL Value 1

ss_mode          Sub Shared: used on an aggregate object to indicate that share locks are being acquired on subparts of the object value 2

sx_mode          Sub eXclusive: used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object value 3

s_mode            Shared: indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks value 4

ssx_mod          Shared SubeXclusive value 5

x_mode            eXclusive value 6

Grant access DBMS_LOCK to all users

GRANT EXECUTE ON DBMS_LOCK TO PUBLIC
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

SQL>  execute sys.dbms_lock.sleep(5);

To take a look at current sessions that are using the SLEEP command. Notice that the column P1 in V$SESSION does not represent the amount of time the session has slept but in fact represents the duration this session will sleep. Also note that this column is in centiseconds and as such the five seconds issued for a sleep has been translated in to 500 centiseconds.

SQL>  select osuser,event,p1
       from v$session
      where event = 'PL/SQL lock timer'

OSUSER       EVENT             P1
------------ ----------------- ---
oracle       PL/SQL lock timer 500

If you wanted to take a look at the total amount of time that has accumulated for this type of wait event, you can look at the V$SYSTEM_EVENT view. Also note that in this particular view the time waited is also in centiseconds but as you can see the time actually waited may be more that expected. On our test system this equated to about 1.024 seconds to 1 second requested.

SQL>  select time_waited
      from v$system_event
      where event = 'PL/SQL lock timer';

SQL>  select package_name, argument_name,
             data_type, data_length,
       data_precision, pls_type
from  all_arguments
where object_name = 'SLEEP'
order by 1;

To find the Dependencies

SQL>  SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_LOCK'
UNION
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOCK';

Oracle 11g’s data dictionary defines the DBA_LOCK view using the following SQL

SQL> select
sid session_id,
decode(type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
type) lock_type,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
ctime last_convert,
decode(block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(block)) blocking_others
from v$lock

Data Dictionary Views of LOCK

DBA_LOCK

Source: $ORACLE_HOME/rdbms/admin/catblock.sql

Related Parameters:

_enqueue_locks
dml_locks                         Specifies the maximum number of DML locks–one for each table modified in a transaction.
enqueue_resources      Sets the number of resources that can be concurrently locked by the lock manager.

Related Commands:

ALTER TABLE DISABLE TABLE LOCK

Related Views:

V$_LOCK

V$ACCESS       Displays objects in the database that are currently locked and the sessions that are accessing them.

V$ENQUEUE_LOCK Displays all locks owned by enqueue state objects.

V$ENQUEUE_STAT Contains detailed enqueue statistics for each enqueue.

V$GLOBAL_BLOCKED_LOCKS Displays global blocked locks.

V$LOCK        Lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.

V$LOCKED_OBJECT    Lists all locks acquired by every transaction on the system.

V$RESOURCE         Contains resource name and address information

V$RESOURCE_LIMIT   Displays information about current and maximum global resource utilization for some system resources.

V$TRANSACTION_ENQUEUE   Displays locks owned by transaction state objects.

DBA_BLOCKERS       Shows all sessions holding a lock which is required by another process.

DBA_DDL_LOCKS      Lists all DDL locks held in the database and all outstanding requests for a DDL lock.

DBA_DML_LOCKS      Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

DBA_KGLLOCK        Lists all the locks and pins held on KGL objects (objects in the Kernel Generic Library cache).

DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

DBA_WAITERS        Shows all sessions waiting for locks and the session that holds the lock.

DBMS_LOCK_ALLOCATED     Describes user-allocated locks.

PL/SQL Packages DBMS_UTILITY

November 14, 2009 Leave a comment

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;
Categories: DBMS_UTILITY Tags: