java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment TABLESPACE.SYS_LOB0000059665C00009$$ by 8 in tablespace TABLESPACE
Solution:
The problem was caused by insufficient space to extend a segment for the tablespace. Adding a datafile or adjusting the size of the current datafile of the tablespace can act as short-term solution.
Adding datafile:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE ‘<path_of_datafile>’ SIZE 10M;
ALTER TABLESPACE <tablespace_name> ADD DATAFILE ‘<path_of_datafile>’ SIZE 10M;
Enlarging the current datafile:
ALTER DATABASE DATAFILE ‘<path_to_datafile>’ RESIZE 10M;
ALTER DATABASE DATAFILE ‘<path_to_datafile>’ RESIZE 10M;
(Assuming you are adding a 10MB datafile and resizing the datafile to 10MB respectively.)
You may run the following SQL statement to check the status of various datafiles:
SELECT file_name, tablespace_name, bytes/1024/1024 MB, AUTOEXTENSIBLE FROM dba_data_files;
SELECT file_name, tablespace_name, bytes/1024/1024 MB, AUTOEXTENSIBLE FROM dba_data_files;
and use the following SQL to turn on AUTOEXTEND option:
ALTER DATABASE DATAFILE ‘<path_to_datafile>' AUTOEXTEND ON;
ALTER DATABASE DATAFILE ‘<path_to_datafile>' AUTOEXTEND ON;
Purging Dehydration Store
There are several ways to purge large amounts of instances in SOA Suite 11g database - dehydration storage;
1. By dropping the SOA Suite repository and next, to recreate it using RCU creation utility providing exactly the same credentials.
2. By executing a set of existing database procedures delivered by Oracle (fixed in SOA Suite 11.1.1.3.0 PS2).
3. By truncating several tables in BPEL SOAINFRA schema.
4. Perform a maintenance to free up some space in a database storage
1. Dropping the SOA Suite repository using RCU is straight forward. Just run the RCU utility select "Drop" instead of "Create" and follow the installer.
2. Examples of procedures in SOAINFRA schema that could clean up dehydration storage:
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES NUMBER;
DELETED_INSTANCES NUMBER;
DAY_OFFSET NUMBER;
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';
DAY_OFFSET := 5;
MAX_INSTANCES := 50000;
DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA
);
END DELETE_OLD_INSTANCES;
Example of procedures 2 - purging instances of multiple composites using STATE filters.
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
MAX_INSTANCES NUMBER;
DELETED_INSTANCES NUMBER;
DAY_OFFSET NUMBER;
FILTER.COMPOSITE_PARTITION_NAME:='default';
FILTER.COMPOSITE_NAME := 'My_Composite_Name';
FILTER.COMPOSITE_REVISION := '1.00.01';
--USE either ONLY ONE filter per run;
FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_RECOVERY_REQUIRED;
--FILTER.STATE := fabric.STATE_COMPLETED_SUCCESSFULLY;
--FILTER.STATE := fabric.STATE_FAULTED;
--FILTER.STATE := fabric.STATE_TERMINATED_BY_USER;
--FILTER.STATE := fabric.STATE_SUSPENDED;
--FILTER.STATE := fabric.STATE_STALE;
--FILTER.STATE := fabric.STATE_UNKNOWN;
DAY_OFFSET := 5;
MAX_INSTANCES := 50000;
DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
Example of procedures 3 - purging instances filtered by name of the composites.
PROCEDURE DELETE_OLD_INSTANCES
FILTER INSTANCE_FILTER := INSTANCE_FILTER();
PROCESS_ARRAY PROCESS_ARRAY_TYPE;
MAX_INSTANCES NUMBER;
DELETED_INSTANCES NUMBER;
DAY_OFFSET NUMBER;
FILTER.COMPOSITE_PARTITION_NAME:='default';
PROCESS_ARRAY := PROCESS_ARRAY_TYPE('My_Composite_1',
'My_Composite_2',
'My_Composite_n');
DAY_OFFSET := 7;
MAX_INSTANCES := 10000;
FILTER.COMPOSITE_NAME := PROCESS_ARRAY(i);
DELETED_INSTANCES := FABRIC.DELETE_COMPOSITE_INSTANCES(
FILTER => FILTER,
MAX_INSTANCES => MAX_INSTANCES,
PURGE_PARTITIONED_DATA => PURGE_PARTITIONED_DATA);
END DELETE_OLD_INSTANCES;
3. There is also a way to truncate several tables in BPEL SOAINFRA schema and delete XML_DOCUMENTS table.
This approach is not recommended for the production environments, but in Dev/Test will do the job in a very fast way.
This approach is not recommended for the production environments, but in Dev/Test will do the job in a very fast way.
In SOAINFRA schema:
TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE REFERENCE_INSTANCE DROP storage;
TRUNCATE TABLE REJECTED_MSG_NATIVE_PAYLOAD DROP storage;
TRUNCATE TABLE INSTANCE_PAYLOAD DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_FAULT DROP storage;
TRUNCATE TABLE COMPOSITE_SENSOR_VALUE DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE_ASSOC DROP storage;
TRUNCATE TABLE COMPOSITE_INSTANCE DROP storage;
TRUNCATE TABLE AUDIT_TRAIL DROP storage;
TRUNCATE TABLE CUBE_INSTANCE DROP storage;
TRUNCATE TABLE CUBE_SCOPE DROP storage;
TRUNCATE TABLE AUDIT_DETAILS DROP storage; DELETE FROM XML_DOCUMENT; COMMIT;
1 comments:
Thanks a lot! You made a new blog entry to answer my question; I really appreciate your time and effort.
RESTful Web Services Corporate TRaining
RESTful Web Services Training In Chennai
Java Projects for Engineering Students of Computer Science
Project Centers in Chennai For CSE
Post a Comment