Hello,
Windows Server 2008 + Oracle Database 10g Express Edition Release 10.2.0.1.0 (with 4Go limitation)
This machine will be replaced begining of next year with a new VM running Oracle 19c SE2. But until then, we fear that we reach the 4Go Limitation of Oracle XE.
The application performs lots of delete and inserts on a table called MESSAGE - 5,6millons rows
A purge is running and performs delete of entries older than 2 years in a continuous manner, but on a daily basis the application keeps inserting more rows and deleting older rows at the same time, thus the free space does not increase.
The purge performs a delete of entries older than 2 years ; it runs for one hour and then commit ; and then for another hour and then commit and so on. We have 6 years to purge. We have monitored the purge, it deletes the same count of rows every day, around 15000 rows per day, this does not change over day. The plan for delete is TABLE ACCESS BY USER ROWID. The query we can see in memory is DELETE FROM (SELECT ROWID, ... , FROM MESSAGE WHERE CREATIONDATE <... ) WHERE ROWID = :ROWID0
We cannot use the ALTER TABLE ... SHRINK SPACE approach to defragment the MESSAGE table because the application is not compatible with "ROW MOVEMENT ENABLED" - we have checked this with application supplier - see SQL query above
select ceil(sum(bytes) / 1024 / 1024) as used_mb, 0 as free_mb from dba_segments union all select 0 as u, ceil(sum(bytes) / 1024 / 1024) returns USED_MB 4971 and FREE_MB 230.
The XE panel shows tablespace USERS allocated 4130Mo, used 3993Mo, 96.70 percent
We have prepared a operation in order to compact space. We are on our way to create a clone of the VM Oracle Database 10g and run the exp/imp migration there.
stop application,
backup VM with snapshot including memory
exp application schemas,
drop application users cascade,
recreate application users with same password and grants
imp application users
start application
Q1 : The size of exp of application schema/users with no compression is 2,5 Go. If the database creates indexes during the import, is there a risk the import fails because of 4Go limitation ?
Q2 : is there a way to have the delete run quicker ? if we choose to delete records older than 6 months instead of older that 2 years ? Considering the plan it seems to me that it would not have any impact on speed, but I may be wrong ?
Q3 : Is there a better option than export/import -considering that migration to 19C SE2 is already on its way, and that any XE installation is now stricly prohibited for the future in our organization