Avoiding space problems

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
How can I avoid problems with tables running out of space?

Resolution:
There are two kinds of problems which can occur:
1) the table reaches its maxextents value
and
2) the table needs another extent but the tablespace in which the table resides doesn't have a large enough extent.

1) The table reaches its maxextents value. First you need to determine what the max_extents values are for your tables. If max_extents are unlimited, you can go on to #2. If they are 505 or some other number, then you should run util a/17/11 to see which tables might be near their maximum.

2) The table needs another extent but the tablespace in which the table resides doesn't have a large enough extent. To prevent this, you should

[step 1] examine each library's file_list to see what tablespaces the files reside in and what the largest secondary allocation is for each tablespace. (Note: If you have Locally-Managed Tablespaces, then the secondary allocation in the file_list is not used.) Then you need to

[step 2] Do util o/14/1 and find the MAX_EXTENT value for each tablespace. This value shows you the largest available extent in the tablespace. Then

[step 3] If this step 2 value is not greater than the largest secondary allocation for this tablespace in the file_list (in step 1), you need to do something. You need to either decrease the size of the allocations, add space, or defragment the tablespace. Note that this problem *could* occur even though the total amount of available space in the tablespace (the util o/14/1 "TOTAL_FREE_SPACE" value) is OK. Consult your Oracle person as to what to do. Note that files can be added to a tablespace with util o/13/3 [in 14.2, util o/11]. Even if the step 2 value is not greater than the largest secondary allocation you may still be close to danger. You can use util o/14/2 to check the exact sizes of the extents remaining. A new site should take a weekly "snapshot" of the util o/14/1, to try to get a sense of how fast each of the tablespaces is being filled up. Tables such as the z30h, z35, z36h, and z71, though they start out small, can show great percentage increases. Note: If you have "Locally Managed Tablespace", then the allocations in the ALEPH file_list are not going to be used. Check with your Oracle administrator.