Tablespace is a logical storage structure of a database that contains tables, indexes etc. So if a table, lob partition or index is growing high, then the corresponding tablespaces will also grow high. In this article, we will learn what should we do if a tablespace is growing faster. If a tablespace is growing fast in oracle database, in most of the cases we check the available space in the ASM disk group and then add the datafile in that tablespace. But this is not the right way. As a DBA we always need to find out the reason why the tablespace is growing faster. So in that case we can do as follows:
Step 1 :
Check the size of the tablespace that is growing faster
Select tablespace_name, file_name,bytes/1024/1024 from dba_data_files where tablespace_name ='USERS';
Step 2:
Check the object or the segment that is consuming most of the spaces from the tablespace
select owner,segment_name,segment_type,sum(bytes/1024/1024) from dba_segments where tablespace_name='USERS' group by owner,segment_name,segment_type order by 4;
In this case, we can see COSTS table under SH schema is taking most of the spaces and it is partitioned.
Step 3:
After identifying the object that is taking most of the spaces, we can check if the partition can be dropped, or if it is the table, then
we can reach out to the application team for this.
So is how we can troubleshoot if a tablespace is growing fast in Oracle database.