Oracle 19c Database Create Tablespace Practical Examples

⚙️ 1. Creating a Bigfile Tablespace

A Bigfile Tablespace contains a single datafile that can be very large in size.

CREATE BIGFILE TABLESPACE bigtbs_data01
  DATAFILE 'bigtbs_data01_f1.dbf'
  SIZE 200M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_data02
  DATAFILE '/data01/oracle/dbs/bigtbs_data02_f1.dbf'
  SIZE 200M AUTOEXTEND ON;

CREATE BIGFILE TABLESPACE bigtbs_data03
  DATAFILE '+data01'
  SIZE 200M AUTOEXTEND ON;

🔁 2. Creating an Undo Tablespace

Undo tablespaces store old values of data for read consistency and rollback operations.

CREATE UNDO TABLESPACE undotbs1
   DATAFILE 'undotbs_1.dbf'
   SIZE 100M AUTOEXTEND ON
   RETENTION GUARANTEE;

🧊 3. Creating a Temporary Tablespace

Used for sorting operations and temporary data storage.

CREATE TEMPORARY TABLESPACE temp01
   TEMPFILE 'temp01.dbf' SIZE 512M AUTOEXTEND ON;

You can also define a default file destination:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/data01/oracle/dbs';
CREATE TEMPORARY TABLESPACE temp02;

🧩 4. Adding Temporary Tablespace to a Group

Grouping temporary tablespaces helps balance load during large sort operations.

CREATE TEMPORARY TABLESPACE temp03
  TEMPFILE 'temp03.dbf' SIZE 512M AUTOEXTEND ON
  TABLESPACE GROUP tbs_grp01;

📦 5. Creating Basic Tablespaces

These are standard permanent tablespaces used to store user data.

CREATE TABLESPACE tbs_data01 
   DATAFILE 'tbs_data01_f1.dbf' SIZE 512M ONLINE;

CREATE TABLESPACE tbs_data02 
   DATAFILE 'tbs_data02_f1.dbf' SIZE 1024M LOGGING;

🔄 6. Enabling Autoextend for a Tablespace

Automatically grows the datafile as more data is added up to a maximum size of 100 megabytes.

CREATE TABLESPACE tbs02 
   DATAFILE '/data01/oracle/tbs02_f1.dbf' SIZE 500K REUSE
   AUTOEXTEND ON NEXT 500K MAXSIZE 500M;

📊 7. Creating a Locally Managed Tablespace

Locally managed tablespaces improve space management efficiency.

CREATE TABLESPACE tbs_data04 DATAFILE 'tbs_data04_f1.dbf' SIZE 100M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

CREATE TABLESPACE lmt_tbs01 DATAFILE 'lmt_tbs01_f1.dbf' SIZE 100M REUSE
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

CREATE TABLE lmt_table1 (col1 NUMBER, col2 VARCHAR2(20))
   TABLESPACE lmt_tbs01 STORAGE (INITIAL 2M);

Without uniform extents:

CREATE TABLESPACE lmt_tbs02 DATAFILE 'lmt_tbs02_f1.dbf' SIZE 100M REUSE 
  EXTENT MANAGEMENT LOCAL;

🔐 8. Creating an Encrypted Tablespace

Enhances data security by encrypting data at rest.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password";

CREATE TABLESPACE encrypt_tbs
  DATAFILE '/data/oracle/dbs/encrypt_tbs_f1.dbf' SIZE 1M
  ENCRYPTION USING 'AES256' ENCRYPT;

CREATE TABLESPACE enctbs2 DATAFILE 'enctbs2_f1.dbf' SIZE 1G 
  ENCRYPTION USING AES256 MODE 'XTS' ENCRYPT;

⚡ 9. Segment Space Management

Oracle can automatically manage free space within segments.

CREATE TABLESPACE auto_seg_tbs DATAFILE 'auto_seg_tbs_f1.dbf' SIZE 1M
   EXTENT MANAGEMENT LOCAL
   SEGMENT SPACE MANAGEMENT AUTO;

🧰 10. Oracle Managed Files (OMF)

Oracle automatically manages file names and locations when OMF is enabled.

Creates a tablespace with an Oracle-managed data file of 100M that is not autoextensible:

ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/data01/oracle/dbs';
CREATE TABLESPACE omf_tbs1;

CREATE TABLESPACE omf_ts2 DATAFILE AUTOEXTEND OFF;

ORACLE DATABASE ADMINISTRATION

recent posts

about