Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain how to increase a tablespace in an Oracle Database after determining the sizes of the Disk Groups.
When increasing the tablespace size, it is important to review the available disk space where our data files are stored.
Checking the size of Disk Groups:
If we are using ASM (Automatic Storage Management), the size can be determined as follows:
1 2 3 4 5 6 7 | SQL> select name, total_mb, free_mb from v$asm_diskgroup; NAME TOTAL_MB FREE_MB ------------------------------ ---------- ---------- DATA 2047890 911156 FRA 1638312 1391308 OCRVOTE 61437 60511 |
2. If we are using Single Node, we look at where our data files are.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -bash-3.2$ ll cd /oracle/ora11g/data_ONURDB/ONURDB/*.dbf ls: cd: No such file or directory -rw-r----- 1 oracle oinstall 1073750016 Jun 25 14:19 /oracle/ora11g/data_ONURDB/ONURDB/hr_bigts.dbf -rw-r----- 1 oracle oinstall 1073750016 Jun 25 16:42 /oracle/ora11g/data_ONURDB/ONURDB/hr_data.dbf -rw-r----- 1 oracle oinstall 943726592 Jun 25 16:57 /oracle/ora11g/data_ONURDB/ONURDB/sysaux01.dbf -rw-r----- 1 oracle oinstall 1184899072 Jun 25 16:57 /oracle/ora11g/data_ONURDB/ONURDB/system01.dbf -rw-r----- 1 oracle oinstall 20979712 Jun 25 12:46 /oracle/ora11g/data_ONURDB/ONURDB/temp01.dbf -rw-r----- 1 oracle oinstall 812654592 Jun 25 16:57 /oracle/ora11g/data_ONURDB/ONURDB/undotbs01.dbf -rw-r----- 1 oracle oinstall 20979712 Jun 25 14:42 /oracle/ora11g/data_ONURDB/ONURDB/undotbs02.dbf -rw-r----- 1 oracle oinstall 110108672 Jun 25 03:18 /oracle/ora11g/data_ONURDB/ONURDB/users01.dbf -rw-r----- 1 oracle oinstall 1073750016 Jun 25 03:18 /oracle/ora11g/data_ONURDB/ONURDB/webtvnew01.dbf -bash-3.2$ df -h /oracle/ora11g/data_ONURDB/ONURDB/ Filesystem Size Used Avail Use% Mounted on /dev/sda3 45G 29G 14G 69% / |
After this information, the data file is added.
This process is done in 2 ways. The first is adding a new data file, the second is expanding the data file.
1. Adding a data file
1 2 3 | SQL> ALTER TABLESPACE WEBTVNEW ADD DATAFILE '/oracle/ora11g/data_ONURDB/ONURDB/webtvnew02.dbf' SIZE 20M; Tablespace altered. |
2. Expanding the Existing Data File
1 2 3 | SQL> ALTER DATABASE DATAFILE '/oracle/ora11g/data_ONURDB/ONURDB/webtvnew01.dbf' RESIZE 20M; Database altered. |
