Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query tuning

Posted on 2009-05-19
6
Medium Priority
?
562 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

885 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