Oracle TABLESPACE Statement

Open SQL Shell

Open a new SSH session and login as oracle user
Open sqlplus shell
# sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 24 23:26:47 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>

CREATE TABLESPACE Statements

Create New Tablespace

Creating a new tablespace with name "tbs01", with datafile name "tbs01_data.dbf" and of size "10MB".

SQL> CREATE TABLESPACE tbs01 DATAFILE 'tbs01_data.dbf' SIZE 10m; Tablespace created.
Check our Newly created Tablespace

Creating a new tablespace with name "tbs01", with datafile name "tbs01_data.dbf" and of size "10MB".

SQL> SELECT tablespace_name, bytes / 1024 / 1024 MB FROM dba_free_space WHERE tablespace_name = 'TBS01'; TABLESPACE_NAME MB ------------------------------ ---------- TBS01 9
Create New Tablespace with SIZE AUTOEXTEND Option

Creating a new tablespace with name "tbs01", with datafile name "tbs01_data.dbf" and of size "10MB" and will autoextend to 20MB.

SQL> CREATE TABLESPACE tbs1 DATAFILE 'tbs01.dbf' SIZE 10m AUTOEXTEND 20m; Tablespace created.

ALTER TABLESPACE Statements

Increase size of Tablespace

Increase size of Tablespace from 10MB to 20MB.

SQL> ALTER DATABASE DATAFILE 'tbs01.dbf' RESIZE 20m; Tablespace altered.
Add New Datafile to an existing Tablespace

Add new datafile of 15MB to Tablespace tbs01.

SQL> ALTER TABLESPACE tbs01 ADD DATAFILE 'tbs01_data_2.dbf' SIZE 15m AUTOEXTEND ON; Tablespace altered.

DROP TABLESPACE Statements

Drop Tablespace - Empty

Run following command to delete an empty table space.

SQL> DROP TABLESPACE tbs01; Tablespace dropped.
Drop Tablespace - Non-Empty

Run following command to delete a tablespace along with all its contents.

SQL> DDROP TABLESPACE tbs01 INCLUDING CONTENTS; Tablespace dropped.