Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

the database in question is Oracle 10g Enterprise software

the tools available are Toad, SQL Developer, and other built in oracle tools

I have an application server within the network, alongside the database server within the network. I also have a system outside of the firewall.

With my current monitoring tools: SGA monitor within Toad

I can see queries coming from the application server to the database, but I can't see what is being sent to/from the system out of the firewall, which does communicate to the database.

Running a packet sniffer I can see the database communicating with the system outside of the firewall (dmz)

What command would allow me to see all of the queries being made, and how would this be run?

share|improve this question

2 Answers 2

Do you have access to oracle enterprise manager (OEM) ?

You should be able to examine sql statements running aginst your db there... eg:

This is oracle 11 docs, but 10g has a similar interface

share|improve this answer

I use the following sql to display all the sql in memory with "ITEM_F" in it. You can change the "ITEM_F" to whatever you want. Then I display the full sql with another sql called HASH_VALUE.sql

EXECUTIONS HASH_VALUE  GETS_PER_EXEC SEC_PER_EXEC SQL_TEXT
---------- ---------- -------------- ------------ ---------------------
         1 3605439262      507993.00   4.24581775 BEGIN    IF '%CAPA%'
         1  671043237      517057.00   26.5039965 BEGIN    IF '%DEV%' =
         1  336821122      527328.50   1422.86585 /* MV_REFRESH (INS) *
         1  484748009      539104.00    22.037663 BEGIN    IF '%MCO%' =
         1 3169765572      564994.50   1274.37927 INSERT /*+ BYPASS_REC
         1 2692239685      573682.50   172.683195 BEGIN DBMS_MVIEW.REFR
         1 2023740499      577038.00   3.74569625 INSERT INTO MDS.MATT_
         1 2724147626      655448.00   58.4461736 INSERT /*+ BYPASS_REC
         1 3939050362      730432.00   23.7762682 INSERT INTO MDS.MATT_
         1 3275016008      792977.50   142.068046 INSERT /*+ BYPASS_REC
         1   60973157      915308.50    1074.8388 BEGIN dbms_mview.refr
         1 1262896430     1044207.50   145.147301 BEGIN DBMS_MVIEW.REFR
         1  481615044     1185227.50   197.121223 INSERT /*+ BYPASS_REC
         1  832862631     1259484.50   1483.57486 /* MV_REFRESH (INS) *
         1  413596320     1327330.00   213.819306 BEGIN dbms_mview.refr
         1 1471539484     1340705.50   1091.36564 INSERT INTO MATT_AML_
         1  846352950     1929911.50   1243.53836 /* MV_REFRESH (INS) *
        18 3947679100      316931.68   3.47618447 SELECT  distinct  TOT
        18 1224535723     6558946.68    29.210453 SELECT  distinct  CHA
        20 1746064953    59020824.67   1370.51753 INSERT /*+ BYPASS_REC

EQADMRT > list
  1  select sql_id,address,parse_calls,buffer_gets,executions,hash_value,buffer_gets/(executions+1) gets_per_exec,
  2  elapsed_time/1000000/(executions+.000001) sec_per_exec,sql_text
  3  from gv$sqlarea
  4  where ( upper(sql_text) like upper('%&1%') )
  5  -- and buffer_gets > 0
  6* order by buffer_gets

EQADMRT > @hash_value 1471539484

HASH_VALUE=1471539484

INSERT INTO MATT_AML_STATUS_3 ( SELECT DISTINCT F.PJ_PK, F.SCI_I
TEM_SRC_ID, F.RS_SUPPLIER_ID,D.PC_MCO_ID,H.ENTRY_VALUE, G.ITEM_M
FR_PART_ID,I.PC_MCO_NO,AGILE_FLEX.TEXT FROM PCM_ANALYSIS_F F, OD
M.ODM_BUYER_LINE_CARD B , ODM_MANUFACTURER_OBJ_D C, PC_MCO_ITEM_
F D, ITEM_MFR_PART_F G,ODM_AML_PREF_STATUS_D H , PC_MCO_F I,ODM.
ODM_AGILE_FLEX_STG AGILE_FLEX WHERE F.RS_SUPPLIER_ID = B.BLC_SUP
PLIER_ID(+) AND D.PC_MCO_ID = I.PC_MCO_ID AND D.PC_MCO_WORKFLOW_
ID=I.PC_MCO_WORKFLOW_ID AND B.BLC_OBJECT_ID = C.ENTRY_ID (+) AND
 F.SCI_ITEM_SRC_ID = D.ITEM_ID (+) AND C.ENTRY_ID = G.MANUFACTUR
ER_ID AND D.PC_MCO_WORKFLOW_ID IN ( SELECT PC_MCO_WORKFLOW_ID FR
OM PC_MCO_WORKFLOW_D G WHERE WORKFLOW='PCM OEM MCO V1.1' AND WOR
KFLOW_STATE NOT IN ('Canceled') ) AND D.CREATED_TIME_ID = (SELEC
T MAX(E.CREATED_TIME_ID) FROM PC_MCO_ITEM_F E WHERE E.PC_MCO_ID=
D.PC_MCO_ID) AND G.CHANGE_ID = D.PC_MCO_ID AND G.ITEM_MFR_PART_P
REFER_STATUS_ID = H.ENTRY_ID AND F.SCI_ITEM_SRC_ID=G.ITEM_ID(+)
AND G.ITEM_ID = AGILE_FLEX.ID(+) AND G.MANUBY_ID =AGILE_FLEX.OBJ
_ID(+))
EQADMRT > list
  1  select
  2  --inst_id,sql_id,hash_value,
  3  piece, sql_text
  4  from gv$sqltext b
  5  where b.hash_value=&1
  6  -- and inst_id=1
  7* order by inst_id,hash_value,b.piece
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.