Getting bind variables from running SQL in em12c and find the UBE running it

Getting SQL bind variables can be tough.

Here is how I do it in EM12C.

goto performance –> SQL Monitoring to get to the page below

This is all of the tough SQL running at the moment.

Find the statement that you want and click the id (3rd column), you’ll get this screen

Now, click the circled button above and you’ll get this screen:

Choose show SQL Binds

Hit the save button and things get even better.  em12c writes you a script with all of the variables defined, you can then run it in SQLPlus if you need to.  Wow, that is awesome.

-- ============================================================================================== --
-- This file is generated by EM Database Express.			                          --
-- The SQL bind variables, if any, will be declared below.                                        --
-- ============================================================================================== --

declare
  KEY1    NCHAR(32)      := 'IMP';
  KEY2    NCHAR(32)      := 'ZZZ';
  KEY3    NCHAR(32)      := 'N';
  KEY4    NCHAR(32)      := '00133';
  KEY5    NCHAR(32)      := '00141';
  KEY6    NCHAR(32)      := '00146';
  KEY7    NCHAR(32)      := '00147';
  KEY8    NCHAR(32)      := '00178';
  KEY9    NCHAR(32)      := '00222';
  KEY10   NCHAR(32)      := '00244';
  KEY11   NCHAR(32)      := '00246';
  KEY12   NCHAR(32)      := '00250';
  KEY13   NCHAR(32)      := '00252';
  KEY14   NCHAR(32)      := '00253';
  KEY15   NCHAR(32)      := '00254';
  KEY16   NCHAR(32)      := '00255';
  KEY17   NCHAR(32)      := '00256';
  KEY18   NCHAR(32)      := '00257';
  KEY19   NCHAR(32)      := '00268';
  KEY20   NCHAR(32)      := '00275';
  KEY21   NCHAR(32)      := '00298';
  KEY22   NCHAR(32)      := '00314';
  KEY23   NCHAR(32)      := '00318';
  KEY24   NCHAR(32)      := '00352';
  KEY25   NCHAR(32)      := '00364';
  KEY26   NCHAR(32)      := '00601';
  KEY27   NCHAR(32)      := '00710';
  KEY28   NCHAR(32)      := '00756';
  KEY29   NCHAR(32)      := '00758';
  KEY30   NCHAR(32)      := '00840';
  KEY31   NCHAR(32)      := '00950';
  KEY32   NCHAR(32)      := '00963';
  KEY33   NCHAR(32)      := '00987';
  KEY34   NCHAR(32)      := '00988';
  KEY35   NCHAR(32)      := '00995';
  KEY36   NVARCHAR2(32)  := '115106';
  KEY37   NVARCHAR2(32)  := '115104';
  KEY38   NCHAR(32)      := NULL;
  KEY39   NCHAR(32)      := 'ZZZZZZZZZZZZ';
  KEY40   NCHAR(32)      := NULL;
  KEY41   NCHAR(32)      := 'ZZZZZZZZZZZZ';
