Oracle Tablespace & User Creation | Step-by-Step Tutorial

ALTER SESSION SET CONTAINER = ORCLPDB;

sqlplus / as sysdba

SHOW CON_NAME;

ALTER SESSION SET CONTAINER = ORCLPDB;

-- Create a new tablespace named TBS_TEST

CREATE TABLESPACE tbs_test    

-- Specify the physical datafile location and name                      

DATAFILE 'D:\database19c\app\oradata\ORCL\TEST.DBF' 

-- Initial size of the datafile is 256 MB

SIZE 256M                                                                             

-- Allow the datafile to grow automatically when space is needed

AUTOEXTEND ON                                       

-- Maximum size the datafile can grow to is 2048 MB (2 GB)

MAXSIZE 2048M                                       

-- Use locally managed extents (recommended for performance)

EXTENT MANAGEMENT LOCAL                             

ONLINE;    -- Make the tablespace immediately available for use

-- Create a new database user named TEST1

CREATE USER TEST1 

-- Set the password for the user

IDENTIFIED BY TEST 

-- Set TBS_TEST as the default tablespace for this user

-- All objects (tables, indexes) will be created here by default

DEFAULT TABLESPACE tbs_test

-- Set TEMP as the temporary tablespace for sorting operations

TEMPORARY TABLESPACE TEMP; 

-- Grant DBA role to user TEST1

-- This gives full administrative privileges

GRANT DBA TO TEST1;