Hi,
I need help understanding where free space is disappearing to in Oracle Free Database.
First I tried Oracle Always Free Database in Oracle Cloud, it promised free 20GB of storage, but after creation of 1 table with (NUMBER, NUMBER, DATE, VARCHAR2(50), VARCHAR2(250)) and inserting 10 000 rows into it using python script (connect, loop, cursor.callproc, commit)... database allocated space went to 100% and tablespace usage was more than 10GB!
5MB of raw data should not use more than 10GB of space in database, right?
Then I tried running Oracle Free DB in Docker. Pulled image, started container, and without even touching it I can see the used space in sysaux tablespace growing during the day. Kept DB idle for a while, and SYSAUX used space went from original 379.94MB to 442.81MB.
BEFORE:
TABLESPACE_NAME AUT MAX_SIZE_IN_MB CURR_SIZE_IN_MB USED_SIZE_IN_MB PCT_FREE
------------------------------ --- -------------- --------------- --------------- ----------
SYSAUX YES 33554432 410 379.94 7 <--!
SYSTEM YES 33554432 280 276.75 1
UNDOTBS1 YES 33554432 100 14.88 85
TEMP YES 32767.98 20 19 5
USERS YES 33554432 7 6.06 13
no user activity for half a day
AFTER:
TABLESPACE_NAME AUT MAX_SIZE_IN_MB CURR_SIZE_IN_MB USED_SIZE_IN_MB PCT_FREE
------------------------------ --- -------------- --------------- --------------- ----------
SYSAUX YES 33554432 470 443.69 6 <--!
SYSTEM YES 33554432 290 287.94 1
UNDOTBS1 YES 33554432 100 18.38 82
TEMP YES 32767.98 20 16 20
USERS YES 33554432 7 6.06 13
from docker log:
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 624640K, new size 655360K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 655360K, new size 665600K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 665600K, new size 675840K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 675840K, new size 686080K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 686080K, new size 696320K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 696320K, new size 706560K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 706560K, new size 727040K
Resize operation completed for file# 3, fname /opt/oracle/oradata/FREE/sysaux01.dbf, old size 727040K, new size 737280K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 409600K, new size 419840K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 419840K, new size 430080K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 430080K, new size 440320K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 440320K, new size 450560K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 450560K, new size 460800K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 460800K, new size 471040K
FREEPDB1(3):Resize operation completed for file# 13, fname /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf, old size 471040K, new size 481280K
If I keep this DB for personal project for a while, this growth will use up all allowed free space and make DB unusable.
Am I missing something?
Should I make some changes immediately after DB creation?
Some regular maintenance to reclaim used space?
Please help.
Thank you.