Basic Concepts

Tablespace Overview

An Oracle tablespace is a logical storage structure used to store database objects such as data files. Its the largest logical unit of information storage in Oracle, containing segments, extents, and data blocks. A tablespace provides a logical container for database objects, and a single database may consist of multiple tablespaces. Tablespaces can be leveraged for Oracle performance tuning (a distinctive advanced feature of Oracle Data base).

Classification of Tablespaces

• Permanent tablespace: Stores objects that need to be permanently persisted, such as tables, views, stored procedures, and indexes.
• Temporary tablespace: Stores intermediate execution data, e.g., temporary data generated during ORDER BY sorting or grouping. The content is automatically released after the operation completes. Temporary tablespaces are shared; usually the TEMP tablespace is used by all users. Additional temporary tablespaces can be created if needed.
• Undo tablespace: Stores pre-modification copies of data. It holds the original values before data changes, enabling rollback, recovery, and undo operations.

Introduction

In Oracle Database, the location and metadata of data files are recorded in the control file. Using OS commands like rm or cp does not update the control file records. Therefore, you must use ALTER statements to modify the control file’s data file information to keep the database operational.

Since the size of a tablespace is determined by the number and sizes of its data files, you can increase a tablespace by adding data files, resizing existing data files, or changing their autoextend settings. However, for bigfile tablespaces, you cannot add data files; they consist of a single file.

1. Adding Data Files

a) Adding a data file to a permanent tablespace:

ALTER TABLESPACE app_data ADD DATAFILE '/oradata/app_data02.dbf' SIZE 200M;

b) Adding a tempfile to a temporary tablespace:

ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 500M;

2. Resizing Data Files

a) Resize an existing data file. An error occurs if the specified size is smaller than the currently used space.

ALTER DATABASE DATAFILE '/oradata/app_data01.dbf' RESIZE 2G;

3. Modifying Data File Autoextend

If a tablespace was created without AUTOEXTEND ON, it has a fixed size. You can enable automatic extension with the following:

ALTER DATABASE DATAFILE '/oradata/app_data01.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE 10G;

This enables autoextension in increments of 50 MB up to a maximum of 10 GB.

4. Resizing Bigfile Tablespaces

Since a bigfile tablespace has only one data file, you can resize it by directly altering the data file or the tablespace.

a) Change the bigfile tablespace big_ts data file to 500 MB:

(1) Resize data file:

ALTER DATABASE DATAFILE '/oradata/big_ts.dbf' RESIZE 500M;

(2) Resize tablespace:

ALTER TABLESPACE big_ts RESIZE 500M;

b) Enable autoextend for bigfile tablespace big_ts:

(1) Via data file:

ALTER DATABASE DATAFILE '/oradata/big_ts.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

(2) Via tablespace:

ALTER TABLESPACE big_ts AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

Common Queries and Operations

Querying Tablespace Usage

Query 1:

SET LINESIZE 300 PAGES 999
SELECT a.tbs_name,
       ROUND(a.total_gb) "Total_GB",
       ROUND(a.total_gb - b.free_gb) "Used_GB",
       ROUND(b.free_gb) "Free_GB",
       ROUND(b.free_gb / a.total_gb * 100, 2) || '%' "Free_Pct"
FROM (SELECT tablespace_name AS tbs_name, SUM(bytes)/1024/1024/1024 total_gb
      FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes)/1024/1024/1024 free_gb
      FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tbs_name = b.tablespace_name(+);

Alternatively:

SET LINESIZE 200 PAGES 999
COL tbs_name FOR a25
SET SPACE 2
SELECT a.TABLESPACE_NAME tbs_name,
       ROUND(a.BYTES/1024/1024/1024) Total_GB,
       ROUND((a.BYTES - NVL(b.BYTES,0))/1024/1024/1024) Used_GB,
       ROUND((1 - ((a.BYTES - NVL(b.BYTES,0)) / a.BYTES)) * 100, 2) Free_Pct,
       ROUND(((a.BYTES - NVL(b.BYTES,0)) / a.BYTES) * 100, 2) Used_Pct,
       NVL(ROUND(b.BYTES/1024/1024/1024),0) Free_GB,
       a.AUTO
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(AUTOEXTENSIBLE) AUTO
          FROM sys.dba_data_files
         GROUP BY TABLESPACE_NAME) a,
       (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
          FROM sys.dba_free_space
         GROUP BY TABLESPACE_NAME) b
 WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)
 ORDER BY ((a.BYTES - b.BYTES) / a.BYTES) DESC;

Querying Total Database Size

SELECT ROUND(SUM(BYTES)/1024/1024/1024,2)||' GB' FROM dba_extents;

Querying Table Space Usage

SELECT BYTES/1024/1024 MB, segment_name, tablespace_name, bytes 
FROM dba_segments 
WHERE segment_name='EMPLOYEES' AND owner='HR';

Querying Data File Names

