The user has observed a fairly fast and an unexpected growth in an oracle database which runs on the JAVA ONLY system.
The issue can be due to various different reasons, please refer to the examples listed below.
- On a regular basis, the Basis objects are not being cleaned up;
- Application specific objects are not being archived regularly;
- Fragmentation of Indexes, LOBs and Tables
Resolution
1. Verifying the objects which increase the maximum.
User has two options in a JAVA ONLY system for verifying the objects which increase the most, by checking the transaction and by setting up a Solution Manager connection to the remote database.
DBAcockpit-> Space-> Segments-> Overview-> TOP Growth or by issuing below statement via sqlplus. Note that you will see the following "Enter value for days_back to:" right after executing the statement, you can set 30 for results of TOP Growth for past month:
column "MB Growth" justify right format 9,999,999.99
column "Total Object Size (MB)" justify right format 9,999,999.99
set linesize 150
set pages 300
set feedback off
select * from
(select c.segment_name,
sum(space_used_delta) / 1024 / 1024 "MB Growth",
avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
group by segment_name)
order by 2 desc;
2. What should you do once you have the list of most increasing object?
Once the user has identified the most increasing objects he should involve the application specifics for each object for finding out a proper way to archive or cleaning up the specific object.
Incase, the object is a basis object, then user will have to proceed as per recommendations from doc #706478.
If the object is a LOB segment then user will have to find the specific object which belongs to it by issuing following statement:
select owner, table_name from dba_lobs
where '<invoved_lobsegment>' in (segment_name, index_name);
The results will indicate the object that belongs to such lobsegment, once you have the object name you should involve the application specifics for carrying out the archive or clean up of the object.
3. Preventing the database from increasing considerably.
For avoiding such kind of issues user must keep the following in mind:
- Archiving and cleaning up the most increasing objects on a regular basis alongwith the application team responsible;
- Performing a thorough clean- up of the basis objects regularly by following doc#706478;
- For fragmented objects user must consider doing a reorganization by following doc #646681, refer to doc #821687 for details related to the fragmented objects.
- for avoiding any known oracle bugs regarding any unexpected growth like mentioned on doc #1741960 and consider having the latest SBP/Patch collection in place.