Read the Product
documentation and implement accordingly. This is just a sample.
There are some
limitations.
. If you use the
purge_soainfra_oracle.sql PL/SQL script provided in releases before 11g Release
1 (11.1.1.4), note that this script is only supported on Oracle databases.
There is no purge script support on the Microsoft SQL Server or IBM DB2
database, either with the purge_soainfra_oracle.sql purge script or with the
newer purge script provided with release 11g Release 1 (11.1.1.4 or later).
Only Oracle databases are supported.
Below are the steps to Purge
/ clean up the instances
Step1 : There are SQL scripts
provided by Oracle as part of RCU installation
Below is the Path to findout
the SQL Scripts
/lapps/Oracle/Middleware/Oracle_SOA1/rcu/integration/soainfra/sql/soa_purge
Step2: Grant the privileges to the “<DEV_SOAINFRA> (Name
of your schema) “user, then login to the <DEV_SOAINFRA> schema.
SQL> GRANT EXECUTE ON DBMS_LOCK
TO <DEV_SOAINFRA>;
Grant succeeded.
SQL> GRANT CREATE ANY JOB TO
<DEV_SOAINFRA>;
Grant succeeded.
Step3: Check the Procedures available or not. If you are
trying this for the first time then probably you need to run the Script to
install the Procedure
SQL> <Oracle SOA
Home>/rcu/integration/soainfra/sql/soa_purge/soa_purge_scripts.sql
Procedure created.
Function created.
Type created.
Type body created.
PL/SQL procedure successfully
completed.
Package created.
Package body created.
Step4: You can
customize the according to your Purging strategy.
Below is the Parallel script available in Oracle Documentation:
DECLARE
max_creation_date timestamp;
min_creation_date timestamp;
retention_period timestamp;
BEGIN
min_creation_date :=
to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date :=
to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
soa.delete_instances_in_parallel(
min_creation_date => min_creation_date,
max_creation_date => max_creation_date,
batch_size => 5000,
max_runtime => 60,
retention_period => retention_period,
DOP => 3,
max_count => 1000000,
purge_partitioned_component => false);
END;
Purging instances
filtered by name of the composites
CREATE OR REPLACE
PROCEDURE DELETE_OLD_INSTANCES
AS
TYPE PROCESS_ARRAY_TYPE IS VARRAY (2) OF VARCHAR (50);
FILTER INSTANCE_FILTER:=
INSTANCE_FILTER ();
PROCESS_ARRAY
PROCESS_ARRAY_TYPE;
MAX_INSTANCES NUMBER;
DELETED_INSTANCES NUMBER;
PURGE_PARTITIONED_DATA BOOLEAN: = TRUE;
DAY_OFFSET NUMBER;
BEGIN
DBMS_OUTPUT.put_line('----- BEGIN '|| SYSDATE ||'----------------');
FILTER.COMPOSITE_PARTITION_NAME:='default';
PROCESS_ARRAY := PROCESS_ARRAY_TYPE('Composite
Name1',
'Composite Name 2',
'Composite Name N');
DAY_OFFSET := 7;
FILTER.MIN_CREATED_DATE:=
TO_TIMESTAMP ('2010-01-01','YYYY-MM-DD');
FILTER.MAX_CREATED_DATE:=
SYSDATE - DAY_OFFSET;
MAX_INSTANCES := 5000;
FOR i IN PROCESS_ARRAY.FIRST..PROCESS_ARRAY.LAST
LOOP
FILTER.COMPOSITE_NAME := PROCESS_ARRAY(i);
DBMS_OUTPUT.put_line(' FILTER.COMPOSITE_NAME '|| FILTER.COMPOSITE_NAME);
DELETED_INSTANCES
:= FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA);
DBMS_OUTPUT.put_line('DELETED_INSTANCES: ' || DELETED_INSTANCES);
END LOOP;
DBMS_OUTPUT.put_line('---------BEGIN '|| SYSDATE ||'---------');
END
DELETE_OLD_INSTANCES;
Step 5: Space recovery is not included in the Purge Script.
Since this is part of Database Maintenance task.
Following sample
instructions can help in space recovery:
Running PL/SQL to release Space:
alter table <TABLE_NAME>
deallocate unused;
alter table <TABLE_NAME> enable row movement;
alter table <TABLE_NAME> shrink space;
alter table <TABLE_NAME> enable row movement;
alter table <TABLE_NAME> shrink space;
For more information, refer the Oracle Documentation:
No comments:
Post a Comment