Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be discussing How to Growth Tablespace in Oracle after learning the sizes of Disk Groups.
While growing the tablespace, we need to review our disk space where our data files are stored.
Learning the sizes of Disk Groups.
1. If we use ASM, we learn 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 1st is to add a new data file, the 2nd is to grow the data file.
1. Adding data file.
1 2 3 | SQL> ALTER TABLESPACE WEBTVNEW ADD DATAFILE '/oracle/ora11g/data_ONURDB/ONURDB/webtvnew02.dbf' SIZE 20M; Tablespace altered. |
2. Enlarging the growing Data File.
1 2 3 | SQL> ALTER DATABASE DATAFILE '/oracle/ora11g/data_ONURDB/ONURDB/webtvnew01.dbf' RESIZE 20M; Database altered. |
