what is tablespace and How to add/resize the datafile on tablespace in oracle database?

   The contents are of my own testing and deployments. Not guaranteed that these might work in your environment. Please test before usage.

Summary: in this tutorial, you will learn How to add the datafile on tablespace in the oracle database.

What is a tablespace?

How to add the data file?

How to find the tablespace size?

What is a tablespace?

Tablespace is a logical storage unit in an oracle database that consists of one or more operating system files which are called datafiles.

• what is datafile?

• The data of logical data structures such as tables and indexes are stored in the datafile of the database
• one or more datafile form a logical unit of database storage called tablespace

• How to create the tablespace?

[oracle@ltitest ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 14 22:19:54 2023

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/ltitest1/o1_mf_system_ky3q81j0_.dbf

/u01/app/oracle/oradata/ltitest1/o1_mf_sysaux_ky3q7870_.dbf

/u01/app/oracle/oradata/ltitest1/o1_mf_undotbs1_ky3q95sj_.dbf

/u01/app/oracle/oradata/ltitest1/o1_mf_users_ky3q94pg_.dbf

SQL> CREATE TABLESPACE SCHOOL DATAFILE '/u01/app/oracle/oradata/ltitest1/ramesh01.dbf' SIZE 1G;

Tablespace created.

How to add the datafile?

SQL> ALTER TABLESPACE SCHOOL ADD DATAFILE '/u01/app/oracle/oradata/ltitest1/ramesh02.dbf' SIZE 500M;

Tablespace altered.

SQL>

How to resize the datafile?

SQL>  ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ltitest1/ramesh02.dbf' RESIZE 1G;

Database altered.

SQL>

How to find the tablespace size?

set line 189

col TABLESPACE_NAME for a23

SELECT fs.tablespace_name TABLESPACE_NAME,

df.totalspace TBS_TOTAL_SIZE_MB,

(df.totalspace - fs.freespace) MB_USED,

fs.freespace MB_FREE,

round(100 * (fs.freespace / df.totalspace),2) PERCENT_FREE,

df.maxsize MAXSIZE_MB

FROM

(SELECT tablespace_name,

sum(MAXBYTES/1024/1024) maxsize,

ROUND(SUM(bytes) / 1048576) TotalSpace

FROM

dba_data_files

GROUP BY tablespace_name ) df,

(SELECT tablespace_name,

ROUND(SUM(bytes) / 1048576) FreeSpace

FROM

dba_free_space

GROUP BY tablespace_name ) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

/

Note: You can add the max 65533 datafiles in a single tablespace.

                                    Thanks for your time!!