Hi, we have an EMIS application developed using Oracle Apex using Oracle 19c Database. There are 10 Datafiles each of 32GB size in USERS tablespace. The datafiles size get full almost every 1-2 weeks, and we had a developer who used to run query and the datafiles size used to get back to normal but since he left, he is not cooperating. To temporary fix the solution, we create new datafiles. But since then we have now 10 datafiles, and the last datafile USERS10.DBF created yesterday is also about to become full.
Here's the screenshot of the datafiles of USERS tablespace.
Interestingly, when we contact the ex-developer, he executes some query and in few minutes the size get back to normal. Here's the screenshot of the datafiles after executing query.
I've also checked the tables, lob_segments by the size using the following query.
SELECT *
FROM (SELECT l.owner,
l.table_name,
l.column_name,
l.segment_name,
l.tablespace_name,
ROUND(s.bytes/1024/1024,2) size_mb
FROM dba_lobs l
JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
ORDER BY 6 DESC)
WHERE ROWNUM <= 20;
and got the following results.
While using the DBA tools in Sql developer, I get the following results under the "Objects" tab
As per the results, the SYS_LOB***$$ log segments have used a lot of storage, I tried shrinking it using queries found online, but no luck.
The actual size of the data in the database is 150GB since we take daily backups and the backup file size is 150 GB only.
Any help regarding this would be appreciated. Thanks in advance.
Regards,
Azaz Hussain