How to Extend an Oracle TABLESPACE

Published by Patrizia Gardis on 10 June 201910 June 2019

How to Extend an Oracle TABLESPACE

Extending an Oracle TABLESPACE is a crucial task in managing the storage of an Oracle database. TABLESPACE is the largest storage area within the database, where tables, indexes, and other database objects reside.

During installation, five essential tablespaces are created to ensure the proper functioning of the database:

SYSTEM and SYSAUX tablespaces: dedicated to the system.
UNDO tablespace: dedicated to transaction management.
USERS tablespace: dedicated to Oracle database users.
TEMP tablespace: used by the database to store temporary data during sorting operations.

In addition to these tablespaces, there may be application-specific tablespaces, typically consisting of DATA tablespaces, INDEX tablespaces, and tablespaces dedicated to LOBs, provided they are created with the correct organization.

How to Extend an Oracle TABLESPACE

To extend a TABLESPACE, follow these steps:

Log in to the database using SQL*Plus with the user / as sysdba.

If you don’t have the name of the TABLESPACE in question, search for tablespaces with insufficient free space in the database with the query in the follow article
Query to see the size of all Oracle TABLESPACE

Once the tablespace is identified, we check whether there is a data file open on a file system or an ASM Disk Group (depending on how storage is managed), where usable space is available, using the statement on the page Query size datafiles of a tablespace

If the tablespace is managed with data files open with a fixed size, we will execute the command:

alter database datafile '/path/nome_datafile' resize --dimensione_datafile;

Example:

alter database datafile '/DBTEST_DATI/DBTEST/DATI01.DBF' resize 512m;

If a tablespace is managed with data files having a dynamic size, we will execute the command:

alter database datafile '/path/nome_datafile' autoextend on next --dimensione_extent maxsize --dimenizione_maxsize;

Example:

alter database datafile '/DBTEST_DATI/DBTEST/DATI01.DBF' autoextend on next 128m maxsize 1024m;

If we want to add a new data file, we must first identify the free space in the File System or the ASM Disk Group.

OS command to view free space on the File System:

df -h

Query to view free space on the ASM Disk Group is as follows link Query to View ASM DiskGroup Spaces.

Once the space is found, we look for the sequential number of the data file to create, which can be identified from the following link Query size datafiles of a tablespace

And we proceed with extending the tablespace using the command:

Tablespace managed with a fixed size:

alter tablespace --nome_tablespace add datafile '/path/nome_datafile03' size --dimensione_datafile;

Example:

alter tablespace DATI add datafile '/DBTEST_DATI/DBTEST/DATI03.dbf' size 2048m;

Tablespace managed with a dynamic size:

alter tablespace --nome_tablespace add datafile '/path/nome_datafile03' size --dimensione_initial_extent autoextend on next --dimensione_extent_successivi maxsize --dimensione_maxsize;

Example:

alter tablespace DATI add datafile '/DBTEST_DATI/DBTEST/DATI03.dbf' size 256m autoextend on next 256m maxsize 5096m;

If you have any doubts, don’t hesitate to write in the comments.

To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin

For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.

• Resume
• Our Story
• Query disconnect session Oracle
• How to Extend an Oracle TABLESPACE