Re: Table use investigation

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.

No triggers.

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...

Tim Gorman
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...

-----Original Message-----

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