begin
--  SELECT T0.GMCO, T0.GMAID, T0.GMMCU, T0.GMOBJ, T0.GMSUB, T0.GMANS, T0.GMDL01, T0.GMLDA, T0.GMBPC, T0.GMPEC, T0.GMBILL, T0.GMCRCD, T0.GMUM, T0.GMR001, T0.GMR002, T0.GMR003, T0.GMR004, T0.GMR005, T0.GMR006, T0.GMR007, T0.GMR008, T0.GMR009, T0.GMR010, T0.GMR011, T0.GMR012, T0.GMR013, T0.GMR014, T0.GMR015, T0.GMR016, T0.GMR017, T0.GMR018, T0.GMR019, T0.GMR020, T0.GMR021, T0.GMR022, T0.GMR023, T0.GMOBJA, T0.GMSUBA, T0.GMWCMP, T0.GMCCT, T0.GMERC, T0.GMHTC, T0.GMQLDA, T0.GMCCC, T0.GMFMOD, T0.GMUSER, T0.GMPID, T0.GMJOBN, T0.GMUPMJ, T0.GMUPMT, T0.GMCEC1, T0.GMCEC2, T0.GMCEC3, T0.GMCEC4, T0.GMIEC, T0.GMFPEC, T0.GMSTPC, T0.GMTXGL, T0.GMTOBJ, T0.GMTSUB, T0.GMPRGF, T0.GMTXA1, T0.GMR024, T0.GMR025, T0.GMR026, T0.GMR027, T0.GMR028, T0.GMR029, T0.GMR030, T0.GMR031, T0.GMR032, T0.GMR033, T0.GMR034, T0.GMR035, T0.GMR036, T0.GMR037, T0.GMR038, T0.GMR039, T0.GMR040, T0.GMR041, T0.GMR042, T0.GMR043, T1.MCMCU, T1.MCSTYL, T1.MCDC, T1.MCLDM, T1.MCCO, T1.MCAN8, T1.MCAN8O, T1.MCCNTY, T1.MCADDS, T1.MCFMOD, T1.MCDL01, T1.MCDL02, T1.MCDL03, T1.MCDL04, T1.MCRP01, T1.MCRP02, T1.MCRP03, T1.MCRP04, T1.MCRP05, T1.MCRP06, T1.MCRP07, T1.MCRP08, T1.MCRP09, T1.MCRP10, T1.MCRP11, T1.MCRP12, T1.MCRP13, T1.MCRP14, T1.MCRP15, T1.MCRP16, T1.MCRP17, T1.MCRP18, T1.MCRP19, T1.MCRP20, T1.MCRP21, T1.MCRP22, T1.MCRP23, T1.MCRP24, T1.MCRP25, T1.MCRP26, T1.MCRP27, T1.MCRP28, T1.MCRP29, T1.MCRP30, T1.MCTA, T1.MCTXJS, T1.MCTXA1, T1.MCEXR1, T1.MCTC01, T1.MCTC02, T1.MCTC03, T1.MCTC04, T1.MCTC05, T1.MCTC06, T1.MCTC07, T1.MCTC08, T1.MCTC09, T1.MCTC10, T1.MCND01, T1.MCND02, T1.MCND03, T1.MCND04, T1.MCND05, T1.MCND06, T1.MCND07, T1.MCND08, T1.MCND09, T1.MCND10, T1.MCCC01, T1.MCCC02, T1.MCCC03, T1.MCCC04, T1.MCCC05, T1.MCCC06, T1.MCCC07, T1.MCCC08, T1.MCCC09, T1.MCCC10, T1.MCPECC, T1.MCALS, T1.MCISS, T1.MCGLBA, T1.MCALCL, T1.MCLMTH, T1.MCLF, T1.MCOBJ1, T1.MCOBJ2, T1.MCOBJ3, T1.MCSUB1, T1.MCTOU, T1.MCSBLI, T1.MCANPA, T1.MCCT, T1.MCCERT, T1.MCMCUS, T1.MCBTYP, T1.MCPC, T1.MCPCA, T1.MCPCC, T1.MCINTA, T1.MCINTL, T1.MCD1J, T1.MCD2J, T1.MCD3J, T1.MCD4J, T1.MCD5J, T1.MCD6J, T1.MCFPDJ, T1.MCCAC, T1.MCPAC, T1.MCEEO, T1.MCERC, T1.MCUSER, T1.MCPID, T1.MCUPMJ, T1.MCJOBN, T1.MCUPMT, T1.MCBPTP, T1.MCAPSB, T1.MCTSBU FROM PRODDTA.F0901 T0,PRODDTA.F0006 T1 WHERE  (  ( T0.GMR002 = :KEY1 AND T1.MCRP20 <> :KEY2 AND T0.GMPEC <> :KEY3 AND T0.GMCO IN  ( :KEY4,:KEY5,:KEY6,:KEY7,:KEY8,:KEY9,:KEY10,:KEY11,:KEY12,:KEY13,:KEY14,:KEY15,:KEY16,:KEY17,:KEY18,:KEY19,:KEY20,:KEY21,:KEY22,:KEY23,:KEY24,:KEY25,:KEY26,:KEY27,:KEY28,:KEY29,:KEY30,:KEY31,:KEY32,:KEY33,:KEY34,:KEY35 )  )  AND  ( T0.GMUPMJ <= :KEY36 AND T0.GMUPMJ >= :KEY37 )  )  AND  ( T0.GMMCU BETWEEN :KEY38 AND :KEY39 )  AND  ( T1.MCMCU BETWEEN :KEY40 AND :KEY41 )  AND  ( T0.GMMCU=T1.MCMCU )  ORDER BY T0.GMAID ASC
end;

To find the culprit, you need to go back to this screen

Click the username field circled above

So with the above we can tell that it’s a batch job and that it’s PID is 1837 and it’s a runbatch process

So we can see in E1 that this is R55BLAHBLAH, nice one.  We know the report that is running the crazy statement.