SELECT COUNT(*) FROM dba_data_files WHERE tablespace_name='LOG_DATA_IDX';
SELECT * FROM dba_data_files WHERE tablespace_name='LOG_DATA_IDX';
SELECT * FROM dba_data_files WHERE tablespace_name='APP_DMA' AND file_name LIKE '%2023%';

Querying ASM Disk Group Free Space

SELECT group_number, name, total_mb/1024 total_gb, TRUNC(free_mb/1024) free_gb 
FROM v$asm_diskgroup;

Adding Tablespaces (Non-ASM)

Adding Data Files

ALTER TABLESPACE APP_DMA ADD DATAFILE '/oradata/app_dma02.dbf' SIZE 30G AUTOEXTEND OFF;
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/vgodba02/oracle/undotbs1_03.dbf' SIZE 30G AUTOEXTEND OFF;

-- Enable unlimited autoextend
ALTER DATABASE DATAFILE '/oradata/test_data.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Autoextend by 512 MB when needed, maximum 10 GB
ALTER DATABASE DATAFILE '/oradata/test_data.dbf' AUTOEXTEND ON NEXT 512M MAXSIZE 10G;

Adding Undo Tablespace Data Files

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA/ORCL/datafile/undotbs01.dbf' SIZE 20G AUTOEXTEND OFF;
ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '+DATA/ORCL/datafile/undotbs02.dbf' SIZE 20G AUTOEXTEND OFF;

Resizing Data Files

View data file sizes:

SELECT file_name, tablespace_name,
       bytes/1024/1024/1024 gb,
       maxbytes/1024/1024/1024 max_gb,
       autoextensible
FROM dba_data_files
WHERE tablespace_name='USERS';

Adjust data file sizes:

ALTER DATABASE DATAFILE '+DATA/orcl/datafile/test.354.923261927' RESIZE 50M;
ALTER DATABASE DATAFILE '+DATA/orcl/datafile/app_data369.dbf' RESIZE 30G;

Taking Tablespaces Offline / Online

ALTER TABLESPACE users_demo OFFLINE;
ALTER TABLESPACE users_demo ONLINE;

Renaming a Tablespace

(Note: The tablespace must be online to be renamed; offline rename is not allowed.)

ALTER TABLESPACE old_ts RENAME TO new_ts;

Adding Data Files on ASM Disk Groups

ALTER TABLESPACE LOG_DATA ADD DATAFILE '+DATA' SIZE 30G AUTOEXTEND OFF;

Auto-Adding Tablespace Data Files (OMF)

SHOW PARAMETER db_create_file_dest
ALTER TABLESPACE IDX_DATA ADD DATAFILE '+DATA' SIZE 30G AUTOEXTEND OFF;

Completely Dropping a Tablespace

DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;

Tablespace Size Limitations

When extending a tablespace, you may encounter ORA-01144. Each database can have up to 1022 files (2 reserved). Each file can have up to 4M blocks. With the default block size of 8 KB, the maximum data file size is 4M × 8 KB = 32 GB.

1. A single data file cannot exceed 32 GB during extension. If you need more space, you must add a new data file, which itself is also limited to 32 GB.
2. Starting from Oracle 10g, you can create bigfile tablespaces to allow a single data file up to 32 TB (theoretically).
If an extension still fails even when the target size is below 32 GB, check available disk or memory resources.

Resolution: Adjust the resize value to a suitable size.

The actual maximum data file size also depends on DB_BLOCK_SIZE. At database creation, you can choose 4K, 8K, 16K, 32K, or 64K. Oracle’s physical file maximum is 4,194,304 blocks (OS-dependent):
4K block: ~16 GB
8K block: ~32 GB
16K block: ~64 GB
32K block: ~128 GB
64K block: ~256 GB

Thus, setting MAXSIZE UNLIMITED does not mean truly unlimited expansion; it is still constrained by the block size. Bigfile tablespaces can bypass some limits, but practical limits are determined by the file system. A smallfile tablespace can contain up to 1022 data files. Refer to the Oracle Physical Database Limits documentation for details.

Creating Tablespaces with OMF

OMF (Oracle Managed Files) simplifies administration by letting Oracle automatically name, size, and locate files. It also automatically deletes OS files when the corresponding database files are dropped.

Files Supported by OMF

• Tablespace data files
• Online redo log files
• Control files

Prerequisite: Set the appropriate initialization parameters.

Managing Data Files with OMF

Caution: OMF removes physical files when tablespaces are dropped; use with care.

Parameter: DB_CREATE_FILE_DEST — the default location for data files and temp files when no path is specified. It also serves as the default for online logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not set.

Example:

1. Attempt to create a tablespace without specifying a data file:

SQL> CREATE TABLESPACE demo_ts;
ERROR at line 1:
ORA-02199: missing DATAFILE/TEMPFILE clause

2. Check the parameter:

SQL> SHOW PARAMETER db_create_file_dest
NAME                TYPE        VALUE
-------------------- ----------- --------------------
db_create_file_dest string

