In Oracle Database, as data grows, segments can run short of space, which may trigger errors and degrade performance. The key mechanism to address this is segment extension (Extent Allocation). This article provides a comprehensive explanation useful for everyone from beginners to practitioners.
1. What is segment extension? Basic concepts
Oracle Database manages data with the following hierarchy:
Data storage structure
Database
├─ Tablespace
│ ├─ Segment -- tables and indexes
│ │ ├─ Extent -- a set of multiple blocks
│ │ │ ├─ Block -- the smallest unit of the database
- Block: The smallest unit of the database. Size is 2KB–32KB.
- Extent: A region composed of multiple blocks.
- Segment: The unit that stores data, such as tables and indexes.
- Tablespace: Physical storage that holds segments.
How segment extension works
- Initial state (INITIAL extent)
An extent specified at creation time is allocated.
[Extent 1] [Free space]
- When data grows (NEXT extent)
New extents are allocated dynamically as needed.
[Extent 1] [Extent 2] [Free space]
- Extent management methods
- AUTOALLOCATE: Oracle automatically manages extent sizes.
- UNIFORM: Manages sizes uniformly (e.g., all 1MB).
2. Segment extension errors and causes
Here are the main causes of segment extension errors with concrete examples.
2.1 Insufficient free space in the tablespace
- Cause: The tablespace lacks enough free space for the required extent size.
- Error example:
ORA-01653: unable to extend table ... in tablespace 'USERS'
2.2 Datafile autoextend is disabled
- Cause: The datafile was created with a fixed size and cannot extend.
- Error example:
ORA-01658: unable to create INITIAL extent in tablespace 'USERS'
2.3 User quota exceeded
- Cause: The user exceeded the allowed space within the tablespace.
- Error example:
ORA-01536: space quota exceeded for tablespace 'USERS'
2.4 Segment reached the maximum number of extents
- Cause: The segment hit the upper limit for the number of extents that can be allocated.
- Error example:
ORA-01631: max # extents (505) reached in table USER_NAME.TABLE_NAME
3. Concrete methods to resolve errors
3.1 Increase free space in the tablespace
- Check free space in the tablespace:
SELECT tablespace_name, bytes/1024/1024 AS free_space_mb FROM dba_free_space; - Resize a datafile:
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/users01.dbf' RESIZE 500M; - Add a new datafile:
ALTER TABLESPACE USERS ADD DATAFILE '/u01/app/oracle/oradata/users02.dbf' SIZE 100M AUTOEXTEND ON;
3.2 Enable autoextend
- Check datafile settings:
SELECT file_name, autoextensible, maxbytes/1024/1024 AS max_size_mb FROM dba_data_files; - Enable autoextend:
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/users01.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
3.3 Remove or increase quota limits
- Check the user’s quota:
SELECT tablespace_name, max_bytes/1024/1024 AS max_size_mb FROM dba_ts_quotas WHERE username = 'USER_NAME'; - Adjust the quota:
ALTER USER USER_NAME QUOTA 500M ON USERS;
Or remove the limit:ALTER USER USER_NAME QUOTA UNLIMITED ON USERS;
3.4 Remove the max extents limitation
- Check the current number of extents:
SELECT segment_name, extents, max_extents FROM dba_segments WHERE segment_name = 'TABLE_NAME'; - Remove or increase the limit:
ALTER TABLE TABLE_NAME STORAGE (MAXEXTENTS UNLIMITED);
4. Prevent operation interruption with RESUMABLE_TIMEOUT
4.1 Configure RESUMABLE mode
Set at the system level:
ALTER SYSTEM SET RESUMABLE_TIMEOUT = 3600; -- 1 hour
Set at the session level:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 600; -- 10 minutes
5. Operational best practices
- Regular monitoring
- Check tablespace free space, datafile status, and user quotas.
- Use locally managed tablespaces
- Use AUTOALLOCATE or UNIFORM to reduce manual management overhead.
- Leverage RESUMABLE mode
- Especially effective for batch jobs and heavy data operations.
6. Tuning for long-term operations
- Appropriate extent sizing: If too small, extensions occur frequently; if too large, disk space is wasted.
- Adjust storage parameters:
CREATE TABLE example_table ( id NUMBER, name VARCHAR2(50) ) STORAGE (INITIAL 64K NEXT 64K MAXEXTENTS UNLIMITED);
7. Summary
This article explained segment extension fundamentals, error handling, and operational best practices in detail.
- Learn to identify error causes and respond quickly
- Reduce operational load by utilizing RESUMABLE mode and automated management
- Prevent issues proactively through monitoring and tuning
This article targets Oracle Database 19c (other versions may differ in screens and defaults).
[Reference]
Oracle Database Database Performance Tuning Guide, 19c
コメント