A tablespace is a logical storage container within an Oracle Database. It provides the logical layer for organizing and managing database storage and is associated with one or more physical datafiles.
Tablespaces are used to store database objects such as tables, indexes, LOBs, materialized views, and other segments. Oracle also provides specialized tablespaces for storing UNDO information and temporary data.
Effective tablespace management is one of the fundamental responsibilities of an Oracle DBA. It includes monitoring space utilization, managing datafiles, configuring AUTOEXTEND, adding or removing storage, managing TEMP and UNDO, controlling user quotas, and troubleshooting space-related errors.
What is a Tablespace?
A tablespace is a logical storage structure within an Oracle Database. It provides a logical layer between database objects and the physical storage represented by datafiles or tempfiles.
A tablespace can contain one or more segments. Segments contain extents, and extents consist of Oracle data blocks. The physical storage for permanent and UNDO tablespaces is provided through datafiles, while temporary tablespaces use tempfiles.
| Storage Layer | Description |
|---|---|
| Tablespace | Logical storage container |
| Datafile / Tempfile | Physical storage associated with the tablespace |
| Segment | Storage allocated for a database object |
| Extent | Contiguous set of Oracle blocks allocated to a segment |
| Block | Smallest logical unit of database storage |
Types of Tablespaces
Oracle Database commonly uses three main types of tablespaces: Permanent, UNDO, and Temporary.
1. Permanent Tablespace
A Permanent Tablespace stores persistent database objects such as tables, indexes, LOB segments, materialized views, and other permanent segments. Permanent tablespaces use datafiles for physical storage.
CREATE TABLESPACE APP_DATA DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
2. UNDO Tablespace
An UNDO Tablespace stores undo information generated by database transactions. Undo is required for transaction rollback, read consistency, transaction recovery, and other database operations that depend on undo information.
UNDO tablespaces use datafiles and are managed separately from normal permanent tablespaces.
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G;
The currently configured UNDO tablespace can be checked using:
SHOW PARAMETER undo_tablespace;
3. Temporary Tablespace
A Temporary Tablespace provides temporary disk space for operations such as sorting, hash joins, temporary segments, index creation, and other SQL operations that require temporary workspace.
Temporary tablespaces use tempfiles instead of permanent datafiles.
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
| Tablespace Type | Purpose | Physical File |
|---|---|---|
| Permanent | Stores tables, indexes, LOBs and other permanent objects | Datafile |
| UNDO | Stores undo information generated by transactions | Datafile |
| Temporary | Provides temporary workspace for SQL operations | Tempfile |
Smallfile and Bigfile Tablespaces
Tablespaces can also be classified as Smallfile or Bigfile. This classification describes the number of datafiles that can belong to the tablespace.
Smallfile Tablespace
A Smallfile Tablespace can contain multiple datafiles. Additional datafiles can be added when more storage is required.
CREATE TABLESPACE APP_DATA DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Additional datafiles can be added using:
ALTER TABLESPACE APP_DATA ADD DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Bigfile Tablespace
A Bigfile Tablespace contains a single datafile. Instead of adding additional datafiles, the existing datafile is enlarged when additional space is required.
CREATE BIGFILE TABLESPACE APP_BIGDATA DATAFILE '+DATA' SIZE 100G AUTOEXTEND ON NEXT 10G MAXSIZE 1T;
The bigfile can subsequently be resized:
ALTER DATABASE DATAFILE '+DATA/.../app_bigdata.dbf' RESIZE 200G;
| Feature | Smallfile | Bigfile |
|---|---|---|
| Number of datafiles | Multiple datafiles | Single datafile |
| ADD DATAFILE | Supported | Not applicable |
| Storage expansion | Add or resize datafiles | Resize the existing bigfile |
Tablespace and Datafile Size Limits
The maximum size of an Oracle datafile depends on the tablespace type and the database block size. Smallfile and Bigfile tablespaces have different limits.
For a Smallfile tablespace, the maximum number of blocks in an individual datafile is approximately 222 blocks. For a Bigfile tablespace, the limit is approximately 232 blocks. Therefore, the actual maximum size in GB or TB depends on the database block size.
Smallfile Datafile Maximum Size
| Block Size | Approximate Maximum Datafile Size |
|---|---|
| 2 KB | 8 GB |
| 4 KB | 16 GB |
| 8 KB | 32 GB |
| 16 KB | 64 GB |
| 32 KB | 128 GB |
For example, with an 8 KB database block size, an individual Smallfile datafile can theoretically reach approximately 32 GB. If more storage is required, additional datafiles can be added to the Smallfile tablespace, subject to database and platform limits.
Bigfile Datafile Maximum Size
| Block Size | Approximate Maximum Bigfile Size |
|---|---|
| 2 KB | 8 TB |
| 4 KB | 16 TB |
| 8 KB | 32 TB |
| 16 KB | 64 TB |
| 32 KB | 128 TB |
For example, with an 8 KB database block size, a Bigfile tablespace can have a single datafile of up to approximately 32 TB. With a 32 KB block size, the theoretical maximum is approximately 128 TB.
Bigfile Minimum Size
Oracle also defines minimum sizes for Bigfile datafiles. The documented minimum depends on the block size.
| Block Size | Bigfile Minimum |
|---|---|
| 8 KB | Approximately 7 MB |
| 32 KB | Approximately 12 MB |
Check Database Block Size
SHOW PARAMETER db_block_size;
Or:
SELECT name,
value
FROM v$parameter
WHERE name = 'db_block_size';
NAME VALUE -------------- ----- db_block_size 8192
Check Whether a Tablespace is Smallfile or Bigfile
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN bigfile FORMAT A10
COLUMN contents FORMAT A12
SELECT tablespace_name,
bigfile,
block_size,
contents,
status
FROM dba_tablespaces
ORDER BY tablespace_name;
Check Datafile Size and Maximum Size
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN file_name FORMAT A90
COLUMN size_gb FORMAT 999,999,990.99
COLUMN max_gb FORMAT 999,999,990.99
COLUMN autoextensible FORMAT A15
SELECT file_id,
tablespace_name,
file_name,
ROUND(bytes/1024/1024/1024,2) size_gb,
autoextensible,
ROUND(maxbytes/1024/1024/1024,2) max_gb
FROM dba_data_files
ORDER BY tablespace_name,
file_id;
Smallfile vs Bigfile – Quick Comparison
| Feature | Smallfile | Bigfile |
|---|---|---|
| Datafiles per tablespace | Multiple | One |
| Maximum blocks per datafile | ~222 | ~232 |
| 8 KB block maximum | ~32 GB per datafile | ~32 TB |
| 16 KB block maximum | ~64 GB per datafile | ~64 TB |
| 32 KB block maximum | ~128 GB per datafile | ~128 TB |
| ADD DATAFILE | Supported | Not supported |
| Typical expansion | Add or resize datafiles | Resize the single datafile |
Check Tablespace Definition and Properties
SET LINESIZE 250
SET PAGESIZE 1000
SET TRIMSPOOL ON
COLUMN tablespace_name FORMAT A25
COLUMN status FORMAT A12
COLUMN contents FORMAT A12
COLUMN logging FORMAT A10
COLUMN force_logging FORMAT A14
COLUMN extent_management FORMAT A18
COLUMN allocation_type FORMAT A14
COLUMN segment_space_management FORMAT A20
COLUMN bigfile FORMAT A10
COLUMN encrypted FORMAT A10
SELECT tablespace_name,
status,
contents,
logging,
force_logging,
extent_management,
allocation_type,
segment_space_management,
bigfile,
encrypted
FROM dba_tablespaces
ORDER BY tablespace_name;
Check Default Permanent and TEMP Tablespace
SELECT property_name,
property_value
FROM database_properties
WHERE property_name IN
('DEFAULT_PERMANENT_TABLESPACE',
'DEFAULT_TEMP_TABLESPACE');
Tablespace Space Utilization
This is the primary query for checking the currently allocated, used and free space of permanent tablespaces.
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN tablespace FORMAT A30
COLUMN total_mb FORMAT 999999999
COLUMN used_mb FORMAT 999999999
COLUMN free_mb FORMAT 999999999
COLUMN used_pct FORMAT 990.99
COLUMN free_pct FORMAT 990.99
SELECT t.tablespace_name tablespace,
ROUND(t.totalspace) total_mb,
ROUND(t.totalspace-NVL(fs.freespace,0),2) used_mb,
NVL(fs.freespace,0) free_mb,
ROUND(
(t.totalspace-NVL(fs.freespace,0))
/t.totalspace*100,2
) used_pct,
ROUND(
NVL(fs.freespace,0)
/t.totalspace*100,2
) free_pct
FROM
(
SELECT SUM(bytes)/1024/1024 totalspace,
tablespace_name
FROM dba_data_files
GROUP BY tablespace_name
) t
LEFT JOIN
(
SELECT SUM(bytes)/1024/1024 freespace,
tablespace_name
FROM dba_free_space
GROUP BY tablespace_name
) fs
ON t.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;
Tablespace MAXSIZE Utilization
A tablespace may currently have low utilization but still have a large potential storage requirement because its datafiles are configured with AUTOEXTEND.
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN auto_ext FORMAT A10
COLUMN current_gb FORMAT 999,999,990.99
COLUMN used_gb FORMAT 999,999,990.99
COLUMN free_gb FORMAT 999,999,990.99
COLUMN used_pct FORMAT 990.99
COLUMN max_gb FORMAT 999,999,990.99
COLUMN max_used_pct FORMAT 990.99
COLUMN expandable_gb FORMAT 999,999,990.99
SELECT d.tablespace_name,
d.auto_ext,
ROUND(d.current_bytes/1024/1024/1024,2) current_gb,
ROUND(
(d.current_bytes-NVL(f.free_bytes,0))
/1024/1024/1024,2
) used_gb,
ROUND(
NVL(f.free_bytes,0)
/1024/1024/1024,2
) free_gb,
ROUND(
(d.current_bytes-NVL(f.free_bytes,0))
/d.current_bytes*100,2
) used_pct,
ROUND(d.max_bytes/1024/1024/1024,2) max_gb,
ROUND(
(d.current_bytes-NVL(f.free_bytes,0))
/d.max_bytes*100,2
) max_used_pct,
ROUND(
(d.max_bytes-d.current_bytes)
/1024/1024/1024,2
) expandable_gb
FROM
(
SELECT tablespace_name,
SUM(bytes) current_bytes,
SUM(
CASE
WHEN maxbytes = 0 THEN bytes
ELSE maxbytes
END
) max_bytes,
MAX(autoextensible) auto_ext
FROM dba_data_files
GROUP BY tablespace_name
) d
LEFT JOIN
(
SELECT tablespace_name,
SUM(bytes) free_bytes
FROM dba_free_space
GROUP BY tablespace_name
) f
ON d.tablespace_name = f.tablespace_name
ORDER BY max_used_pct DESC;
Datafile Details
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN file_name FORMAT A90
COLUMN size_gb FORMAT 999,999,990.99
COLUMN autoextensible FORMAT A15
COLUMN next_mb FORMAT 9999990.99
COLUMN max_gb FORMAT 999,999,990.99
COLUMN status FORMAT A15
SELECT d.file_id,
d.tablespace_name,
d.file_name,
ROUND(d.bytes/1024/1024/1024,2) size_gb,
d.autoextensible,
ROUND(
d.increment_by*t.block_size/1024/1024,
2
) next_mb,
ROUND(d.maxbytes/1024/1024/1024,2) max_gb,
d.status
FROM dba_data_files d
JOIN dba_tablespaces t
ON d.tablespace_name = t.tablespace_name
ORDER BY d.tablespace_name,
d.file_id;
Create Tablespace – Filesystem
CREATE TABLESPACE APP_DATA DATAFILE '/u01/app/oracle/oradata/PROD/APP_DATA.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
Create Tablespace – ASM with OMF
CREATE TABLESPACE APP_DATA1 DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
When Oracle Managed Files are being used, Oracle manages the physical ASM filename.
Create Tablespace – ASM Without OMF
CREATE TABLESPACE APP_DATA2 DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
Add Datafile
For a Smallfile tablespace, additional datafiles can be added when more storage is required.
ALTER TABLESPACE APP_DATA ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
Resize Datafile
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf' RESIZE 2G;
AUTOEXTEND ON
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
AUTOEXTEND OFF
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf' AUTOEXTEND OFF;
Change AUTOEXTEND NEXT and MAXSIZE
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf' AUTOEXTEND ON NEXT 2G MAXSIZE 31G;
Drop Datafile
A permanent datafile should only be dropped after confirming that it does not contain required permanent segments.
Check Objects in a Datafile
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN owner FORMAT A25
COLUMN segment_name FORMAT A40
COLUMN segment_type FORMAT A25
COLUMN size_mb FORMAT 999,999,990.99
SELECT owner,
segment_name,
segment_type,
file_id,
ROUND(SUM(bytes)/1024/1024,2) size_mb
FROM dba_extents
WHERE file_id = 16
GROUP BY owner,
segment_name,
segment_type,
file_id
ORDER BY size_mb DESC;
Drop Datafile
ALTER TABLESPACE APP_DATA DROP DATAFILE 16;
A datafile can also be specified by filename:
ALTER TABLESPACE APP_DATA DROP DATAFILE '+DATA/PROD/PRODPDB/APP_DATA2.dbf';
Move Datafile
Datafile relocation is commonly required when changing storage, migrating filesystem datafiles to ASM, reorganizing storage, or moving database files between storage locations.
Read: How to Relocate Oracle Database Datafiles →
Rename Datafile
Renaming a datafile changes the physical filename recorded by Oracle. This is different from renaming the tablespace.
ALTER DATABASE RENAME FILE '/old/path/app_data01.dbf' TO '/new/path/app_data01.dbf';
Tablespace ONLINE / OFFLINE
Check Status
SELECT tablespace_name,
status,
contents
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';
Take Tablespace Offline
ALTER TABLESPACE APP_DATA OFFLINE NORMAL;
Immediate Offline
ALTER TABLESPACE APP_DATA OFFLINE IMMEDIATE;
Bring Tablespace Online
ALTER TABLESPACE APP_DATA ONLINE;
READ ONLY / READ WRITE
Make Tablespace READ ONLY
ALTER TABLESPACE APP_DATA READ ONLY;
Verify
SELECT tablespace_name,
status
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA';
Return to READ WRITE
ALTER TABLESPACE APP_DATA READ WRITE;
| State | SELECT | INSERT / UPDATE / DELETE |
|---|---|---|
| ONLINE / READ WRITE | Yes | Yes |
| READ ONLY | Yes | No |
| OFFLINE | No | No |
Rename Tablespace
Renaming a tablespace changes its logical name. Oracle updates the corresponding database metadata automatically.
ALTER TABLESPACE APP_DATA RENAME TO APP_DATA_NEW;
Verify
SELECT tablespace_name,
status,
contents
FROM dba_tablespaces
WHERE tablespace_name = 'APP_DATA_NEW';
The physical datafile filename does not automatically change.
SELECT file_id,
tablespace_name,
file_name
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA_NEW';
TEMP Tablespace Management
TEMP tablespaces provide temporary disk space for SQL operations such as sorts, hash joins, temporary segments and other operations requiring temporary workspace.
Check TEMPFILES
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN file_name FORMAT A90
COLUMN size_gb FORMAT 999,999,990.99
COLUMN autoextensible FORMAT A15
COLUMN max_gb FORMAT 999,999,990.99
SELECT file_id,
tablespace_name,
file_name,
ROUND(bytes/1024/1024/1024,2) size_gb,
autoextensible,
ROUND(maxbytes/1024/1024/1024,2) max_gb,
status
FROM dba_temp_files
ORDER BY tablespace_name,
file_id;
Create TEMP Tablespace – Filesystem
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/oradata/PROD/temp02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Create TEMP Tablespace – ASM
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Add TEMPFILE
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Resize TEMPFILE
ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/temp01.dbf' RESIZE 20G;
Enable TEMPFILE AUTOEXTEND
ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/temp01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 100G;
Disable TEMPFILE AUTOEXTEND
ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/temp01.dbf' AUTOEXTEND OFF;
Drop TEMPFILE
ALTER DATABASE TEMPFILE '+DATA/PROD/TEMPFILE/temp02.dbf' DROP;
Drop TEMP Tablespace
DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;
DROP DATAFILE vs DROP TEMPFILE
| Feature | DROP DATAFILE | DROP TEMPFILE |
|---|---|---|
| Used for | Permanent tablespaces | Temporary tablespaces |
| Contains permanent objects | Yes | No |
| Command | ALTER TABLESPACE ... DROP DATAFILE | ALTER DATABASE TEMPFILE ... DROP |
| Object check | Required | Permanent segments are not stored there |
TEMP Tablespace Usage
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN tablespace_name FORMAT A25
COLUMN total_gb FORMAT 999,999,990.99
COLUMN used_gb FORMAT 999,999,990.99
COLUMN free_gb FORMAT 999,999,990.99
COLUMN used_pct FORMAT 990.99
SELECT tablespace_name,
ROUND(
SUM(bytes_used)/1024/1024/1024,2
) used_gb,
ROUND(
SUM(bytes_free)/1024/1024/1024,2
) free_gb,
ROUND(
(SUM(bytes_used)+SUM(bytes_free))
/1024/1024/1024,2
) total_gb,
ROUND(
SUM(bytes_used) /
NULLIF(
SUM(bytes_used)+SUM(bytes_free),0
)*100,2
) used_pct
FROM v$temp_space_header
GROUP BY tablespace_name
ORDER BY used_pct DESC;
Find Sessions Consuming TEMP
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN sql_id FORMAT A15
COLUMN tablespace FORMAT A20
COLUMN temp_mb FORMAT 999,999,990.99
SELECT s.username,
s.sid,
s.serial#,
s.sql_id,
u.tablespace,
u.blocks,
ROUND(
u.blocks*p.value/1024/1024,2
) temp_mb
FROM v$session s
JOIN v$tempseg_usage u
ON s.saddr = u.session_addr
CROSS JOIN
(
SELECT value
FROM v$parameter
WHERE name='db_block_size'
) p
ORDER BY u.blocks DESC;
Change Default TEMP Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Verify
SELECT property_name,
property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
UNDO Tablespace Management
UNDO tablespaces store undo information generated by transactions and are managed separately from permanent tablespaces.
Check Current UNDO Configuration
SHOW PARAMETER undo;
Check UNDO Tablespaces
SELECT tablespace_name,
status,
contents,
bigfile
FROM dba_tablespaces
WHERE contents = 'UNDO';
Check UNDO Datafiles on ASM
SET LINESIZE 250
COLUMN tablespace_name FORMAT A20
COLUMN file_name FORMAT A90
COLUMN size_gb FORMAT 999,990.99
COLUMN max_gb FORMAT 999,990.99
COLUMN autoextensible FORMAT A15
SELECT file_id,
tablespace_name,
file_name,
ROUND(bytes/1024/1024/1024,2) size_gb,
autoextensible,
ROUND(maxbytes/1024/1024/1024,2) max_gb,
status
FROM dba_data_files
WHERE tablespace_name IN
(SELECT tablespace_name
FROM dba_tablespaces
WHERE contents = 'UNDO')
ORDER BY tablespace_name,
file_id;
Create UNDO Tablespace on ASM
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G;
Add Datafile to Smallfile UNDO
ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G;
Resize UNDO Datafile
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/undotbs02.dbf' RESIZE 2G;
Enable AUTOEXTEND
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/undotbs02.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 31G;
Disable AUTOEXTEND
ALTER DATABASE DATAFILE '+DATA/PROD/PRODPDB/undotbs02.dbf' AUTOEXTEND OFF;
Switch to New UNDO Tablespace
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
Verify
SHOW PARAMETER undo_tablespace;
Check UNDO Extent Status
SELECT tablespace_name,
status,
COUNT(*) extents,
ROUND(SUM(bytes)/1024/1024/1024,2) size_gb
FROM dba_undo_extents
GROUP BY tablespace_name,
status
ORDER BY tablespace_name,
status;
Drop Old UNDO Tablespace
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Tablespace Quota Management
Check Quotas
SET LINESIZE 200
COLUMN username FORMAT A25
COLUMN tablespace_name FORMAT A25
SELECT username,
tablespace_name,
ROUND(bytes/1024/1024/1024,2) used_gb,
CASE
WHEN max_bytes = -1
THEN 'UNLIMITED'
ELSE TO_CHAR(
ROUND(max_bytes/1024/1024/1024,2),
'999,999,990.99'
)
END max_quota
FROM dba_ts_quotas
ORDER BY username,
tablespace_name;
Assign Quota
ALTER USER TESTUSER QUOTA 5G ON USERS;
Unlimited Quota
ALTER USER TESTUSER QUOTA UNLIMITED ON USERS;
Remove Quota
ALTER USER TESTUSER QUOTA 0 ON USERS;
User Default Tablespace Management
Check User Defaults
SELECT username,
default_tablespace,
temporary_tablespace
FROM dba_users
ORDER BY username;
Change Default Permanent Tablespace
ALTER USER TESTUSER DEFAULT TABLESPACE APP_DATA;
Change Temporary Tablespace
ALTER USER TESTUSER TEMPORARY TABLESPACE TEMP2;
ASM Capacity Check
Before increasing datafiles or creating new tablespaces on ASM, check the available space in the relevant ASM diskgroup.
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN name FORMAT A20
COLUMN total_gb FORMAT 999,999,990.99
COLUMN free_gb FORMAT 999,999,990.99
COLUMN used_gb FORMAT 999,999,990.99
COLUMN free_pct FORMAT 990.99
SELECT name,
ROUND(total_mb/1024,2) total_gb,
ROUND(free_mb/1024,2) free_gb,
ROUND(
(total_mb-free_mb)/1024,2
) used_gb,
ROUND(
free_mb/total_mb*100,2
) free_pct
FROM v$asm_diskgroup
ORDER BY free_pct;
Free Extent Analysis
Free extent information can be useful when investigating space allocation issues and understanding the distribution of free space inside a tablespace.
SELECT tablespace_name,
COUNT(*) free_extents,
ROUND(SUM(bytes)/1024/1024/1024,2) free_gb,
ROUND(MAX(bytes)/1024/1024,2) largest_free_mb,
ROUND(AVG(bytes)/1024/1024,2) avg_free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_gb DESC;
Object Space by Tablespace
SET LINESIZE 200
COLUMN tablespace_name FORMAT A25
COLUMN owner FORMAT A25
COLUMN segment_count FORMAT 999999
COLUMN size_gb FORMAT 999,999,990.99
SELECT tablespace_name,
owner,
COUNT(*) segment_count,
ROUND(
SUM(bytes)/1024/1024/1024,2
) size_gb
FROM dba_segments
GROUP BY tablespace_name,
owner
ORDER BY tablespace_name,
size_gb DESC;
Drop Tablespace
When a tablespace is no longer required, it can be completely removed from the database.
Drop Empty Tablespace
DROP TABLESPACE APP_DATA;
Drop Tablespace Including Contents
DROP TABLESPACE APP_DATA INCLUDING CONTENTS;
Drop Tablespace Including Contents and Datafiles
DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES;
AND DATAFILES, the associated physical files. Always validate
dependencies and backups before performing this operation in production.
Common Tablespace Errors
| Error | Typical Meaning | Troubleshooting / Solution |
|---|---|---|
| ORA-01653 | Unable to extend a table in the tablespace. |
Check tablespace free space, datafile AUTOEXTEND and MAXSIZE.
If sufficient storage is available, resize the existing datafile or
add a new datafile. Also verify underlying ASM/filesystem capacity.
Typical solution:
ALTER TABLESPACE APP_DATA
ADD DATAFILE '+DATA'
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 100G;
|
| ORA-01654 | Unable to extend an index in the tablespace. | Check available tablespace space and the index's growth requirement. Increase tablespace capacity by resizing an existing datafile or adding a datafile. Also check whether the index is growing rapidly. Typical solution: Add or resize a datafile after checking ASM/filesystem capacity. |
| ORA-01652 | Unable to extend TEMP segment. |
Check TEMP utilization and identify sessions consuming TEMP.
If TEMP is genuinely exhausted, add or resize a tempfile.
Investigate the SQL consuming excessive TEMP if the usage is abnormal.
Typical solution:
ALTER TABLESPACE TEMP
ADD TEMPFILE '+DATA'
SIZE 10G
AUTOEXTEND ON
NEXT 1G
MAXSIZE 100G;
|
| ORA-03297 | Used data exists beyond the requested RESIZE value. | The datafile cannot be reduced to the requested size because allocated extents exist beyond the target size. Identify objects near the end of the datafile using DBA_EXTENTS, move or reorganize the affected objects if appropriate, and then retry the resize. Typical solution: Perform HWM analysis before reducing the datafile. |
| ORA-01536 | User quota exceeded on the tablespace. |
Check the user's quota using DBA_TS_QUOTAS. If the user requires
additional space, increase the quota or assign unlimited quota
according to the application's requirements.
Typical solution:
ALTER USER TESTUSER
QUOTA 10G ON APP_DATA;
Or:
ALTER USER TESTUSER
QUOTA UNLIMITED ON APP_DATA;
|
| ORA-01113 | Datafile needs media recovery. |
Identify the affected datafile and determine why it requires recovery.
Check the alert log and database recovery requirements. Perform the
appropriate media recovery using RMAN or SQL*Plus before attempting
to bring the datafile or tablespace online.
Typical approach:
RECOVER DATAFILE <file_id>;
|
| ORA-01110 | Identifies the datafile associated with another database error. | ORA-01110 normally accompanies another error and identifies the affected datafile. Use the file number and filename shown in the message to investigate the actual underlying error. Typical approach: Check the complete error stack and verify the datafile status, location and accessibility. |
| ORA-01565 | Error identifying a datafile. |
Verify that the specified datafile exists and that the filename or
path is correct. Check DBA_DATA_FILES, V$DATAFILE and the underlying
filesystem or ASM storage.
Typical approach:
SELECT file#,
name,
status
FROM v$datafile;
|
| ORA-03206 | Specified AUTOEXTEND maximum file size is outside the allowed range. |
The requested MAXSIZE exceeds the maximum size allowed for the
datafile/tablespace configuration. Check the database block size,
whether the tablespace is Smallfile or Bigfile, and Oracle's
datafile size limits. Reduce MAXSIZE or use an appropriate
tablespace/datafile configuration.
Check:
SHOW PARAMETER db_block_size;
SELECT tablespace_name,
bigfile,
block_size
FROM dba_tablespaces;
|
| ORA-03214 | File size is smaller than the minimum allowed for the operation. | The requested datafile size is below the minimum size permitted by Oracle for the operation. Verify the current file size, database block size and tablespace configuration. Increase the requested SIZE or RESIZE value to an acceptable size. Typical approach: Check DBA_DATA_FILES and DBA_TABLESPACES before retrying the operation. |
| ORA-01658 | Unable to create INITIAL extent for segment in tablespace. | Check whether the tablespace has enough free space and whether a sufficiently large extent can be allocated. Increase tablespace capacity or review the object's storage requirements. Typical solution: Add or resize a datafile after checking available storage. |
| ORA-30036 | Unable to extend segment in UNDO tablespace. | Check UNDO tablespace utilization, datafile AUTOEXTEND/MAXSIZE and available ASM/filesystem capacity. Investigate long-running transactions and unusually high undo generation. Typical solution: Increase UNDO capacity or add/resize an UNDO datafile. |
Important DBA Checks Before Increasing Space
- Check current tablespace utilization.
- Check datafile AUTOEXTEND configuration.
- Check datafile MAXSIZE.
- Check ASM or filesystem capacity.
- Check which objects are consuming space.
- Check user quota if the issue affects a specific schema.
- Check whether an existing datafile can be resized.
- For TEMP issues, identify sessions consuming TEMP.
- For UNDO issues, check UNDO usage and long-running transactions.
- For datafile reduction, perform HWM analysis first.
Conclusion
Tablespace management is one of the fundamental responsibilities of an Oracle DBA. Effective management requires more than simply checking the percentage of free space. A DBA should understand the relationship between tablespaces, datafiles, tempfiles, AUTOEXTEND, MAXSIZE, ASM capacity, object growth, quotas, TEMP, UNDO and database storage.
The most important routine checks are current space utilization, MAXSIZE utilization, datafile configuration, TEMP usage, UNDO usage, and underlying ASM or filesystem capacity.
For operational changes, Oracle provides commands to create tablespaces, add and resize datafiles, configure AUTOEXTEND, drop unnecessary datafiles, change tablespace states, make tablespaces READ ONLY or READ WRITE, rename tablespaces, manage TEMP and UNDO storage, configure user quotas, and remove unused tablespaces.
