🗄️ Tablespace

August 1, 2024 7 min read Memo Databases Storage

On this page

Concepts

A tablespace (TBS) is a logical group of storage for data; each tablespace is made of one or more datafiles (.dbf), created on a disk (TBS = 1.dbf + 2.dbf + …). One datafile belongs to exactly one tablespace; a tablespace can have many datafiles. To grow a database you grow the datafiles of the required tablespace (which needs free space on the filesystem or ASM disk).

View tablespaces & datafiles

1SELECT * FROM dba_tablespaces;   -- the tablespaces.
2SELECT * FROM dba_data_files;    -- the datafiles.
3SELECT * FROM dba_temp_files;    -- the temporary files.

1SELECT tablespace_name FROM dba_tablespaces;

Size & max size of a tablespace (interactive):

1COL file_name FOR a60;
2SELECT file_name, bytes/1024/1024/1024 AS dtf_gb, maxbytes/1024/1024/1024 AS dtf_max_gb, autoextensible
3FROM dba_data_files WHERE tablespace_name = '&TABLESPACE_NAME';

Find the default tablespace of a user:

1SELECT username, default_tablespace, temporary_tablespace FROM dba_users WHERE username = 'MY_USER';
2SELECT default_tablespace FROM dba_users WHERE username = 'APP_OWNER';

Create a tablespace (filesystem)

1CREATE TABLESPACE training DATAFILE '/u01/oradata/training_01.dbf' SIZE 10M;
2
3CREATE TABLESPACE training_9 DATAFILE '/u01/oradata/training_9.dbf'
4  SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

In DBA_DATA_FILES: AUTOEXTENSIBLE = yes/no, MAXBYTES = the max TBS size, INCREMENT_BY = the extension value.
For AUTOEXTEND, don’t pick too-small a value — there can be a limit on the number of extends.

1CREATE TABLESPACE ora_data
2  DATAFILE 'g:\oracle\oradata\orcl\ORA_DATA01.dbf' SIZE 100M,
3           'g:\oracle\oradata\orcl\ORA_DATA02.dbf' SIZE 100M
4  MINIMUM EXTENT 500K              -- V8 only
5  DEFAULT STORAGE (initial 500K next 500K MAXEXTENTS 500 PCTINCREASE 0);

Create a tablespace (ASM)

 1CREATE TABLESPACE "MYDATA" LOGGING
 2  DATAFILE '+DATA/ORCL/MYDATA01.dbf' SIZE 100M REUSE
 3  AUTOEXTEND ON NEXT 20M MAXSIZE 22G
 4  EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 5
 6CREATE TABLESPACE DATA_TBS
 7  DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON, '+DATA' SIZE 100M AUTOEXTEND ON;
 8-- grows to 32G by default (can be changed).
 9
10CREATE TABLESPACE DATA_CFRM DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE 10G;
11CREATE TABLESPACE TEMPTABS  DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON MAXSIZE 10G;

Change the maxsize:

1ALTER DATABASE DATAFILE '+DATA/ORCL/MYDATA.dbf'  AUTOEXTEND ON MAXSIZE 31G;
2ALTER DATABASE DATAFILE '+DATA/ORCL/MYINDX.dbf'  AUTOEXTEND ON MAXSIZE 22G;

Creation & storage parameters

Creation parameters:

DATAFILE — list of datafiles.
MINIMUM EXTENT — every extent size is a multiple of this integer.
ONLINE / OFFLINE — available immediately or not.
PERMANENT / TEMPORARY — permanent or temporary objects.
DEFAULT STORAGE — storage for all objects in the tablespace.

DEFAULT STORAGE parameters:

INITIAL — size of the first extent (default 5 × DB_BLOCK_SIZE).
NEXT — size of the next extent.
MINEXTENTS — extents allocated at segment creation (default 1).
PCTINCREASE — growth percentage: the n-th next = next * (1 + pctincrease/100)^(n-2). E.g. initial 16k, pctincrease 10 → extents 16k, 16k, 18k, 20k, …

Add / resize / drop datafiles

1ALTER TABLESPACE training ADD DATAFILE '/u01/oradata/training_02.dbf' SIZE 10M;
2ALTER DATABASE DATAFILE '/u01/oradata/training_01.dbf' RESIZE 20M;
3
4ALTER TABLESPACE MYDATA ADD DATAFILE '+DATA/ORCL/MYDATA02.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 20M;
5ALTER TABLESPACE MYDATA DROP DATAFILE '+DATA/ORCL/mydata.dbf';

