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