Re: Table use investigation
Date: Wed, 22 Aug 2012 17:31:36 +0000
I guess that's what you get with a 5-yr-old running EBS R12, eh? :-)
Looking within another production EBS R12.1.1 database at the same standard table, I see...
SQL> select owner, name, type, referenced_owner, referenced_type
2 from dba_dependencies
3 where referenced_name = 'QP_PREQ_LINE_ATTRS_TMP_T';
OWNER NAME TYPE REFERENCED_O REFERENCED_TYPE
------------ ------------------------------ ----------------- ------------ -----------------
APPS QP_PREQ_LINE_ATTRS_TMP_T SYNONYM QP TABLE APPS OZF_ORDER_PRICE_PVT PACKAGE BODY APPS SYNONYM APPS QP_PREQ_LINE_ATTRS_TMP VIEW APPS SYNONYM APPS QP_NPREQ_LINE_ATTRS_TMP VIEW APPS SYNONYM ARCHIVE QP_PREQ_LINE_ATTRS_TMP_T SYNONYM APPS SYNONYM MERGE QP_PREQ_LINE_ATTRS_TMP_T SYNONYM APPS SYNONYM
6 rows selected.
I'll bet someone created that trigger to populate the "mystery" table as custom-objects on the "seeded" standard Oracle global temporary table, probably for tuning/debugging/troubleshooting purposes (i.e. capturing actual data values stored in the GTT, etc), and it sounds like it can be an enormous problem.
I think you need to find out why that trigger is there, if it is indeed a "valid" custom object, and if it isn't, then disable it immediately.
Auditing the usage of the QP_PREQ_LINE_ATTRS_TMP_T table is not going to help, because pretty much everybody with pricing responsibility is causing that trigger to fire. It might be useful to audit all SELECT operations from the "mystery" table, but if I'm right, and someone was using it to gather data values for tuning a query using the QP_PREQ_LINE_ATTRS_TMP_T table, and they forgot to clean up after they were done, then you won't find any SELECTs on table, as they were gone 186m rows ago.
Either way, check to see if "mystery" table is a valid customization, and if not, disable or drop that trigger as soon as possible.
...and don't let any more 5-yr-olds on to your EBS R12 system...
Hope this helps...
consultant => Evergreen Database Technologies, Inc. postal => PO Box 352151, Westminster CO 80035 email => Tim_at_EvDBT.com
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
From: Anthony Ballo [mailto:anthony.ballo_at_onecall.com] Sent: Wednesday, August 22, 2012 11:02 AM To: oracle-l_at_freelists.org
Subject: Table use investigation
I have assumed responsibility for a 5 year old running EBS 12 database and came across a mysterious table that has grown to 186m rows. It is created via trigger on a standard EBS (Advanced Pricing) table named: QP.QP_PREQ_LINE_ATTRS_TMP_T. This trigger basically archives the data from this GTT from Advanced Pricing in EBS.I have searched dba_objects with: select * from dba_source where upper(text) like'%XXQP_PREQ_LINE_ATTRS_TMP_T%'- but the only object returned is the trigger that writes to it. I have also searched SQL with:SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID IN ( select SQL_ID from DBA_HIST_SQL_PLAN where OBJECT_NAME = 'XXQP_PREQ_LINE_ATTRS_TMP_T')- nothing returned their either. Since we use Discoverer, I searched: select * from EUL_US.EUL5_OBJSwhere SOBJ_EXT_TABLE like '%XQP_PREQ_LINE_ATTRS_TMP_T%'And with: SELECT Obj.Obj_Name,Obj.Obj_Ba_Id,Seg.Seg_Chunk1,Seg.Seg_Chunk2,Seg.Seg_Chunk3,Seg.Seg_Chunk4FROM Eul_Us.Eul5_Segments Seg, Eul_Us.Eul5_Objs Obj WHE RE Seg.Seg_Seg_Type = 5AND Seg.Seg_Cuo_Id = Obj.Obj_IdAND ( upper(Seg.Seg_Chunk1) LIKE '%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk2) LIKE '%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk3) LIKE '%XXQP_PREQ_LINE_ATTRS_TMP_T%'OR upper(Seg.Seg_Chunk4) LIKE '%XXQP_PREQ_LINE_ATTRS_TMP_T%')ORDER BY Obj.Obj_Id, Seg.Seg_Sequence;- both return nothing also. Another observation: Since there are no indexes on this table, I wonder what really could be using this table as any reads would require a very long full table scan (FTS) of 186m rows?Is there any other place I can look to see? I was thinking that we have a customization to Advanced Pricing and I was going to check code on the EBS Application Server next but based on the above, I'm doubtful that it would return anything. I also did "Find in Files" on every file on our IT share- nothing turned up there also.There are two other tables that mimic the same characteristics - I found them being used in a Custom Folder in Di scoverer and have a plan ready forthese.Thanks,Anthony--http://www.freelists.org/webpage/oracle-l
http://www.freelists.org/webpage/oracle-l Received on Wed Aug 22 2012 - 12:31:36 CDT