OTM 6.2.10 purging of unwanted transmission data

 We perfomred and saved good amount of space by purging unwanted data on otm by following standard purge methods as per oracle  .Below are some of details .Activity needs downtime during index rebuild .

Biggest gain will be speed .

1.       Using GLOGOWNER/ user of database execute below command,

set timi on time on

set serveroutput on

                exec domainman.transmission_purge(265,4000,1000);

                Date Difference: 01.01.2015 – 22.09.2015 = 265

Keep 265 days old data, activity will take max 4000 minutes and execute it for bunches of             1000 records

265 Days can vary depending on the data to be required in OTM as per client’s approval

2.       Execute below statement to release space on database level

set timi on time on

ALTER TABLE I_TRANSMISSION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSMISSION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

ALTER TABLE I_TRANSMISSION DISABLE ROW MOVEMENT;

                ALTER TABLE I_TRANSACTION ENABLE ROW MOVEMENT;

ALTER TABLE I_TRANSACTION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

                ALTER TABLE I_TRANSACTION DISABLE ROW MOVEMENT;

After that we have to rebuild indexes to avoid performance issue, please see below attached list of indexes which need to be rebuild.

Oracle SOA 11.1.1.5 purging

SOA being middleware mostly does not requires much of data to be stored .We can follow standard oracle purge doc for this .Steps we did are below


select sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='DEV_SOAINFRA';

SUM(BYTES/1024/1024/1024)
-------------------------
               325.999969

connect as DEV_SOAINFRA

Create packages 

@/home/oraqa/soa_purge/soa_purge_scripts.sql

========================

@soa_purge_scripts.sql

Procedure created.

Function created.

Type created.

Type body created.

PL/SQL procedure successfully completed.

Package created.

Package body created.

===================

Run Purge.sql  ---Modify Dates accordingly

$ cat purge.sql

DECLARE

   MAX_CREATION_DATE timestamp;

   MIN_CREATION_DATE timestamp;

   batch_size integer;

   max_runtime integer;

BEGIN

   MIN_CREATION_DATE := to_timestamp('2014-01-01','YYYY-MM-DD');

   MAX_CREATION_DATE := to_timestamp('2014-12-31','YYYY-MM-DD');

    max_runtime := 920;

   batch_size := 5000;

     soa.delete_instances(

     min_creation_date => MIN_CREATION_DATE,

     max_creation_date => MAX_CREATION_DATE,

     batch_size => batch_size,

     max_runtime => max_runtime,

     purge_partitioned_component => false,

     ignore_state => true);

  END;

ALTER TABLE XML_DOCUMENT MODIFY lob (DOCUMENT) (shrink space);

Oracle DB 11g : How to restrict users to login to DB from specific conditions

Lot of time developers run big queries on prod from toad etc and screw up entire database .This is how you can enable filetrs on user machine  ,tool etc . You can create a logon trigger at db level to restrict users to login from toad and sql developer .


create or replace TRIGGER block_toad
  AFTER LOGON ON DATABASE
  DECLARE
   --Declare a cursor to find out the program
   --the user is connecting with.
    CURSOR user_prog IS
    SELECT program,username FROM v$session
    WHERE audsid=sys_context('USERENV','SESSIONID');
   --Assign the cursor to a PL/SQL record.
   user_rec user_prog%ROWTYPE;
   BEGIN
   OPEN user_prog;
   FETCH user_prog INTO user_rec;
   IF user_rec.program IN ('TOAD.exe', 'toad.exe','Toad.exe','TOAD.EXE','SQL Developer') and user_rec.username in ('APPS')
   THEN
   RAISE_APPLICATION_ERROR(-20001, 'You are not authorised to use **** as per policy in prod ');
END IF;
 CLOSE user_prog;
 END;
/