When a tablespace has allocated extents, they remain allocated even if rows are deleted — so a tablespace filled to 80% cannot be shrunk by more than 20%. Only `TRUNCATE TABLE` or `ALTER INDEX REBUILD` releases extents; to retrim you may have to move the objects to another tablespace first.

DBF & blocks

A .dbf is divided into segments and blocks (the minimum unit). You choose the block size (2k / 4k / 8k / 16k / 32k) — e.g. for images, 32k blocks reduce I/O.

Temporary tablespace

1CREATE TEMPORARY TABLESPACE temp_training TEMPFILE '/u01/oradata/training_temp_1.dbf' SIZE 10M;
2ALTER TABLESPACE temp_training ADD TEMPFILE '/u01/oradata/training_temp_2.dbf' SIZE 10M;
3ALTER DATABASE TEMPFILE '/u01/oradata/training_temp_1.dbf' RESIZE 20M;

1CREATE USER my_user IDENTIFIED BY my_password
2  DEFAULT TABLESPACE training TEMPORARY TABLESPACE temp_training;

Check the space of the TEMP tablespace

1COL file_name FOR a60;
2SELECT tablespace_name, file_name, status, increment_by FROM dba_temp_files;
3
4SELECT file_name, bytes/1024/1024/1024, maxbytes/1024/1024/1024 FROM dba_temp_files;

1SELECT tablespace_name, tablespace_size/1024/1024 AS "TABLESPACE_SIZE",
2       free_space/1024/1024 AS "FREE_SPACE"
3FROM dba_temp_free_space;

Reclaim space on TEMP

1-- ⚠️ careful — only when nothing is using it:
2ALTER TABLESPACE TEMP SHRINK TEMPFILE '+DATA/ORCL/temp01.dbf' KEEP 1G;
3ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/oracle/base/oradata/ORCL/temp01.dbf' KEEP 1G;

Move a tablespace

Take it OFFLINE: ALTER TABLESPACE ora_data OFFLINE;
Copy the .dbf to the new directory.
Rename: ALTER DATABASE RENAME FILE 'g:\...\ORA_DATA01.dbf' TO 'g:\...\data\ORA_DATA01.dbf';
Bring it ONLINE: ALTER TABLESPACE ora_data ONLINE;
Delete the old file.

Read-only / drop

1ALTER TABLESPACE app_data READ ONLY;      -- read-only.
2ALTER TABLESPACE app_data READ WRITE;     -- read/write.
3
4DROP TABLESPACE app_data INCLUDING CONTENTS;   -- drop a tablespace.
5DROP TABLESPACE DATA_TBS INCLUDING CONTENTS AND DATAFILES;

`DROP TABLESPACE` does not delete the datafile; remove the file manually if needed (or use `… INCLUDING CONTENTS AND DATAFILES`).

Reclaim space (HWM)

