Solved

Query tuning

Posted on 2009-05-19
6
541 Views
Last Modified: 2013-12-19
Hello there,

I got below query. It took long time to retrieve the data. Is there any way to tune it? Thank you for your input.

Regards,
Frank
SELECT LEG.LEGID,

       AUX.RESERVATION_ID RESERVATIONID,

       LEG.REQUEST_ID,

       LEG.LEG_ORDER_NBR,

       LEG.FLOWN_STATUS AS LEG_FLOWN_STAT_ID,

       REQ.FLIGHT_TYPE_ID AS FLIGHT_TYPE_ID,

       REQ.PROGRAMID PROGRAMID,

       LEG.ETD_TM,

	   trunc(LEG.ETD_TM) ETD_TM_TRUC,

       LEG.ETD_SLIDE_TM,

       LEG.ETA_SLIDE_TM,

	   LEG.FLIGHT_RULE_ID,

       LEG.ATD_TM ATD_TM,

	   trunc(LEG.ATD_TM) ATD_TM_TRUC,

       LEG.ATA_TM ATA_TM,

	   trunc(LEG.ATA_TM) ATA_TM_TRUC,

       LEG.ETA_TM,

	   trunc(LEG.ETA_TM) ETA_TM_TRUC,

       LEG.DEP_LATE_EARLY_OPS_RQRD_FLAG,

       LEG.ARR_LATE_EARLY_OPS_RQRD_FLAG,

       AUX.ACCOUNT_ID AUS_ACCOUNTID,

       AUX.DEP_AIRPORT_ID DEP_AIRPORT_ID,

       AUX.ARR_AIRPORT_ID ARR_AIRPORT_ID,

       LEG.requested_sell_off_id,

       AUX.DEP_FBO_ID DEP_FBO_ID,

       AUX.ARR_FBO_ID ARR_FBO_ID,

       LEG.FUEL_START_QTY,

       LEG.FUEL_END_QTY,

       LEG.REQ_EST_FLIGHT_DISTANCE_KM FLT_MILES_NBR,

       AUX.LEG_ASSIGNMENT_REF_ID,

       LEG.NEXTLEGID,

	   REQ.PROGRAMID REQ_PROGRAM_ID,

       REQ.NEXTREQUESTID,

       UPPER(REQ.TYPENAME) REQ_ACR_TYPENAME,

       UPPER(REQ.GUARANTEED_TYPENAME) GUA_ACR_TYPENAME,

       AUX.TAIL_ID,

       LEG.REQ_EST_FUEL_STOPS,

       ASN.assignment_id,

       LEG.NUMBER_OF_LANDINGS_QTY,

       LEG.AIRFRAME_CYCLES_QTY,

       RES.BOOKED_TS,

       RES.BOOKING_AGENT_ID,

       REQ.REQUEST_STATUS_CD,

       RQS.PRIMARY_REASON_ID SELL_OFF_PRIMARY_REASON_ID,

       RQS.STATUS_ID SELL_OFF_STATUS_ID,

       LEG.AIRFRAME_HRS_QTY,

       LEG.INTIME_DAT,

       LEG.OUTTIME_DAT,

	   dep.latitude_qty dep_latitude_qty,

       dep.longitude_qty dep_longitude_qty,

       arr.latitude_qty arr_latitude_qty,

       arr.longitude_qty arr_longitude_qty,	 

	   /* Get Nautical_Distance from IJET.Great_Circle function */

	   (3437.75 * 2 * asin(sqrt(power(sin(((DEP.LATITUDE_QTY/3600*3.14159/180)-(ARR.LATITUDE_QTY/3600*3.14159/180))/2),2) + cos(DEP.LATITUDE_QTY/3600*3.14159/180)*cos(ARR.LATITUDE_QTY/3600*3.14159/180)*power(sin(((DEP.LONGITUDE_QTY/3600*3.14159/180)-(ARR.LONGITUDE_QTY/3600*3.14159/180))/2),2) ))) nautical_mile,

       /* Get Statute_Distance from IJET.Great_Circle function */

	   (1.15 * 3437.75 * 2 * asin(sqrt(power(sin(((DEP.LATITUDE_QTY/3600*3.14159/180)-(ARR.LATITUDE_QTY/3600*3.14159/180))/2),2) +cos(DEP.LATITUDE_QTY/3600*3.14159/180)*cos(ARR.LATITUDE_QTY/3600*3.14159/180)*power(sin(((DEP.LONGITUDE_QTY/3600*3.14159/180)-(ARR.LONGITUDE_QTY/3600*3.14159/180))/2),2)))) statute_mile, 

      (CASE

         when REQ.REQUEST_STATUS_CD = 'C' THEN

          (SELECT MAX(z.SYS_LAST_CHANGED_TS)

             FROM ijet.audit_trail_item z

            where z.REQUEST_ID = leg.request_id AND

                  z.audit_trail_type_id = 105 AND

                  z.change_desc like '%to Cancelled')

       END) LEG_CANCELLATION_TM,

	   (CASE

         WHEN REQ.FLIGHT_TYPE_ID in (1, 5, 6, 7, 8) then

          'Y'

         ELSE

          'N'

       END) REVENUE_FLIGHT,

	   (select      ejco.EJ_COMPANY_ID

        from        ijet.ej_company ejco, ijet.tail_usage_history tuh

        where       ejco.ej_company_id=tuh.operating_company_id

        and         tuh.aircraft_tail_id= AUX.TAIL_ID

        and tuh.from_dat <= NVL(LEG.ATD_TM, LEG.ETD_TM)

        and NVL(LEG.ATD_TM, LEG.ETD_TM) < nvl(tuh.to_dat,to_date('20991231','YYYYMMDD'))) OPERATING_COMPANY_ID, -- Based on ADMIN_FLIGHT_DATA_VW 

	   CAST((FROM_TZ(CAST(IJET.LEG.ETD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ETD_TM,

	   CAST((FROM_TZ(CAST(IJET.LEG.ETA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ETA_TM,

       CAST((FROM_TZ(CAST(IJET.LEG.ATD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ATD_TM,

       CAST((FROM_TZ(CAST(IJET.LEG.ATA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ATA_TM,

	   trunc(CAST((FROM_TZ(CAST(IJET.LEG.ETD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ETD_TRUC,

       trunc(CAST((FROM_TZ(CAST(IJET.LEG.ETA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ETA_TRUC,

       trunc(CAST((FROM_TZ(CAST(IJET.LEG.ATD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ATD_TRUC,

	   trunc(CAST((FROM_TZ(CAST(IJET.LEG.ATA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ATA_TRUC,

	   trunc((((LEG.ETA_TM - LEG.ETD_TM) * 24) - 0.2), 4) Estimated_Flight_Hrs_Qty,

	   trunc(((LEG.ETA_TM - LEG.ETD_TM) * 24), 4) Estimated_Block_Hrs_Qty,

	   trunc(((LEG.ATA_TM - LEG.ATD_TM) * 24), 4) Actual_Flight_Hrs_Qty,

	   trunc(((LEG.INTIME_DAT - LEG.OUTTIME_DAT) * 24), 4) Actual_Block_Hrs_Qty,

	   round(((LEG.ATA_TM - LEG.ATD_TM) * 1440), 0) Actual_Flight_Minutes_Qty,

	   round(((LEG.INTIME_DAT - LEG.OUTTIME_DAT) * 1440), 0) Actual_Block_Minutes_Qty,

	   round(((LEG.OUTTIME_DAT - decode(LEG.ETD_SLIDE_TM, NULL, LEG.ETD_TM, LEG.ETD_SLIDE_TM)) * 1440), 0) Departure_Delay_Minutes_Qty,

     round(((LEG.INTIME_DAT - decode(LEG.ETA_SLIDE_TM, NULL, LEG.ETA_TM, LEG.ETA_SLIDE_TM)) * 1440), 0) Arrival_Delay_Minutes_Qty 

  FROM ijet.airport            arr,

       ijet.airport            dep,

       ijet.requested_sell_off rqs,

       ijet.leg_assignment_ref ASN,

       IJET.RESERVATION        RES,

       IJET.REQUEST            REQ,

       IJET.AUX_LEG            AUX,

       IJET.LEG                LEG,

       (select t.legid as previous_legid,t. nextlegid,t. intime_dat previous_intime, t.outtime_dat from ijet.leg t where t.nextlegid  is not null) p

 WHERE DEP.airportid(+) = AUX.DEP_AIRPORT_ID AND

       ARR.airportid(+) = AUX.ARR_AIRPORT_ID AND

       RQS.requested_sell_off_id(+) = LEG.requested_sell_off_id AND

       ASN.leg_id(+) = leg.legid AND RES.RESERVATIONID = REQ.RESERVATIONID AND

       REQ.REQUEST_ID = LEG.REQUEST_ID AND AUX.LEG_ID(+) = LEG.LEGID AND 

     (LEG.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or RES.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss')

     or REQ.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or rqs.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss')

     or ASN.SYS_LAST_CHANGED_TS >=to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or AUX.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss'))

     AND LEG.LEGID = p.nextlegid (+)

Open in new window

0
Comment
Question by:FlyingJet
6 Comments
 
LVL 11

Expert Comment

by:Andytw
ID: 24427067
Can you provide some more information as we don't know anything about your data-structures and what data/and volume of data they contain.  Can you post an explain plan, or SQL*Plus autotrace?

In the mean-time some observations from looking at your query:
1).  How much data did you get back from this query? looking at the date predicates, it looks like you're returning all data since 1960!
2). You have an inline view ((select t.legid as previous_legid,t. nextlegid, ...) alias P.  Non of the columns from p are used in the query, and it is outer joined to LEG, so you can remove it.
0
 
LVL 18

Expert Comment

by:sventhan
ID: 24437873
The problem here is with the outer joins which requires the full table scans.  
Just for debugging remove those outer joins and see if you get any performace gains.  Instead of joining all at once try to rewrite the query using scalar sub queries.
Like the first post, you have to provide more details to get some answers.
0
 
LVL 1

Accepted Solution

by:
rbodepudi earned 500 total points
ID: 24441332
There are several things you can change which should definately improve the performance:

1. CASE is a generic statement, and applies to all databases. DECODE is native to oracle, and performs quicker than CASE
2. One of your case statements includes a sub-select and your are trying to find maximum value as below:
(CASE  when REQ.REQUEST_STATUS_CD = 'C' THEN
          (SELECT MAX(z.SYS_LAST_CHANGED_TS) FROM ijet.audit_trail_item z
        where z.REQUEST_ID = leg.request_id AND z.audit_trail_type_id = 105 AND
                  z.change_desc like '%to Cancelled')  END) LEG_CANCELLATION_TM,
This way you are asking Oracle to go against this table and find the max() for each record in the result set. This costs too many resources and time. Instead you can seperate this statment (max() only) and join it to the main query and then use DECODE in main query. You join should look like the one in Code Snippet. If you want all rows to be retrieved, Remember I only replaced CASE with DECODE for one statement.

3. You have to many OR statements in the where clause. If you really need all those that's OKAY... but in an idle situation, instead of checking this, I would restrict the results even before joining. So intead of mentioning 'ljet.requested_sell_off rqs', if you can identify the rows you needed from that table, then use like this
(Select column1, column2 from FROM ljet.requested_sell_off rqs where rqs.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss').

This helps you to limit the results upfront.
SELECT LEG.LEGID,

       AUX.RESERVATION_ID RESERVATIONID,

       LEG.REQUEST_ID,

       LEG.LEG_ORDER_NBR,

       LEG.FLOWN_STATUS AS LEG_FLOWN_STAT_ID,

       REQ.FLIGHT_TYPE_ID AS FLIGHT_TYPE_ID,

       REQ.PROGRAMID PROGRAMID,

       LEG.ETD_TM,

	   trunc(LEG.ETD_TM) ETD_TM_TRUC,

       LEG.ETD_SLIDE_TM,

       LEG.ETA_SLIDE_TM,

	   LEG.FLIGHT_RULE_ID,

       LEG.ATD_TM ATD_TM,

	   trunc(LEG.ATD_TM) ATD_TM_TRUC,

       LEG.ATA_TM ATA_TM,

	   trunc(LEG.ATA_TM) ATA_TM_TRUC,

       LEG.ETA_TM,

	   trunc(LEG.ETA_TM) ETA_TM_TRUC,

       LEG.DEP_LATE_EARLY_OPS_RQRD_FLAG,

       LEG.ARR_LATE_EARLY_OPS_RQRD_FLAG,

       AUX.ACCOUNT_ID AUS_ACCOUNTID,

       AUX.DEP_AIRPORT_ID DEP_AIRPORT_ID,

       AUX.ARR_AIRPORT_ID ARR_AIRPORT_ID,

       LEG.requested_sell_off_id,

       AUX.DEP_FBO_ID DEP_FBO_ID,

       AUX.ARR_FBO_ID ARR_FBO_ID,

       LEG.FUEL_START_QTY,

       LEG.FUEL_END_QTY,

       LEG.REQ_EST_FLIGHT_DISTANCE_KM FLT_MILES_NBR,

       AUX.LEG_ASSIGNMENT_REF_ID,

       LEG.NEXTLEGID,

	   REQ.PROGRAMID REQ_PROGRAM_ID,

       REQ.NEXTREQUESTID,

       UPPER(REQ.TYPENAME) REQ_ACR_TYPENAME,

       UPPER(REQ.GUARANTEED_TYPENAME) GUA_ACR_TYPENAME,

       AUX.TAIL_ID,

       LEG.REQ_EST_FUEL_STOPS,

       ASN.assignment_id,

       LEG.NUMBER_OF_LANDINGS_QTY,

       LEG.AIRFRAME_CYCLES_QTY,

       RES.BOOKED_TS,

       RES.BOOKING_AGENT_ID,

       REQ.REQUEST_STATUS_CD,

       RQS.PRIMARY_REASON_ID SELL_OFF_PRIMARY_REASON_ID,

       RQS.STATUS_ID SELL_OFF_STATUS_ID,

       LEG.AIRFRAME_HRS_QTY,

       LEG.INTIME_DAT,

       LEG.OUTTIME_DAT,

	   dep.latitude_qty dep_latitude_qty,

       dep.longitude_qty dep_longitude_qty,

       arr.latitude_qty arr_latitude_qty,

       arr.longitude_qty arr_longitude_qty,	 

	   /* Get Nautical_Distance from IJET.Great_Circle function */

	   (3437.75 * 2 * asin(sqrt(power(sin(((DEP.LATITUDE_QTY/3600*3.14159/180)-(ARR.LATITUDE_QTY/3600*3.14159/180))/2),2) + cos(DEP.LATITUDE_QTY/3600*3.14159/180)*cos(ARR.LATITUDE_QTY/3600*3.14159/180)*power(sin(((DEP.LONGITUDE_QTY/3600*3.14159/180)-(ARR.LONGITUDE_QTY/3600*3.14159/180))/2),2) ))) nautical_mile,

       /* Get Statute_Distance from IJET.Great_Circle function */

	   (1.15 * 3437.75 * 2 * asin(sqrt(power(sin(((DEP.LATITUDE_QTY/3600*3.14159/180)-(ARR.LATITUDE_QTY/3600*3.14159/180))/2),2) +cos(DEP.LATITUDE_QTY/3600*3.14159/180)*cos(ARR.LATITUDE_QTY/3600*3.14159/180)*power(sin(((DEP.LONGITUDE_QTY/3600*3.14159/180)-(ARR.LONGITUDE_QTY/3600*3.14159/180))/2),2)))) statute_mile, 

      DECODE(REQ.REQUEST_STATUS_CD, 'C', TEST.SYS_LAST_CHANGED_TS) LEG_CANCELLATION_TM,

	   (CASE WHEN REQ.FLIGHT_TYPE_ID in (1, 5, 6, 7, 8) then 'Y' ELSE 'N' END) REVENUE_FLIGHT,

	   (select      ejco.EJ_COMPANY_ID

        from        ijet.ej_company ejco, ijet.tail_usage_history tuh

        where       ejco.ej_company_id=tuh.operating_company_id

        and         tuh.aircraft_tail_id= AUX.TAIL_ID

        and tuh.from_dat <= NVL(LEG.ATD_TM, LEG.ETD_TM)

        and NVL(LEG.ATD_TM, LEG.ETD_TM) < nvl(tuh.to_dat,to_date('20991231','YYYYMMDD'))) OPERATING_COMPANY_ID, -- Based on ADMIN_FLIGHT_DATA_VW 

	   CAST((FROM_TZ(CAST(IJET.LEG.ETD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ETD_TM,

	   CAST((FROM_TZ(CAST(IJET.LEG.ETA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ETA_TM,

       CAST((FROM_TZ(CAST(IJET.LEG.ATD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ATD_TM,

       CAST((FROM_TZ(CAST(IJET.LEG.ATA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) KCMH_ATA_TM,

	   trunc(CAST((FROM_TZ(CAST(IJET.LEG.ETD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ETD_TRUC,

       trunc(CAST((FROM_TZ(CAST(IJET.LEG.ETA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ETA_TRUC,

       trunc(CAST((FROM_TZ(CAST(IJET.LEG.ATD_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ATD_TRUC,

	   trunc(CAST((FROM_TZ(CAST(IJET.LEG.ATA_TM AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE)) KCMH_ATA_TRUC,

	   trunc((((LEG.ETA_TM - LEG.ETD_TM) * 24) - 0.2), 4) Estimated_Flight_Hrs_Qty,

	   trunc(((LEG.ETA_TM - LEG.ETD_TM) * 24), 4) Estimated_Block_Hrs_Qty,

	   trunc(((LEG.ATA_TM - LEG.ATD_TM) * 24), 4) Actual_Flight_Hrs_Qty,

	   trunc(((LEG.INTIME_DAT - LEG.OUTTIME_DAT) * 24), 4) Actual_Block_Hrs_Qty,

	   round(((LEG.ATA_TM - LEG.ATD_TM) * 1440), 0) Actual_Flight_Minutes_Qty,

	   round(((LEG.INTIME_DAT - LEG.OUTTIME_DAT) * 1440), 0) Actual_Block_Minutes_Qty,

	   round(((LEG.OUTTIME_DAT - decode(LEG.ETD_SLIDE_TM, NULL, LEG.ETD_TM, LEG.ETD_SLIDE_TM)) * 1440), 0) Departure_Delay_Minutes_Qty,

     round(((LEG.INTIME_DAT - decode(LEG.ETA_SLIDE_TM, NULL, LEG.ETA_TM, LEG.ETA_SLIDE_TM)) * 1440), 0) Arrival_Delay_Minutes_Qty 

  FROM ijet.airport            arr,

       ijet.airport            dep,

       ijet.requested_sell_off rqs,

       ijet.leg_assignment_ref ASN,

       IJET.RESERVATION        RES,

       IJET.REQUEST            REQ,

       IJET.AUX_LEG            AUX,

       IJET.LEG                LEG,

       (select t.legid as previous_legid,t. nextlegid,t. intime_dat previous_intime, t.outtime_dat from ijet.leg t where t.nextlegid  is not null) p,

       (SELECT MAX(z.SYS_LAST_CHANGED_TS) SYS_LAST_CHANGED_TS, z.REQUEST_ID FROM ijet.audit_trail_item z 

              where z.audit_trail_type_id = 105 AND z.change_desc like '%to Cancelled') TEST

 WHERE DEP.airportid(+) = AUX.DEP_AIRPORT_ID AND

       ARR.airportid(+) = AUX.ARR_AIRPORT_ID AND

       RQS.requested_sell_off_id(+) = LEG.requested_sell_off_id AND

       ASN.leg_id(+) = leg.legid AND RES.RESERVATIONID = REQ.RESERVATIONID AND

       REQ.REQUEST_ID = LEG.REQUEST_ID AND AUX.LEG_ID(+) = LEG.LEGID AND 

     (LEG.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or RES.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss')

     or REQ.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or rqs.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss')

     or ASN.SYS_LAST_CHANGED_TS >=to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss') or AUX.SYS_LAST_CHANGED_TS >= to_date('1/1/1960', 'mm/dd/yyyy hh24:mi:ss'))

     AND LEG.LEGID = p.nextlegid (+)

     AND LEG.request_id = TEST.request_id

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:FlyingJet
ID: 24444168
Thank you, rbodepudi. Your suggestions did improve the performance. but I am going to split points within you and Andytw since he quickly response my question.

Thank you all for your input!

Regards,
Frank
0
 

Author Closing Comment

by:FlyingJet
ID: 31583170
400 points will go to rbodepudi. And the rest 100 for rbodepudi. Thanks.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24454168
FlyingJet: only split the points with me and rbodepudi if I helped with your problem.  If you still want to split, you will have to click on the "Request Attention" link and ask a moderator to help (as you've already accepted rbodepudi's answer).
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now