3. Set the parameter:

SQL> ALTER SYSTEM SET db_create_file_dest = '/u01/oracle/oradata/ORCL';

4. After setting, creation succeeds:

SQL> CREATE TABLESPACE demo_ts;
Tablespace created.
SQL> SELECT name FROM v$datafile;
/u01/oracle/oradata/ORCL/datafile/o1_mf_demo_ts_5vrl1t7h_.dbf

5. You can also specify a size:

SQL> CREATE TABLESPACE archive_ts DATAFILE SIZE 500M;
SQL> SELECT name FROM v$datafile;
/u01/oracle/oradata/ORCL/datafile/o1_mf_archive_ts_5vrlgqtl_.dbf

6. Dropping an OMF tablespace automatically removes its files:

SQL> DROP TABLESPACE archive_ts;

Compare with a non-OMF tablespace:

SQL> CREATE TABLESPACE manual_ts DATAFILE '/u01/oracle/oradata/manual.dbf' SIZE 10M;
SQL> DROP TABLESPACE manual_ts;
-- file still exists on the OS; use INCLUDING CONTENTS AND DATAFILES to remove it.

Summary: Using OMF

1. Set DB_CREATE_FILE_DEST
2. Verify with SHOW PARAMETER
3. Create tablespaces without explicit data file clauses
4. You can provide size or a specific path if needed
5. UNDO and TEMPORARY tablespaces can also be managed: CREATE UNDO TABLESPACE ..., CREATE TEMPORARY TABLESPACE ...
6. Dropping an OMF tablespace deletes physical files automatically

Moving Tablespace Data Files

Data files are physical files limited by disk space. When the disk runs low, you can move data files to a new location. ALTER TABLESPACE can move data files for most tablespaces except SYSTEM, SYSAUX, UNDOTBS, and temporary tablespaces. ALTER DATABASE works for all tablespaces but requires instance shutdown.

Method 1: Using ALTER DATABASE (requires downtime)

Steps:

1. Shut down the database;
2. Copy data files to the new location;
3. Start the database in MOUNT mode;
4. Rename the file in the control file;
5. Open the database;

Example:

SQL> SHUTDOWN IMMEDIATE;
$ cp /old_path/sales.dbf /new_path/sales.dbf
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE '/old_path/sales.dbf' TO '/new_path/sales.dbf';
SQL> ALTER DATABASE OPEN;
SQL> SELECT name FROM v$datafile;

Method 2: Using ALTER TABLESPACE (online move)

Steps:

1. Take tablespace offline;
2. Copy the file at OS level;
3. Rename the data file within the tablespace;
4. Bring the tablespace online;

Example:

SQL> ALTER TABLESPACE sales_ts OFFLINE;
$ cp /old_path/sales.dbf /new_path/sales.dbf
SQL> ALTER TABLESPACE sales_ts RENAME DATAFILE '/old_path/sales.dbf' TO '/new_path/sales.dbf';
SQL> ALTER TABLESPACE sales_ts ONLINE;

Releasing Temporary Tablespace Space

Temporary tablespace is used for sorting, index creation, views, etc. The space should be released automatically, but sometimes segments remain, causing high usage.

Causes: ORDER BY / GROUP BY (disk sorts), index creation/rebuild, DISTINCT, UNION/INTERSECT/MINUS merge joins, ANALYZE, and certain anomalies.

Solutions:

Option 1: Add another tempfile.

Option 2: Create a new temporary tablespace and switch if disk space is limited.

-- 1. Check current default temporary tablespace:
SELECT * FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

-- 2. Create new temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/oradata/temp_new.dbf' SIZE 31G;

-- 3. Set as default
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

-- 4. Drop old tablespace
DROP TABLESPACE TEMP_OLD INCLUDING CONTENTS AND DATAFILES;

-- If drop fails due to ORA-60100 (sort segment in use), find and kill the offending session:
SELECT se.username, se.sid, se.serial#, su.extents, su.blocks*to_number(rtrim(p.value)) AS space,
       tablespace, segtype, sql_text
FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
WHERE p.name='db_block_size' AND su.session_addr=se.saddr
  AND s.hash_value=su.sqlhash AND s.address=su.sqladdr
ORDER BY se.username, se.sid;

ALTER SYSTEM KILL SESSION 'sid,serial#';

Setting Tablespace Autoextend

-- View current data file autoextend settings
SELECT file_name, autoextensible, increment_by FROM dba_data_files;

-- For a specific tablespace
SELECT tablespace_name, file_name, autoextensible 
FROM dba_data_files 
WHERE tablespace_name = 'USERS';

-- Enable autoextend
ALTER DATABASE DATAFILE '/oradata/users01.dbf' AUTOEXTEND ON;

-- Disable autoextend
ALTER DATABASE DATAFILE '/oradata/users01.dbf' AUTOEXTEND OFF;