ADVERTISEMENT

How to Create & Manage Oracle Bigfile Tablespaces

3 min read

Bigfile tablespaces are commonly used in modern Oracle environments where large storage volumes, ASM, and Oracle Managed Files (OMF) are standard. They simplify tablespace administration by using a single large datafile instead of multiple small ones.

This guide explains Bigfile tablespaces and provides all essential DBA commands in one SQL block with SQL*Plus formatting.

What Is a Bigfile Tablespace?

A Bigfile Tablespace (BFT) is a special Oracle tablespace that contains exactly one datafile (or one tempfile for temporary tablespaces).
However, that file can grow extremely large—up to multiple terabytes depending on the block size.

Key Characteristics

  • One datafile per tablespace
  • Supports very large file sizes
  • Simplifies storage administration
  • Works best with ASM / OMF
  • Fully supports autoextend and online resize
  • Convertible to/from smallfile tablespaces

When to Use Bigfile Tablespaces

Use Bigfile tablespaces when:

  • Your environment uses ASM or OMF
  • You manage very large schemas/databases
  • You want simpler tablespace/file management

Avoid them if:

  • Your filesystem cannot support very large files
  • Your environment requires multiple datafiles per tablespace

All Bigfile Tablespace Commands

--------------------------------------------------------------------------------
-- SQL*Plus Output Formatting
--------------------------------------------------------------------------------
SET LINES 200;
COL TABLESPACE_NAME FORMAT A25;
COL FILE_NAME       FORMAT A60;
COL BIGFILE         FORMAT A8;
COL STATUS          FORMAT A15;
COL MB              FORMAT 999,999,999;

--------------------------------------------------------------------------------
-- 1. Create a Bigfile Tablespace (Manual File Path)
--------------------------------------------------------------------------------
CREATE BIGFILE TABLESPACE big_tbs
DATAFILE '/opt/oradata/BIG_TBS01.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

--------------------------------------------------------------------------------
-- 2. Create Bigfile Tablespace Using OMF (Recommended for ASM/OMF)
--------------------------------------------------------------------------------
CREATE BIGFILE TABLESPACE big_tbs_omf
DATAFILE SIZE 1G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

--------------------------------------------------------------------------------
-- 3. Check If a Tablespace Is Bigfile or Smallfile
--------------------------------------------------------------------------------
SELECT TABLESPACE_NAME, BIGFILE
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME;

--------------------------------------------------------------------------------
-- 4. Check Datafile of a Bigfile Tablespace
--------------------------------------------------------------------------------
SELECT FILE_NAME,
       BYTES/1024/1024 AS MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'BIG_TBS';

--------------------------------------------------------------------------------
-- 5. Resize Bigfile Datafile
--------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '/opt/oradata/BIG_TBS01.dbf'
RESIZE 5G;

--------------------------------------------------------------------------------
-- 6. Enable or Modify Autoextend on Bigfile
--------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '/opt/oradata/BIG_TBS01.dbf'
AUTOEXTEND ON NEXT 500M MAXSIZE 50G;

--------------------------------------------------------------------------------
-- 7. Rename a Bigfile Tablespace
--------------------------------------------------------------------------------
ALTER TABLESPACE big_tbs RENAME TO big_tbs_new;

--------------------------------------------------------------------------------
-- 8. Make a Bigfile Tablespace READ ONLY / READ WRITE
--------------------------------------------------------------------------------
ALTER TABLESPACE big_tbs_new READ ONLY;
ALTER TABLESPACE big_tbs_new READ WRITE;

--------------------------------------------------------------------------------
-- 9. Drop a Bigfile Tablespace (With or Without Datafiles)
--------------------------------------------------------------------------------
-- Drop tablespace but keep physical datafile
DROP TABLESPACE big_tbs_new;

-- Drop tablespace and delete its datafile
DROP TABLESPACE big_tbs_new INCLUDING CONTENTS AND DATAFILES;

--------------------------------------------------------------------------------
-- 10. Convert a Smallfile Tablespace to Bigfile (12c+)
--------------------------------------------------------------------------------
ALTER TABLESPACE small_tbs CONVERT TO BIGFILE;

--------------------------------------------------------------------------------
-- 11. Convert Bigfile to Smallfile
--------------------------------------------------------------------------------
ALTER TABLESPACE big_tbs_omf CONVERT TO SMALLFILE;

--------------------------------------------------------------------------------
-- 12. Create a Bigfile Temporary Tablespace
--------------------------------------------------------------------------------
CREATE BIGFILE TEMPORARY TABLESPACE big_temp
TEMPFILE '/opt/oradata/BIG_TEMP01.dbf'
SIZE 1G AUTOEXTEND ON NEXT 500M;

--------------------------------------------------------------------------------
-- 13. Set Bigfile Tablespace as Default for New Users
--------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TABLESPACE big_tbs;

--------------------------------------------------------------------------------
-- 14. Check Block Size & Bigfile Support
--------------------------------------------------------------------------------
SELECT TABLESPACE_NAME, BLOCK_SIZE, BIGFILE
FROM DBA_TABLESPACES;
--------------------------------------------------------------------------------

Summary

Bigfile tablespaces simplify the management of large Oracle databases by consolidating storage into a single, large datafile per tablespace. They are ideal for ASM, OMF, large OLTP systems, and data warehouses.
The command block above gives DBAs everything they need to create, manage, convert, resize, monitor, and drop Bigfile tablespaces efficiently.

Close ✖