Author: Volker Borowski

In this article we will discuss some key issues related to sizing of segments, tablespaces and datafiles so as to enable you to manage them efficiently, avoiding frustrating errors and getting huge performance gains for your database.

Identifying Segments Reaching MAX EXTENTS:

Many times we get MAX EXTENTS error message for our database. The first solution most database administrators do is to alter the max extents for the table. However if this thing has to be done on the same table again and again then one need increase the extent size for that table.

Below query help you determine the segment that are near to cross MAX EXTENTS.

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, EXTENTS, MAX_EXTENTS
FROM DBA_SEGMENTS WHERE (MAX_EXTENTS-EXTENTS<=3);

If you get the UNABLE TO EXTEND due to lack of space then you might need to increase the size of your datafile or add more datafiles.

Below query will return the list of objects when max extents limit is at distance of 25 from allocated extents.

SELECT
   NVL(
      'SEGMENT ' || TRIM(SEGMENT_NAME) || 'OF ' || TRIM(OWNER) || ' MAX EXTENTS ' || MAX_EXTENTS || ' HAS       UTILIZED ' || EXTENTS || ' ONLY ' || MAX_EXTENTS-EXTENTS || ' ARE REMAINING','NO OBJECT'
   )
   EXTENT_REPORT,LENGTH(
      'SEGMENT ' || TRIM(SEGMENT_NAME) || 'OF ' || TRIM(OWNER) || ' MAX EXTENTS ' || MAX_EXTENTS || ' HAS       UTILIZED ' || EXTENTS || ' ONLY ' || MAX_EXTENTS-EXTENTS || ' ARE REMAINING'
   )
   LENGTH
FROM DBA_SEGMENTS
WHERE DECODE(TRUNC(MAX_EXTENTS-EXTENTS)/25,0,'ALERT ZONE','SAFE ZONE') ='ALERT ZONE'

It is recommended to set MAXEXTENTS unlimited with FIXED size extents. You can set pct_increase = 0 and initial = next for all objects in a tablespace. PCT_INCREASE increases the NEXT size every time a new extent is allocated but it requires some monitoring to avoid unexpected growth of segments.

Identifying Tablespace Reaching MAX EXTENTS:

If your tablespace is reaching MAX EXTENTS then you need to count the number of rollback segments, the number of maxextents for these and the size of the rollback segments and the size of the tablespace for the rollback segments.

If (num_of_rollbacksegments * maxextents_of_rollbacksegments * size_of_rollbacksegments) > TABLESPACE
then you need to enhance the tablespace by distributing the rest of the tablespace to the rollback segment extents.

Identifying Tablespaces Reaching Maximum Limit:

Below can help you identify the tablespaces that are reaching maximum limit by returning the list of tablespaces with 200 MB free space.

SELECT
   DECODE( NAME, NULL, 'FREE SPACE IN MY DB', 'CHECK TABLESPACES ' || TRIM(NAME) || ' ' || TRIM(FREE) || 'M    FREE SPACE REMAING') SPACE_RESULTS
FROM

(
SELECT
   NVL( B.TABLESPACE_NAME, NVL(A.TABLESPACE_NAME,'UNKOWN') ) NAME,
   MBYTES_ALLOC MBYTES, ROUND( MBYTES_ALLOC-NVL(MBYTES_FREE,0) ) USED,
   NVL(MBYTES_FREE,0) FREE,
   --ROUND( ( (MBYTES_ALLOC-NVL(MBYTES_FREE,0))/MBYTES_ALLOC )*100 ) PCT_USED,
   NVL(LARGEST,0) LARGEST
FROM
(
   SELECT SUM(BYTES)/1024/1024 MBYTES_FREE, MAX(BYTES)/1024/1024 LARGEST, TABLESPACE_NAME
   FROM SYS.DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME
) A1,
(
   SELECT SUM(BYTES)/1024/1024 MBYTES_ALLOC, TABLESPACE_NAME
   FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME
)A2
WHERE (A1.TABLESPACE_NAME (+) = A2.TABLESPACE_NAME) A1)

WHERE (FREE < 200)

Identifying Datafile Size Limit:

Below tip can help you identify the maximum size limit of a datafile before switching to the next datafile . First of all we will have to create a TABLESPACE as follows and insert some data into it.

CREATE TABLESPACE MY_TABLESPACE
       DATAFILE '/U137/ORADATA1/MYDB/MY_TABLESPACE01.DBF' SIZE 400M REUSE
       AUTOEXTEND ON
       EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
       NOLOGGING
       PERMANENT
       ONLINE;

Now we will get the file_id of the datafile created for the MY_TABLESPACE tablespace.

SELECT FILE_ID,FILE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'MY_TABLESPACE';

 FILE_ID        AUT                     FILE_NAME
----------     -----     ----------------------------------------
   17           YES      /U137/ORADATA1/MYDB/MY_TABLESPACE01.DBF

Now we will query the FILEXT$ table to get the maximum size in database blocks to which the datafile can grow before switching to the next file.

SELECT * FROM FILEXT$ WHERE FILE#=17;

  FILE#           MAXEXTEND             INC
----------       ----------           --------
   17              3092816               1

Now we will get the blocksize of the database by querying V$PARAMETER table.

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'DB_BLOCK_SIZE';

  VALUE
  -----
  4096

The result shows that if default values are set and datafile extends then you get maximum value of 3092816 blocks with increments of 1 block. Below workaround gives you the maximum size of datafiles on a database having 4k database block size .

SELECT ((3092816*4096)/1024) AS MAX_SIZE FROM DUAL;

    MAX_SIZE
   ----------
    12371264

So our datafile can grow up to maximum size of 12.37 GB before we add a new datafile. Please note that the size you get depends on the database block size for a given platform . The maximum space that can be allocated to new datafile also depends on the free space your OS has on the disk. If your OS has lots of free space then you can set the size of datafile equal to the space available on the disk, the OS limit for a file size (2GB for some 32-bit OS) or the Oracle limit for a file size. Also note that it is very important to set the MAXEXTEND value when turning on the AUTOEXTEND option because if you do not set it then the first datafile would grow to the MAXEXTEND value and then it will shift to the next datafile. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   Oracle Recovery Manager - RMAN
   Handling Tricky Oracle Job Scheduling Problem!!
   Step by Step Guide to Oracle Parsing