This article explains how to correctly verify the usage and size of tablespaces in Oracle and how to expand capacity when necessary, using Data Dictionary views (DBA_) and dynamic performance views (V$). We first clarify what a tablespace is, then present the shortest day-to-day workflow (target: Linux / 19c non-CDB).
[reference]
- Conclusion & Fast Path (for snippets)
- What is a tablespace? (background & basics)
- Prerequisites & creating a test set (minimal, reproducible)
- Procedure & implementation
- Text diagram: operations flow (alt: Oracle tablespace usage check → expansion flow)
- Example run (excerpt: visualizing permanent usage trend)
- Troubleshooting (reading common errors)
- Operations, monitoring, and security notes
- FAQ
- Summary (key points)
Conclusion & Fast Path (for snippets)
- Get the whole picture
UseDBA_TABLESPACES(status/type/BIGFILE) to identify the target. - Check file-level sizes
Permanent:DBA_DATA_FILES, TEMP:DBA_TEMP_FILES. - Check tablespace usage
Permanent:DBA_TABLESPACE_USAGE_METRICSor an aggregation ofDBA_FREE_SPACE.
TEMP: aggregateV$TEMP_SPACE_HEADER. - Check real-time state
V$TABLESPACE/V$DATAFILE(ONLINE/OFFLINE, read-only, etc.). - When short on space
Choose amongALTER TABLESPACE ... ADD DATAFILE/AUTOEXTEND ON/RESIZE. - Operations baseline
Verify with read-only queries → change → re-verify / check alert log.
What is a tablespace? (background & basics)
- Tablespace: a logical container that stores segments such as tables and indexes. Its physical reality is one or more data files (.dbf).
- PERMANENT / TEMPORARY: permanent data vs. sort/temporary workspace.
- BIGFILE / SMALLFILE: different thinking about file count and maximums (BIGFILE uses one very large file).
- Usage basics:
- Permanent: total datafile size − free (
DBA_FREE_SPACE) - TEMP: currently used temp space (
V$TEMP_SPACE_HEADER)
- Permanent: total datafile size − free (
Tip:
DBA_TABLESPACE_USAGE_METRICSlets you grasp permanent usage at a glance, great for daily reports. TEMP is checked with a separate query.
Prerequisites & creating a test set (minimal, reproducible)
- OS: Oracle Linux (example)
- DB: Oracle Database 19c (non-CDB)
- Privilege:
SYSDBA(to query DBA_* views) - Path: this article uses
/u01/app/oracle/oradata/ORCL
Goal: create a small test tablespace, user, and table so you can visualize changes in usage and size with the queries below.
-- 小さな検証用表領域（サイズ 50MB、必要に応じて自動拡張）
CREATE TABLESPACE TBS_APP
DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_app01.dbf' SIZE 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Explanation: CREATE TABLESPACE creates a new tablespace. SIZE is the initial size; AUTOEXTEND ON enables automatic expansion when full; MAXSIZE caps autoextend growth.
-- アプリ用ユーザー作成（デフォルト表領域を TBS_APP に）
CREATE USER APPUSER IDENTIFIED BY "Passw0rd1"
DEFAULT TABLESPACE TBS_APP
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON TBS_APP;
GRANT CREATE SESSION, CREATE TABLE TO APPUSER;
Explanation: CREATE USER creates the application user and sets TBS_APP as the default tablespace. GRANT provides the minimal privileges for login and table creation.
-- 検証用テーブル作成＋データ投入（使用率の変化を確認しやすく）
CONN APPUSER/"Passw0rd1"
CREATE TABLE T1 (id NUMBER, pad CHAR(1000));
INSERT INTO T1 SELECT LEVEL, RPAD('x',1000,'x') FROM dual CONNECT BY LEVEL <= 5000;
COMMIT;
Explanation: CREATE TABLE creates a test table; INSERT ... CONNECT BY loads 5,000 rows at once to raise tablespace usage on purpose. COMMIT makes it durable.
Procedure & implementation
1) Overview: identify target tablespaces to verify
-- 表領域の状態・種別・ブロックサイズ・BIGFILE かを一覧
SELECT tablespace_name, status, contents, bigfile, block_size
FROM dba_tablespaces
ORDER BY tablespace_name;
Explanation: List the core attributes of all tablespaces. Use status (ONLINE/OFFLINE), contents (PERMANENT/TEMPORARY), bigfile, and block_size to verify what you monitor.
Command execution example
SQL> SELECT tablespace_name, status, contents, bigfile, block_size
2 FROM dba_tablespaces
3 ORDER BY tablespace_name;
TABLESPACE_NAME STATUS CONTENTS BIG BLOCK_SIZE
------------------------------ --------- --------------------- --- ----------
SYSAUX ONLINE PERMANENT NO 8192
SYSTEM ONLINE PERMANENT NO 8192
TBS_APP ONLINE PERMANENT NO 8192
TEMP ONLINE TEMPORARY NO 8192
UNDOTBS1 ONLINE UNDO NO 8192
USERS ONLINE PERMANENT NO 8192
2) Check file-level size / auto-extend settings
-- PERMANENT のデータファイル（現在サイズ・自動拡張の有無・上限）
SELECT tablespace_name, file_name,
bytes/1024/1024 AS size_mb,
autoextensible,
maxbytes/1024/1024 AS max_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;
Explanation: For permanent datafiles, check current size (MB), AUTOEXTENSIBLE, and maximum MAXBYTES per file to estimate expansion headroom.
Command execution example
SQL> col tablespace_name for a20
SQL> col file_name for a50
SQL> SELECT tablespace_name, file_name,
2 bytes/1024/1024 AS size_mb,
3 autoextensible,
4 maxbytes/1024/1024 AS max_mb
5 FROM dba_data_files
6 ORDER BY tablespace_name, file_name;
TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAX_MB
-------------------- -------------------------------------------------- ---------- --- ----------
SYSAUX /u01/app/oracle/oradata/ORCL/sysaux01.dbf 510 YES 32767.9844
SYSTEM /u01/app/oracle/oradata/ORCL/system01.dbf 900 YES 32767.9844
TBS_APP /u01/app/oracle/oradata/ORCL/tbs_app01.dbf 50 YES 500
UNDOTBS1 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 345 YES 32767.9844
USERS /u01/app/oracle/oradata/ORCL/users01.dbf 5 YES 32767.9844
-- TEMP のファイル（TEMPFILE）
SELECT tablespace_name, file_name,
bytes/1024/1024 AS size_mb,
autoextensible,
maxbytes/1024/1024 AS max_mb
FROM dba_temp_files
ORDER BY tablespace_name, file_name;
Explanation: For tempfiles, check current size and auto-extend settings. TEMP usage grows with sorts and hash joins, so keep an eye on the maximum as well.
Note: Even with
AUTOEXTENSIBLE=YES, immediately usable capacity is limited to the current size. Always consider theMAXSIZEalongside it.
Command execution example
SQL> SELECT tablespace_name, file_name,
2 bytes/1024/1024 AS size_mb,
3 autoextensible,
4 maxbytes/1024/1024 AS max_mb
5 FROM dba_temp_files
6 ORDER BY tablespace_name, file_name;
TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAX_MB
-------------------- -------------------------------------------------- ---------- --- ----------
TEMP /u01/app/oracle/oradata/ORCL/temp01.dbf 32 YES 32767.9844
3) Verify permanent tablespace usage (recommended)
-- 使用率を一括表示（PERMANENT）。block_size を考慮して MB に換算
SELECT m.tablespace_name,
t.block_size,
ROUND(m.tablespace_size * t.block_size / 1024/1024) AS size_mb,
ROUND(m.used_space * t.block_size / 1024/1024) AS used_mb,
ROUND( (m.used_space/m.tablespace_size) * 100, 1 ) AS used_pct
FROM dba_tablespace_usage_metrics m
JOIN dba_tablespaces t
ON m.tablespace_name = t.tablespace_name
ORDER BY used_pct DESC;
Explanation: Convert the block counts in DBA_TABLESPACE_USAGE_METRICS using DBA_TABLESPACES.BLOCK_SIZE and verify total, used, and usage percentage per tablespace.
Command execution example
SQL> SELECT m.tablespace_name,
2 t.block_size,
3 ROUND(m.tablespace_size * t.block_size / 1024/1024) AS size_mb,
4 ROUND(m.used_space * t.block_size / 1024/1024) AS used_mb,
5 ROUND( (m.used_space/m.tablespace_size) * 100, 1 ) AS used_pct
6 FROM dba_tablespace_usage_metrics m
7 JOIN dba_tablespaces t
8 ON m.tablespace_name = t.tablespace_name
9 ORDER BY used_pct DESC;
TABLESPACE_NAME BLOCK_SIZE SIZE_MB USED_MB USED_PCT
-------------------- ---------- ---------- ---------- ----------
SYSTEM 8192 32768 892 2.7
SYSAUX 8192 32768 507 1.5
TBS_APP 8192 500 7 1.4
USERS 8192 32417 3 0
TEMP 8192 32461 0 0
-- 代替：FREE_SPACE 集計で使用率を算出（USING では修飾子を外す）
WITH df AS (
SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files GROUP BY tablespace_name
), fs AS (
SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space GROUP BY tablespace_name
)
SELECT tablespace_name, -- ★USING を使うため未修飾で出力
df.total_mb,
NVL(fs.free_mb,0) AS free_mb,
(df.total_mb - NVL(fs.free_mb,0)) AS used_mb,
ROUND( (df.total_mb - NVL(fs.free_mb,0)) / NULLIF(df.total_mb,0) * 100, 1 ) AS used_pct
FROM df
LEFT JOIN fs USING (tablespace_name)
ORDER BY used_pct DESC;
Explanation: Compute usage by subtracting DBA_FREE_SPACE from the total in DBA_DATA_FILES. Because this uses a USING clause, reference tablespace_name unqualified in SELECT.
Command execution example
SQL> WITH df AS (
2 SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
3 FROM dba_data_files GROUP BY tablespace_name
4 ), fs AS (
5 SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
6 FROM dba_free_space GROUP BY tablespace_name
7 )
8 SELECT tablespace_name,
9 df.total_mb,
10 NVL(fs.free_mb,0) AS free_mb,
11 (df.total_mb - NVL(fs.free_mb,0)) AS used_mb,
12 ROUND( (df.total_mb - NVL(fs.free_mb,0)) / NULLIF(df.total_mb,0) * 100, 1 ) AS used_pct
13 FROM df
14 LEFT JOIN fs USING (tablespace_name)
15 ORDER BY used_pct DESC;
TABLESPACE_NAME TOTAL_MB FREE_MB USED_MB USED_PCT
-------------------- ---------- ---------- ---------- ----------
UNDOTBS1 345 2 343 99.4
SYSTEM 900 8.375 891.625 99.1
SYSAUX 540 29.5625 510.4375 94.5
USERS 5 2.3125 2.6875 53.8
TBS_APP 50 43 7 14
4) Verify TEMP usage in real time
SELECT tablespace_name,
ROUND(SUM(bytes_used)/1024/1024) AS used_mb,
ROUND(SUM(bytes_free)/1024/1024) AS free_mb,
ROUND(SUM(bytes_used)/NULLIF(SUM(bytes_used)+SUM(bytes_free),0) * 100, 1) AS used_pct
FROM v$temp_space_header
GROUP BY tablespace_name
ORDER BY used_pct DESC;
Explanation: Aggregate V$TEMP_SPACE_HEADER to get current TEMP usage. For per-session details, use V$TEMPSEG_USAGE.
Command execution example
SQL> SELECT tablespace_name,
2 ROUND(SUM(bytes_used)/1024/1024) AS used_mb,
3 ROUND(SUM(bytes_free)/1024/1024) AS free_mb,
4 ROUND(SUM(bytes_used)/NULLIF(SUM(bytes_used)+SUM(bytes_free),0) * 100, 1) AS used_pct
5 FROM v$temp_space_header
6 GROUP BY tablespace_name
7 ORDER BY used_pct DESC;
TABLESPACE_NAME USED_MB FREE_MB USED_PCT
-------------------- ---------- ---------- ----------
TEMP 32 0 100
5) Verify real-time states (ONLINE/OFFLINE, read-only)
-- 表領域 ONLINE/OFFLINE など
SELECT tablespace_name, status, contents
FROM dba_tablespaces
ORDER BY tablespace_name;
Explanation: Quickly verify tablespace states via DBA_TABLESPACES (status / contents). Useful during switchover or incident checks.
Command execution example
SQL> SELECT tablespace_name, status, contents
2 FROM dba_tablespaces
3 ORDER BY tablespace_name;
TABLESPACE_NAME STATUS CONTENTS
-------------------- --------- ---------------------
SYSAUX ONLINE PERMANENT
SYSTEM ONLINE PERMANENT
TBS_APP ONLINE PERMANENT
TEMP ONLINE TEMPORARY
UNDOTBS1 ONLINE UNDO
USERS ONLINE PERMANENT
6 rows selected.
-- データファイルのオンライン状態（有効/無効など）を確認
SELECT file#, status, enabled
FROM v$datafile
ORDER BY file#;
Explanation: With V$DATAFILE, check status and enabled to see whether each datafile is read-write, read-only, or disabled.
Command execution example
SQL> SELECT file#, status, enabled
2 FROM v$datafile
3 ORDER BY file#;
FILE# STATUS ENABLED
---------- ------- ----------
1 SYSTEM READ WRITE
3 ONLINE READ WRITE
4 ONLINE READ WRITE
5 ONLINE READ WRITE
7 ONLINE READ WRITE
6) Expand when capacity is insufficient (safe steps)
Always verify first with read-only queries → change → re-verify results. Establish evidence with “read-only queries” before any changes.
-- 追加でファイルを作成して拡張（推奨。リスクが低い）
ALTER TABLESPACE TBS_APP ADD DATAFILE
'/u01/app/oracle/oradata/ORCL/tbs_app02.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 2G;
Explanation: ADD DATAFILE expands a tablespace by adding a new file. It leaves existing files untouched and tends to avoid fragmentation—generally the safest option.
-- 既存ファイルを RESIZE（OS 空きと連動。拡張できない場合あり）
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_app01.dbf' RESIZE 100M;
Explanation: Directly change a file’s size. This can fail (ORA-03206/03217) due to OS free space or segment placement constraints; use with care.
-- 自動拡張を有効化/設定変更
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/tbs_app01.dbf'
AUTOEXTEND ON NEXT 20M MAXSIZE 2G;
Explanation: Enable auto-extend on the datafile. NEXT is the increment; MAXSIZE prevents unbounded growth.
Rollback: an added file can be
DROP DATAFILEonly when empty (with constraints). Shrinking too aggressively can hit ORA-03206/03217, so avoid shrinking unless necessary.
Text diagram: operations flow (alt: Oracle tablespace usage check → expansion flow)
┌──────────────┐ ┌────────────────┐ ┌─────────────────────┐
│ Check (read) │ → │ Identify cause │ → │ Choose expansion │
│ • usage/size │ │ • TEMP or perm │ │ ADD/RESIZE/AUTOEXTEND│
│ • ONLINE │ │ • surge cause │ │ rollback policy │
└─────┬────────┘ └───────┬─────────┘ └───────────┬─────────┘
│ (OK) │ (execute)
▼ ▼
Re-check results (queries / alert log / monitoring)
Example run (excerpt: visualizing permanent usage trend)
-- 拡張前
SELECT tablespace_name, used_pct
FROM (
SELECT m.tablespace_name,
ROUND((m.used_space/m.tablespace_size)*100,1) AS used_pct
FROM dba_tablespace_usage_metrics m
) WHERE tablespace_name = 'TBS_APP';
Explanation: Extract the usage for the target tablespace (TBS_APP) and keep it as a snapshot before expansion.
-- データ増加 → 再確認
INSERT INTO APPUSER.T1 SELECT LEVEL+5000, RPAD('y',1000,'y')
FROM dual CONNECT BY LEVEL <= 5000;
COMMIT;
Explanation: Insert another 5,000 rows to purposefully raise usage, so the before/after difference becomes visible. COMMIT persists it.
-- 拡張後に再確認
ALTER TABLESPACE TBS_APP ADD DATAFILE
'/u01/app/oracle/oradata/ORCL/tbs_app03.dbf' SIZE 100M;
Explanation: Perform expansion with ADD DATAFILE, then re-run the previous verification query to confirm that usage decreased (more free space).
Intent: Using the same query for before/after comparison makes scope and effect explicit.
Troubleshooting (reading common errors)
| Symptom / Error | Main cause | First checks (read-only) | Resolution order (read → change) |
|---|---|---|---|
| ORA-01653 (cannot extend) | Tablespace out of space | Permanent usage, maximum size, AUTOEXTENSIBLE | 1) Remove unused segments → 2) ADD DATAFILE → 3) AUTOEXTEND ON |
| ORA-01652 (TEMP shortage) | Not enough TEMP | Usage from V$TEMP_SPACE_HEADER | 1) Tune SQL / review PGA_AGGREGATE_TARGET → 2) Add TEMPFILE |
| ORA-01659 (create failed) | Free space fragmentation in target | Max extent from DBA_FREE_SPACE | 1) Confirm fragmentation → 2) Add file or use RESIZE to secure contiguous space |
| ORA-03206/03217 (cannot shrink) | Segment placement prevents shrinking | Tail usage in DBA_EXTENTS | Shrinking is a last resort. Consider MOVE/SHRINK then retry. |
Always verify with read-only queries first, then apply the minimal expansion needed.
Operations, monitoring, and security notes
- Monitoring: regularly verify
DBA_TABLESPACE_USAGE_METRICS(permanent) andV$TEMP_SPACE_HEADER(TEMP). Set thresholds and alerts. - Capacity planning:
AUTOEXTENDis handy, but always control the maximum (MAXSIZE) and filesystem free space. - Change management: prefer low-risk expansion (
ADD DATAFILE). Avoid shrinking; if you must, test thoroughly. - Privileges: use minimum privileges for read-only checks; apply changes under proper procedures and dual control.
- Logs: check the alert log afterward (re-verify) to track expansions and failures.
FAQ
Q1. What is the unit in DBA_TABLESPACE_USAGE_METRICS?
A1. Blocks. Convert to MB/GB using DBA_TABLESPACES.BLOCK_SIZE.
Q2. Where do I check TEMP usage?
A2. Sum V$TEMP_SPACE_HEADER. For per-session details, see V$TEMPSEG_USAGE.
Q3. What should I verify first?
A3. (1) tablespace type/status, (2) current size/maximum per file, (3) usage (permanent and TEMP separately).
Q4. Which expansion method should I pick?
A4. Prefer ADD DATAFILE. Use RESIZE / AUTOEXTEND as needed; shrinking is a last resort.
Q5. Any caveats for BIGFILE tablespaces?
A5. A single very large file can increase backup/recovery impact. Decide block size and maximums up front.
Summary (key points)
- What a tablespace is: a logical container for segments; physically backed by data files.
- Verifying usage: permanent via
DBA_TABLESPACE_USAGE_METRICS; TEMP viaV$TEMP_SPACE_HEADER. - Sizing: manage
AUTOEXTENDmaximums and filesystem free space together. - Expansion principle: favor low-risk
ADD DATAFILE; treat shrinking as a last resort. - Operations: read-only evidence → change → re-verify results.
This article targets Oracle Database 19c (other versions may have different screens or defaults).
[reference]
Managing Tablespaces
コメント