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; |