Reclaims space on the filesystem/ASM when the tablespace is AUTOEXTEND:

 1SET LINESIZE 1000 PAGESIZE 0 FEEDBACK OFF TRIMSPOOL ON
 2WITH
 3 HWM AS (
 4  SELECT /*+ MATERIALIZE */ KTFBUESEGTSN TS#, KTFBUEFNO RELATIVE_FNO, MAX(KTFBUEBNO+KTFBUEBLKS-1) HWM_BLOCKS
 5  FROM SYS.X$KTFBUE GROUP BY KTFBUEFNO, KTFBUESEGTSN
 6 ),
 7 HWMTS AS (
 8  SELECT NAME TABLESPACE_NAME, RELATIVE_FNO, HWM_BLOCKS
 9  FROM HWM JOIN V$TABLESPACE USING(TS#)
10 ),
11 HWMDF AS (
12  SELECT FILE_NAME, NVL(HWM_BLOCKS*(BYTES/BLOCKS), 50*1024*1024) HWM_BYTES, BYTES, AUTOEXTENSIBLE, MAXBYTES
13  FROM HWMTS RIGHT JOIN DBA_DATA_FILES USING(TABLESPACE_NAME, RELATIVE_FNO)
14 )
15SELECT CASE WHEN AUTOEXTENSIBLE='YES' AND MAXBYTES>=BYTES THEN
16         'ALTER DATABASE DATAFILE '''||FILE_NAME||''' RESIZE '||CEIL(HWM_BYTES/1024/1024)||'M;'
17       ELSE
18         '/* AFTER SETTING AUTOEXTENSIBLE MAXSIZE HIGHER THAN CURRENT SIZE FOR FILE '||FILE_NAME||' */'
19       END SQL
20FROM HWMDF
21WHERE BYTES-HWM_BYTES > 1024*1024
22ORDER BY BYTES-HWM_BYTES DESC;

Database size

1SELECT TRUNC(SUM(bytes)/1024/1024/1024) AS "DB_SIZE_GB" FROM dba_data_files;   -- total (no temp).
2
3SELECT ROUND(SUM(bytes)/1024/1024/1024) AS "Used GB" FROM dba_segments;        -- used (no temp).
4SELECT ROUND(SUM(bytes)/1024/1024/1024) AS "Free GB" FROM dba_free_space;      -- free (no temp).
5-- used + free = total.

Space used per schema in a tablespace:

1SELECT owner, TO_CHAR(SUM(bytes)/1024/1024/1024, '990.00') AS gb
2FROM dba_segments WHERE tablespace_name = 'DATA_TBS' GROUP BY owner;

Space used per user (all tablespaces):

1SELECT owner, 'Used: ' || TO_CHAR(SUM(bytes)/1024/1024, '99990.00') || ' (Mo)'
2FROM dba_segments GROUP BY owner;

Table sizes

Backup a table:

1CREATE TABLE app_log_table_bak AS SELECT * FROM app_log_table;

List the tables of a schema:

1SELECT DISTINCT owner, object_name FROM dba_objects WHERE object_type = 'TABLE' AND owner = 'APP_OWNER';

What takes space (per segment type):

1SELECT segment_type, SUM(bytes)/(1024*1024*1024) gb
2FROM dba_segments WHERE owner = 'APP_USER' GROUP BY segment_type ORDER BY 2 DESC;

Find the biggest table per tablespace:

1SELECT tablespace_name, segment_name, tab_size_mb FROM (
2  SELECT tablespace_name, segment_name, bytes/1024/1024 tab_size_mb,
3         RANK() OVER (PARTITION BY tablespace_name ORDER BY bytes DESC) AS rnk
4  FROM dba_segments WHERE segment_type = 'TABLE'
5) WHERE rnk = 1;

Size of every table (with its indexes and LOBs) in a schema:

1SET LINES 200 PAGES 2000
2COLUMN size_mb    FORMAT '999,999,990.0'
3COLUMN num_rows   FORMAT '999,999,990'
4COLUMN owner      FORMAT A16
5SELECT lower(owner) AS owner, lower(table_name) AS table_name, tablespace_name,
6       num_rows, blocks*8/1024 AS size_mb, pct_free, compression, logging
7FROM all_tables
8WHERE owner LIKE UPPER('&1') OR owner = USER
9ORDER BY 1,2;

Recycle bin

Disable the recycle bin:

1ALTER SYSTEM SET recyclebin = OFF SCOPE=SPFILE;
2-- or, on versions without the RECYCLEBIN parameter:
3ALTER SYSTEM SET "_recyclebin" = FALSE SCOPE=BOTH;
4-- on RAC, disable it on every instance.
5PURGE DBA_RECYCLEBIN;

Check & list:

1SHOW PARAMETER recyclebin;
2SHOW RECYCLEBIN;

Schemas & owners

An owner is a schema — a user that owns database objects.
Unlike “one file per database”, Oracle stores the objects of the schemas inside tablespaces.

1SELECT SUM(bytes), owner FROM dba_segments GROUP BY owner;   -- owners = the schemas on the instance.
2SELECT DISTINCT owner, tablespace_name FROM dba_segments;    -- one schema can span several tablespaces.
3SELECT username, default_tablespace, temporary_tablespace FROM dba_users;   -- defaults live in dba_users.

Oracle Managed Files (OMF)

OMF lets Oracle auto-create and name datafiles, tempfiles, redo logs and control files, so you no longer specify paths in CREATE TABLESPACE.

1SHOW PARAMETER db_creat;

1ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE = BOTH;   -- datafiles / tempfiles / controlfiles.

db_create_file_dest — default location for datafiles, tempfiles and the default control file.
db_create_online_log_dest_1..5 — locations for the redo logs (multiplexed).
db_recovery_file_dest — the Fast Recovery Area (FRA) for RMAN backups and archived logs.

1SHOW PARAMETER reco;
2-- control_file_record_keep_time, db_recovery_file_dest, db_recovery_file_dest_size ...

With OMF enabled, CREATE TABLESPACE t DATAFILE SIZE 100M; (no path) places the file automatically.

Sunday, October 4, 2026 Thursday, August 1, 2024

🧩 Clusterware

💽 Disks ASM

Browse Docs