Check for Database Objects which will not extend in Oracle 19c

Database objects typically won't extend if their underlying tablespace has run out of space or does not have auto-extension enabled.

Query to check tablespace usage:

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024 AS size_in_mb,
    maxbytes / 1024 / 1024 AS max_size_in_mb,
    autoextensible
FROM
    dba_data_files
WHERE
    tablespace_name NOT IN ('SYSTEM', 'SYSAUX', 'TEMP');
    

• Key points to check:
  • AUTOEXTENSIBLE = NO: The file will not grow automatically.
  • SIZE_IN_MB close to MAX_SIZE_IN_MB: The file is near its maximum allowed size.
  • Tablespaces with no free space will block object growth.

To confirm if there is available space in the tablespace:

SELECT
    tablespace_name,
    SUM(bytes) / 1024 / 1024 AS free_space_mb
FROM
    dba_free_space
GROUP BY
    tablespace_name;
    

Objects in Oracle 19c may fail to extend if they reach the maximum number of extents allowed by their storage clause.

Query to check objects near maximum extents:

SELECT
    segment_name,
    segment_type,
    owner,
    tablespace_name,
    extents,
    max_extents
FROM
    dba_segments
WHERE
    max_extents IS NOT NULL
  AND extents >= max_extents;
    

• Key points:
  • EXTENTS: The current number of extents allocated to the object.
  • MAX_EXTENTS: The maximum allowed extents (can be UNLIMITED for modern configurations).

Certain storage parameters (like PCTINCREASE or INITIAL) might prevent an object from allocating more extents.

Query to check storage parameters for objects:

SELECT
    segment_name,
    segment_type,
    owner,
    tablespace_name,
    initial_extent,
    next_extent,
    pct_increase
FROM
    dba_segments
WHERE
    tablespace_name = 'YOUR_TABLESPACE_NAME';
    

• Key points:
  • NEXT_EXTENT: The size of the next extent to be allocated.
  • If NEXT_EXTENT exceeds the available free space in the tablespace, the object will not extend.
  • PCT_INCREASE: Ensure it is reasonable (a value of 0 is typical for modern databases).

Users with limited quota on a tablespace might also cause objects not to extend.

Query to check user quotas:

SELECT
    tablespace_name,
    username,
    bytes / 1024 / 1024 AS allocated_mb,
    max_bytes / 1024 / 1024 AS max_allocated_mb
FROM
    dba_ts_quotas
WHERE
    max_bytes > 0 -- Only users with quotas
  AND bytes >= max_bytes;
    

Objects in READ-ONLY tablespaces cannot extend.

Query to find tablespaces in READ-ONLY mode:

SELECT
    tablespace_name,
    status
FROM
    dba_tablespaces
WHERE
    status = 'READ ONLY';
    

If there are persistent issues with object extensions, the Oracle alert log often provides clues about which objects are failing and why.

ALTER DATABASE DATAFILE '/path/to/datafile.dbf'
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
    

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 1G;
    

ALTER TABLE schema.table_name MOVE TABLESPACE larger_tablespace;
    

ALTER TABLE schema.table_name STORAGE (NEXT 128K MAXEXTENTS UNLIMITED);
    

ALTER USER username QUOTA UNLIMITED ON tablespace_name;