Hello,
It's been a while since I host a few applications in the free tier and I was forced to create a second database instance and migrate my applications due to the original database simply "eating up" itself.
If I execute the following query:
select TBS "File_ID-Tablespace",
round(sum(bytes)/1024/1024/1024,2) USED_GB,
round(sum(bytes)/max(PROPERTY_VALUE)*100) PCT
from
(select file_id||'-'||tablespace_name TBS, bytes
from DBA_DATA_FILES
where tablespace_name!='SAMPLESCHEMA'),
(select PROPERTY_VALUE
from DATABASE_PROPERTIES
where PROPERTY_NAME = 'MAX_PDB_STORAGE')
group by rollup(TBS)
it turns out that I use only a small percentage of the available space.
11295-SYSTEM 0,74 4
11296-SYSAUX 15,01 75
11298-DATA 2,1 10
11299-DBFS_DATA 2,1 10
14589-UNDO_3 1 5
20,95 105
When I investigated I found out (too late) that the optimizer consumed a lot of space so I changed a few parameters here and there, but the damage was already done, there is no way to shrink datafiles in the sysaux tablespace. I did the following:
execute dbms_stats.purge_stats(sysdate - 2)
execute dbms_stats.alter_stats_history_retention(2)
execute dbms_workload_repository.modify_baseline_window_size(window_size=> 1)
I also found out that the apex activity logs took up lots of space and what really killed my is that the internal apex application runs in debug mode and due to a problem in the email queue it logged a lot of error messages into the apex_debug_log and no matter how I tried to purge those messages they stay in the log. But I suspect that even if I purged them I wouldn't be able to reclaim the space for the tablespace.
So here I am with a question: do you use the free tier in production and what do you do to limit the space usage due to system activities, log files, etc....?
I have a new shiny instance and I want to set things now to keep the most possible space for my user data in the long run.
Thank you for any help,
Krisztian