🧩 How to Create a New Tablespace in Oracle Database

🧩 Introduction

Tablespaces are logical storage units in Oracle that group related data. As a DBA, you may need to create a new tablespace to separate application data, improve performance, or manage storage effectively.

Step 1: Create a Basic Tablespace

CREATE TABLESPACE my_tbs

DATAFILE '/u01/app/oracle/oradata/ORCL/my_tbs01.dbf'

SIZE 100M

AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

➡️ This creates a tablespace called my_tbs with an initial size of 100 MB, and autoextend enabled.

Step 2: Assign a User to the New Tablespace

ALTER USER my_user DEFAULT TABLESPACE my_tbs;

➡️ This ensures that all new objects created by my_user will go into my_tbs.

Step 3: Verify the Tablespace

SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb

FROM dba_data_files

WHERE tablespace_name = 'MY_TBS';

➡️ Confirms the tablespace was created and shows its size.

Conclusion

Creating new tablespaces helps you manage storage better and isolate data. Always use AUTOEXTEND with care and monitor tablespace growth to avoid storage issues.