DEV Community

Cover image for Oracle 21c Tablespace Tutorial: Create & Manage via Command Line and SQL Developer
Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag)

Posted on

Oracle 21c Tablespace Tutorial: Create & Manage via Command Line and SQL Developer

#oracle #programming #database #dba

inchirags@gmail.com Oracle DBA Tutorial https://www.chirags.in

Oracle 21c Tablespace Tutorial: Create & Manage via Command Line and SQL Developer

🔧 Prerequisites:

Oracle 21c installed
SQL*Plus or SQL Developer connected as SYSDBA
Enter fullscreen mode Exit fullscreen mode

Full Guide: Creating & Managing Tablespaces and Users in Oracle 21c

🔰 Example Scenario:

We want to create a dedicated user called chirag with its own tablespace (chirag_tbs) and datafile (chirag_data01.dbf). We will cover both AUTOEXTEND ON and OFF configurations.

🔧 Step 1: Connect as SYSDBA

You must log in as a privileged user (SYSDBA) to perform tablespace and user management.

✅ Using SQL*Plus (Command Line):

su - oracle
sqlplus / as sysdba
Enter fullscreen mode Exit fullscreen mode

🫙 Check Instance Status.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN
Enter fullscreen mode Exit fullscreen mode

If database is mount State then run the below Query:

SQL> ALTER DATABASE OPEN;
Database altered.
Enter fullscreen mode Exit fullscreen mode

👥 Create a User

SQL> ALTER SESSION SET CONTAINER = ORCLPDB1;
Enter fullscreen mode Exit fullscreen mode

✅ Using SQL Developer:

Open SQL Developer

Create a new connection:
Enter fullscreen mode Exit fullscreen mode 
Connection Name: SYSDBA_Connection
        Username: SYS
        Password: (your SYS password)
        Role: SYSDBA
        Hostname: localhost or your server IP
        Port: 1521
        Service Name: ORCLPDB1
Enter fullscreen mode Exit fullscreen mode 
Click Test and then Connect
Enter fullscreen mode Exit fullscreen mode

📦 Step 2: Create Tablespace for User chirag

A tablespace is a logical storage unit. It uses datafiles to physically store data.

A. 📌 Tablespace with AUTOEXTEND ON

This allows Oracle to automatically increase file size when more space is needed.

CREATE TABLESPACE chirag_tbs
DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chirag_data01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 1G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Enter fullscreen mode Exit fullscreen mode

✅ Explanation:

Clause Meaning

DATAFILE Physical file location where data is stored

SIZE 100M Initial size of the datafile

AUTOEXTEND ON Enables automatic growth when space runs out

NEXT 10M Grows in 10MB chunks

MAXSIZE 1G Maximum size the datafile can grow

EXTENT MANAGEMENT LOCAL is the recommended mode

SEGMENT SPACE MGMT AUTO improves DML (insert/update/delete) performance

B. 📌 Tablespace with AUTOEXTEND OFF

Useful when you want fixed space and control space usage manually.

CREATE TABLESPACE chirag_tbs_fixed
DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chirag_data_fixed01.dbf'
SIZE 100M
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Enter fullscreen mode Exit fullscreen mode

👤 Step 3: Create User chirag and Assign Tablespace

Each user should have a default tablespace to store objects like tables and indexes.

CREATE USER chirag1 IDENTIFIED BY "Tiger123"
DEFAULT TABLESPACE chirag_tbs
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON chirag_tbs;
Enter fullscreen mode Exit fullscreen mode

✅ Explanation:

Part Description

IDENTIFIED BY Password for the user

DEFAULT TABLESPACE Where user's data (tables/indexes) will be stored

TEMPORARY TABLESPACE Used for sorting and temporary operations

QUOTA UNLIMITED User can use full space in chirag_tbs (up to MAXSIZE limit)

🔐 Step 4: Grant Required Privileges

Users by default can't log in or create objects. Grant minimum privileges.

GRANT CONNECT, RESOURCE TO chirag1;
-- Optional: More granular privileges

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO chirag1;
GRANT DBA TO chirag1;
Enter fullscreen mode Exit fullscreen mode

✅ Explanation of Roles:

Role/Privilege Purpose

CONNECT Basic login privileges

RESOURCE Allows creation of tables, sequences, triggers, etc.

CREATE SESSION Allows login

CREATE TABLE Allows table creation

💻 Step 5: Accessing as chirag in SQL Developer

Let’s connect as chirag to verify the setup.

Open SQL Developer
Enter fullscreen mode Exit fullscreen mode 
Click + New Connection
Enter:
Field       Value
Connection Name Chirag_Conn
Username    chirag1
Password    Tiger123
Hostname    localhost or 192.168.224.133
Port    1521
Service Name    ORCLPDB1
Save Password   ✅
Enter fullscreen mode Exit fullscreen mode

Click Test → Then Connect
🔍 Step 6: Verify Configuration

A. Check Tablespaces

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
Enter fullscreen mode Exit fullscreen mode

