Add and resize datafiles
Sometimes, we face an issue with an Oracle database related with the space used. We can realize of this problem due to an alert from our monitoring system or with performing usual administration tasks.
In order to list the tablespaces we have in the database, first of all we need to connect to the database (sqlplus “/as sysdba”) and then we can use the following sql sentence:
select tablespace_name from dba_tablespaces;
Once we have the name of the tablespaces we can check the space of each one. We can use this sql sentence in order to check the size for all the tablespaces:
set lines 200 set pages 200 select a.tablespace_name TableSpace, a.Totalasig/1024/1024 MB_Asignados, b.Libre/1024/1024 MB_Libres, (a.Totalasig - b.Libre)/1024/1024 MB_Ocupados, ((a.Totalasig - b.Libre) * 100)/a.Totalasig Ocupacion_x100 from (select tablespace_name, sum(bytes) Totalasig from dba_data_files group by tablespace_name) a, (select tablespace_name , sum(bytes) Libre, max(bytes) Mayor_blk from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name;
The list of all the tablespaces provide us an easy view of the status of all of them regarding the space they have. Now it’s time to add a new datafile or resize one already exists for the tablespace we want to increase the space:
1. Oracle: how to resize a datafile already exists in a tablespace
First of all we need to know where are the datafiles for the tablespace we want to resize. For that, we can check it with this sql sentence:
Select bytes, file_name from dba_data_files where tablespace_name=’SYSTEM;
In this case, we want to know where the datafiles for the tablespace “SYSTEM” are.
Now we know the tablespace SYSTEM only have one datafile and where is located. The path is “/oracle/SID/sapdata1/system_1/” and we want to resize the datafile system.data1. The datafile has assigned 2000Mb and we will resize it to 3000Mb. For that we have to execute the following sentence:
Alter database datafile '/oracle/SID/sapdata1/system_1/system.data1' RESIZE 3000m;
2. Oracle: how to add new datafile to a tablespace:
Sometimes we want to add a new datafile instead to resize the already exists. For that, we have to check if we have enough space in our system. Once we know that it is possible to create a new datafile, we proceed to create the directory for it. Usually, the datafiles are created under the same path in the same filesystem changing only the name for the last directory and the datafile itself. That does not mean that we can create the directory in a different filesystem, it is only to get all the datafiles sort. In case we don’t have enough space in the filesystem already in use for this tablespace, it is not a problem to create the directory in other location.
In our case, we want to create a new datafile for the tablespace SYSTEM. As we already have one in the path “/oracle/SID/sapdata1/system_1”, we will create a new directory named “/oracle/SID/sapdata1/system_2”.
Once we have the new directory created, we can proceed to create the new datafile as follows:
Alter tablespace PSAPSR3 add datafile '/oracle/ SID/sapdata1/system_2size/system.data1' 1000m;