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

WHEREUPPER(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%'

ORupper(Seg.Seg_Chunk2)LIKE'%XXOC_QP_PREQ_LDETS_TMP_T%'

ORupper(Seg.Seg_Chunk3)LIKE'%XXOC_QP_PREQ_LDETS_TMP_T%'

ORupper(Seg.Seg_Chunk4)LIKE'%XXOC_QP_PREQ_LDETS_TMP_T%')

ORDERBY 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:

Create a CTAS statement containing keep records in each table

Truncate original table

Perform 10g SHRINK on table

Insert records from Table created in #1 back into original table

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.