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

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

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

🫙 Check Instance Status.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

If database is mount State then run the below Query:

SQL> ALTER DATABASE OPEN;
Database altered.

👥 Create a User

SQL> ALTER SESSION SET CONTAINER = ORCLPDB1;

✅ Using SQL Developer:

Open SQL Developer

Create a new connection:

        Connection Name: SYSDBA_Connection
        Username: SYS
        Password: (your SYS password)
        Role: SYSDBA
        Hostname: localhost or your server IP
        Port: 1521
        Service Name: ORCLPDB1

Click Test and then Connect

📦 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;

✅ 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;

👤 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;

✅ 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;

✅ 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

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    ✅
Click Test → Then Connect

🔍 Step 6: Verify Configuration

A. Check Tablespaces

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

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';

C. Check User Info

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS
WHERE USERNAME = 'CHIRAG1';

D. Login with Enterprise Manager

https://localhost:5500/em

or

https://192.168.224.133:5500/em

Username : sys
Password : Tiger123

⚙️ 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;

B. Resize Existing Datafile:

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

✅ 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

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
);

✅ 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;

✅ Explanation:

These INSERT statements add employee records.

COMMIT; makes the changes permanent in the database.

🔍 Step D: Verify the Inserted Data

SELECT * FROM employees;

✅ 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;

✅ 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;

B. Drop the tablespaces and associated files:

DROP TABLESPACE chirag_tbs INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE chirag_tbs_fixed INCLUDING CONTENTS AND DATAFILES;

🎓 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.