B. Check Datafiles of chirag_tbs

SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 AS SIZE_MB, AUTOEXTENSIBLE
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'CHIRAG_TBS';
Enter fullscreen mode Exit fullscreen mode

C. Check User Info

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS
WHERE USERNAME = 'CHIRAG1';
Enter fullscreen mode Exit fullscreen mode

D. Login with Enterprise Manager

https://localhost:5500/em
Enter fullscreen mode Exit fullscreen mode

or

https://192.168.224.133:5500/em
Enter fullscreen mode Exit fullscreen mode 
Username : sys
Password : Tiger123
Enter fullscreen mode Exit fullscreen mode

⚙️ Step 7: Manage Tablespace (Optional)

A. Add Additional Datafile (if space runs out):

ALTER TABLESPACE chirag_tbs
ADD DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chirag_data02.dbf'
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;
Enter fullscreen mode Exit fullscreen mode

B. Resize Existing Datafile:

ALTER DATABASE DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/chirag_data01.dbf'
RESIZE 200M;
Enter fullscreen mode Exit fullscreen mode

✅ Step 8: Insert Sample Data and Test as User chirag

🔧 Step A: Connect as User chirag in SQL Developer

    Connection Name: Chirag_Conn
    Username: chirag
    Password: chirag@123
    Hostname: localhost (or your DB server IP)
    Port: 1521
    Service Name: ORCLPDB1
Enter fullscreen mode Exit fullscreen mode

Click Test, then Connect

🛠️ Step B: Create Sample Table

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    department VARCHAR2(30),
    hire_date DATE DEFAULT SYSDATE
);
Enter fullscreen mode Exit fullscreen mode

✅ Explanation:

Column Type Description

emp_id NUMBER Unique employee ID

emp_name VARCHAR2(50) Name of employee

department VARCHAR2(30) Department name

hire_date DATE Defaults to current date

📥 Step C: Insert Sample Data

INSERT INTO employees (emp_id, emp_name, department)
VALUES (101, 'Chirag Mahto', 'IT');
INSERT INTO employees (emp_id, emp_name, department)
VALUES (102, 'Sneha Kapoor', 'HR');
INSERT INTO employees (emp_id, emp_name, department)
VALUES (103, 'Amit Singh', 'Finance');
COMMIT;
Enter fullscreen mode Exit fullscreen mode

✅ Explanation:

These INSERT statements add employee records.

COMMIT; makes the changes permanent in the database.
Enter fullscreen mode Exit fullscreen mode

🔍 Step D: Verify the Inserted Data

SELECT * FROM employees;
Enter fullscreen mode Exit fullscreen mode

✅ Output (Example):

EMP_ID EMP_NAME DEPARTMENT HIRE_DATE
101 Chirag Mahto IT 21-JUL-2025 10:32 AM
102 Sneha Kapoor HR 21-JUL-2025 10:32 AM
103 Amit Singh Finance 21-JUL-2025 10:32 AM
🔧 Optional Step E: Describe Table

To view structure of the table:

DESC employees;
Enter fullscreen mode Exit fullscreen mode

✅ Output:

Name Null? Type

EMP_ID NOT NULL NUMBER

EMP_NAME VARCHAR2(50)

DEPARTMENT VARCHAR2(30)

HIRE_DATE DATE

🎯 Summary of Insert and Test Flow

Task SQL/Tool Used

Connect as chirag SQL Developer

Create Table CREATE TABLE employees (...)

Insert Data INSERT INTO employees VALUES (...)

View Data SELECT * FROM employees;

Confirm Structure DESC employees;

❌ Step 9: Drop User and Tablespace (Cleanup)

A. Drop the user and their objects:

DROP USER chirag1 CASCADE;
Enter fullscreen mode Exit fullscreen mode

B. Drop the tablespaces and associated files:

DROP TABLESPACE chirag_tbs INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE chirag_tbs_fixed INCLUDING CONTENTS AND DATAFILES;
Enter fullscreen mode Exit fullscreen mode

🎓 Recap Table

Task Command/Tool

Connect as SYSDBA SQL Developer / SQL*Plus

Create tablespace CREATE TABLESPACE chirag_tbs ...

Create user CREATE USER chirag IDENTIFIED BY 'chirag@123' ...

Grant privileges GRANT CONNECT, RESOURCE TO chirag;

Connect as user SQL Developer → chirag / chirag@123

View datafiles SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ...

Resize or add datafile ALTER DATABASE DATAFILE ... RESIZE; / ALTER TABLESPACE

Drop user/tablespace DROP USER, DROP TABLESPACE

For any doubts and query, please write on YouTube video 📽️ comments 💬 section.

Note : Flow the Process shown in video 📽️.

😉Please, Subscribe and like for more videos:

https://www.youtube.com/@chiragtutorial

💛Don't forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
https://www.chirags.in

Note: All scripts used in this demo will be available in our website.
Link will be available in description.

Top comments (0)