When a segment needs another extent, Oracle must find suitable contiguous free blocks in a data file that belongs to its tablespace. A failure to extend calls for an allocation and capacity check. Inspect the free extents, the file type, the current file size, the automatic-growth configuration, and the actual storage headroom, then choose one corrective action.
Oracle DBA Lesson 58 Part 1 — Choose the Correct Data-File Growth Action
Understand the failed allocation
An extent is a group of contiguous database blocks allocated to a segment. In this example the next request is 8 MiB, and the largest available free extent is 2 MiB. Free space scattered through a file, or across several files, stays in separate pieces. Those pieces are not one contiguous extent. File size includes allocated object space, free space, and file metadata. Occupied table data is a separate measurement.
Capture the exact allocation error, the object, the tablespace, and the requested blocks. Convert the blocks with that tablespace's BLOCK_SIZE. Confirm that the failure is a space-extension problem. A quota error, an unavailable file, or an unrelated application error needs its own diagnosis.
SHOW CON_NAME
SELECT d.file_id, d.file_name,
ROUND(d.bytes / 1048576) AS file_mib,
d.autoextensible,
ROUND(d.maxbytes / 1048576) AS max_mib,
t.bigfile
FROM dba_data_files d
JOIN dba_tablespaces t
ON t.tablespace_name = d.tablespace_name
WHERE d.tablespace_name = 'APP_GROW';
SHOW CON_NAME is a SQL*Plus command. It names the current container, the PDB whose files these views describe. For APP_GROW, the file is a 64 MiB smallfile with automatic extension off:
| FILE_ID | FILE_NAME | FILE_MIB | AUTOEXTENSIBLE | MAX_MIB | BIGFILE |
|---|---|---|---|---|---|
| 12 | /u02/oradata/app_grow01.dbf | 64 | NO | 0 | NO |
BYTESis the current physical file size recorded by Oracle.AUTOEXTENSIBLE=NOdisables automatic extension. Turning autoextend off resetsNEXTandMAXSIZEto zero. The zero in this row is an inactive automatic-growth setting. It is not a ceiling on a later manual resize.- When autoextend is enabled,
MAXBYTESis its configured automatic-growth ceiling. File, platform, and storage limits also apply. BIGFILE=NOidentifies a smallfile tablespace. A smallfile tablespace can hold more than one data file, within the applicable limits.BIGFILE=YESidentifies a single-file tablespace.FILE_IDis the absolute file number. Keep the fullFILE_NAMEwith it, and use both when you choose the file.
Inspect free extents in that same PDB:
SELECT d.file_id,
ROUND(NVL(SUM(f.bytes), 0) / 1048576, 2) AS free_mib,
ROUND(NVL(MAX(f.bytes), 0) / 1048576, 2) AS largest_extent_mib
FROM dba_data_files d
LEFT JOIN dba_free_space f ON f.file_id = d.file_id
WHERE d.tablespace_name = 'APP_GROW'
GROUP BY d.file_id
ORDER BY d.file_id;
The outer join keeps files that have no free extents. DBA_FREE_SPACE can omit free space for an offline locally managed file, so read it while the target is online. The largest extent is measured per file. Keep each file's result when you compare files.
Sizes in this lesson are MiB: 1 MiB = 1,048,576 bytes. The Oracle size suffix M is that binary size. The narration calls the same quantity megabytes.
The data-file inventory records Oracle's file sizes. Free capacity on the filesystem or in ASM is a separate measurement. The destination in this lesson has 160 MiB available before any change. Keep a reserve for other files, concurrent growth, and operating needs, and count that shared headroom once.
See DBA_DATA_FILES, DBA_TABLESPACES, DBA_FREE_SPACE, and managing data files and temp files.
Choose one action
Each growth command below is an alternative. Running one of them changes the capacity plan for the file or tablespace it names. Match the command to the file type, the current size, the growth settings, and the storage that is actually free.
Immediate resize of the existing smallfile data file
ALTER DATABASE DATAFILE 12 RESIZE 96M;
RESIZE sets the absolute resulting size. From 64 MiB to 96 MiB, the command attempts a 32 MiB increase of the existing physical file. The change is file length. File metadata means the added length is not 32 MiB of usable table data.
If storage cannot supply the increase, the resize fails. After it succeeds, rerun the inventory and the free-extent query. Confirm the resulting size and the largest free extent before you retry the workload. Automatic-growth settings stay a separate check.
Bounded automatic extension
Starting again from the original 64 MiB file:
ALTER DATABASE DATAFILE 12
AUTOEXTEND ON NEXT 16M MAXSIZE 128M;
NEXT is the minimum automatic-extension increment. MAXSIZE is the largest size automatic extension may reach. The existing file may then grow when space is required, from 64 MiB toward 128 MiB, within storage and file limits. Turning autoextend on leaves the current size at 64 MiB. The command leaves future space unallocated until growth actually occurs.
Set NEXT and MAXSIZE deliberately when you turn automatic growth back on. Turning it off earlier reset both values to zero. Watch later growth and the storage it consumes. MAXSIZE UNLIMITED still stops at database, file, platform, and storage limits, so the ceiling still needs a capacity plan.
Another file in a smallfile tablespace
When the placement and capacity plan calls for another file:
ALTER TABLESPACE app_grow ADD DATAFILE
SIZE 32M AUTOEXTEND ON
NEXT 16M MAXSIZE 128M;
The filename is omitted, so placement depends on Oracle Managed Files and an effective DB_CREATE_FILE_DEST. Confirm that destination and its free storage before you use this form. Without that setting, put an explicit valid filename in the command. REUSE overwrites an existing file of the same name.
The command creates a new 32 MiB file. The 128 MiB maximum belongs to that new file. The original 64 MiB file remains, so the combined current file size in this alternative is 96 MiB. File metadata reduces the usable portion. The new file needs capacity now, and later extension needs headroom then. Check the file-count limits, including DB_FILES and the operating-system or platform limit, and check the size limits.
Rerun the inventory. Record the new FILE_ID, the full FILE_NAME, the current size, and the growth configuration, then check the tablespace's free extents. Filling an existing file leaves that file full until you extend it or add a file.
See Oracle Managed Files and physical database limits.
Bigfile tablespace
A bigfile tablespace has one data file. A second data file cannot be added to it. Tablespace-level clauses operate on that single file. These two statements are independent alternatives for the bigfile tablespace APP_BIG:
ALTER TABLESPACE app_big RESIZE 96M;
ALTER TABLESPACE app_big
AUTOEXTEND ON NEXT 16M MAXSIZE 128M;
Check the existing size before you choose a resize target. File limits depend on block size and platform. The statements above change capacity. An online move of the file to another destination is a separate operation.
See managing tablespaces, ALTER DATABASE, ALTER TABLESPACE, and file specifications and autoextend.
Why a downward resize can fail
A smaller target is valid only when the proposed new end of the file lies past every required allocated block and the file metadata. An extent belonging to any segment near that end can block the shrink, even when much of the file is free. Inspect allocation across the whole file, using DBA_EXTENTS and the file metadata, before you plan a smaller size. One table's high-water mark describes that table. The file's shrink boundary also includes every other segment and the metadata.
A used-space total does not locate a safe new end. Moving an object so the end of the file can shrink affects indexes, availability, and dependent objects, and that move needs its own plan. After new allocations occupy the added area, a reverse resize is not a guaranteed return to the previous size.
Recap
A failed extent needs one contiguous free extent large enough for the request. For the 64 MiB APP_GROW smallfile with autoextend off, an 8 MiB request against a 2 MiB largest extent is a capacity choice among three alternatives. RESIZE 96M makes that existing file 96 MiB. Autoextend with NEXT 16M and MAXSIZE 128M leaves the current 64 MiB in place until an allocation needs more space. Adding a data file creates a separate 32 MiB file, and its 128 MiB ceiling belongs to that new file. Each choice spends real storage. The destination has 160 MiB free before the change, and that headroom is shared.
A bigfile tablespace such as APP_BIG keeps a single file. Resize it, or set its autoextend, with ALTER TABLESPACE. A later reduction can fail when allocated blocks or metadata sit beyond the proposed new end.
No comments:
Post a Comment