Thursday, August 23, 2012

Mystery Tables (Part I)

Recently,  I have been trying to figure out the use/need for three mystery tables in our EBS 12 system. These tables were basically archiving Global Temporary Table (GTT) data generated by Advanced Pricing tables such as: QP.QP_PREQ_LINE_ATTRS_TMP_T via Trigger. There were no comments in the trigger to indicate why this was done.

Usually, the first place I look is for custom code within the database that accesses this table in any way. One of my favorite statements is:

SELECT *
  FROM dba_objects
 WHERE UPPER (text) LIKE '%XXOC_QP_PREQ_LINE_ATTRS_TMP_T%';

However, only the Trigger appeared - no other Procedures or Packages. I also checked on other Production systems just to check if a DBLink was being used. Nothing.

I also checked for any SQL History:

SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SQL_ID IN (
        select SQL_ID from DBA_HIST_SQL_PLAN
        where OBJECT_NAME = 'XXOC_QP_PREQ_LINE_ATTRS_TMP_T');

Since we use Oracle Discoverer, I checked there next using something like:

   SELECT Obj.Obj_Name,
         Obj.Obj_Ba_Id,
         Seg.Seg_Chunk1,
         Seg.Seg_Chunk2,
         Seg.Seg_Chunk3,
         Seg.Seg_Chunk4
    FROM Eul_Us.Eul5_Segments Seg, Eul_Us.Eul5_Objs Obj
   WHERE     Seg.Seg_Seg_Type = 5
         AND Seg.Seg_Cuo_Id = Obj.Obj_Id
         AND (   upper(Seg.Seg_Chunk1) LIKE '%XXOC_QP_PREQ_LDETS_TMP_T%'
              OR upper(Seg.Seg_Chunk2) LIKE '%XXOC_QP_PREQ_LDETS_TMP_T%'
              OR upper(Seg.Seg_Chunk3) LIKE '%XXOC_QP_PREQ_LDETS_TMP_T%'
              OR upper(Seg.Seg_Chunk4) LIKE '%XXOC_QP_PREQ_LDETS_TMP_T%')
ORDER BY Obj.Obj_Id, Seg.Seg_Sequence;

which showed me a report that was using two of the smaller tables. Great, I can purge unwanted data according to the report's user's specifications using the following methodology:
  1. Create a CTAS statement containing keep records in each table 
  2. Truncate original table 
  3. Perform 10g SHRINK on table 
  4. Insert records from Table created in #1 back into original table 
  5. Re-analyze table
However, I'm still left with one last table - and it contains about 190m rows (20GB) of data. Another interesting fact about this table is that it has no indexes on it - and no date columns. What ever could be using it? Any use clause would force a Full Table Scan of 190m rows?

Instead of turning on Auditing in our Production EBS system,  I decided to use Fine Grain Auditing (FGA) which is new since Oracle 9i. 

To start, I created this little Table to capture some of this audit information:

create table XXOC.PREQ_audit
( whodidit varchar2(40)
, whenwasit timestamp
, sql_executed varchar2(4000)
)
/

Next, I created a Handler:

create or replace
package XXOC.AUDIT_HANDLER
is
PROCEDURE HANDLE_PREQ_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
);
end;
/

create or replace
package body XXOC.AUDIT_HANDLER
is
PROCEDURE HANDLE_PREQ_ACCESS
( object_schema VARCHAR2
, object_name VARCHAR2
, policy_name VARCHAR2
) is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into XXOC.PREQ_audit
  ( whodidit, whenwasit, sql_executed)
  values
  ( user, systimestamp, sys_context('userenv','current_sql'))
  ;
  commit;
end HANDLE_PREQ_ACCESS;
end;
/

Lastly, I created this FGA Policy:

begin
  dbms_fga.add_policy
  ( object_schema=>'XXOC'
  , object_name=>'XXOC_QP_PREQ_LINE_ATTRS_TMP_T'
  , policy_name=>'PREQ_ACCESS_HANDLED'
  , handler_schema => 'XXOC'
  , handler_module => 'AUDIT_HANDLER.HANDLE_PREQ_ACCESS'
  );
end;
/

To change/remove, you can simply use:

begin
DBMS_FGA.DROP_POLICY(
   object_schema  =>'XXOC', 
   object_name    =>'XXOC_QP_PREQ_LINE_ATTRS_TMP_T',    
   policy_name    =>'PREQ_ACCESS_HANDLED'
   );
end;
/

to Drop and Recreate if needed.

This will now capture any Select operations on this target table, and by default, log information to: SYS.FGA_LOG$ table. The above example illustrates that you can extent this functionality to do just about anything with this information - including sending a email alert.

Depending on what comes through with FGA, will determine next steps. More later.

1 comment:

  1. Military armor plates are created from sheets of high power steel. The material is made by alloying steel with selection of|quite lots of|a wide selection of} components like carbon, manganese, and copper to improve its hardness. As in the case of aluminized steel, galvanized steel is made by coating steel with zinc via scorching dipping to make it extra corrosion resistant. Sheets of galvanized steel are used for making car bodies, water pipes, heaps of|and a lot of} building components like fences, roofs, and staircases. Manufacturers can reduce sheet steel utilizing selection of|quite lots of|a wide selection of} completely different pieces hottest thongs of equipment, a few of that are unique to sheet steel fabrication.

    ReplyDelete