Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will learn how to Add Data File to Tablespace with queries step by step.
1. Columns and data types are learned from dba_data_files.
You can learn whether dba_data_files is a view or a table as follows.
In TOAD, we write dba_data_files on the editor screen and hold down Ctrl while moving the cursor over dba_data_files.
It becomes active as a link. When we click, its details open.
It can also be found in sqlplus as follows.
1 | desc dba_data_files; |
2. The following query is run to see where the tablespaces to be added are located.
1 | select TABLESPACE_NAME, FILE_NAME from dba_data_files; |
3. We find out whether there is space in the section we will add or not.
1 | select name, total_mb, free_mb from v$asm_diskgroup; |
4. We use the query below to see how much space is allocated for the tablespace and the maximum size that the tablespace can reach.
1 2 | select tablespace_name, trunc(sum(bytes)/1024/1024/1024) mevcut_size, trunc(sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024/1024) max_size from dba_data_files group by tablespace_name union all select tablespace_name, trunc(sum(bytes)/1024/1024/1024) mevcut_size, trunc(sum(maxbytes)/1024/1024/1024) max_size from dba_temp_files group by tablespace_name order by 1; |
5. After learning all this, the data file is added to the tablespace with the following query.
1 | alter tablespace USERS add datafile '+DATA' size 20G autoextend on next 1G maxsize unlimited |
