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 the question of how to add a data file to the tablespace in Oracle?
1. We learn columns and data types from dba_data_files.
We can find out whether dba_data_files is a view or a table as follows.
In TOAD, we type dba_data_files on the editor screen and move the cursor over dba_data_files while holding down Ctrl. It becomes active as a link. When we click it, its details open.
It can also be found from 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; |
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 room in the section we will add.
1 | select name, total_mb, free_mb from v$asm_diskgroup; |
4. We use the following query to see how much space is allocated for the tablespace and the maximum size of the tablespace.
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 datafile is added to the tablespace with the following query.
1 | alter tablespace USERS add datafile '+DATA' size 20G autoextend on next 1G maxsize unlimited; |
6. If the ratio does not appear in the dba tablespace view, manual calculation is made below.
1 | select sum(file_size)/sum(file_maxsize)*100 from gv$filespace_usage where tablespace_id=6; |
