I am not an Oracle DBA, I have managed to install XE on my home PC to enable me to develop reporting tools using Excel, and therefore to emulate my work DB. To do this I have recently had to truncate and re-populate a number of large(ish) tables.
The problem now seem to have is I appear to have hit a limit, not THE limit in terms of the 4GB DB size, but a limit, on what I think to be 2 of the TABLESPACES on the DB.
My question is, how to I "shrink" them? I have tried the compact database function within the XE interface which reports back:
Space Allocated: 1,590 MB
Available: 3,530 MB
Maximum Allowable: 5,120 MB
Percent Used: 31%
Which seems to suggest I have more capacity, however when I view the DB through SQLSeveloper, it shows the following
SYSTEM PCT_USED: 97.75 ALLOCATED:350 USED:342.13 FREE:7.88
SYSAUX PCT_USED: 97.02 ALLOCATED: 300 USED:291.06 FREE: 8.94
UNDO PCT_USED: 81.2 ALLOCATED: 500 USED: 406 FREE: 94
USERS SPCT_USED: 47.06 ALLOCATED: 940 USED: 442.38 FREE: 497.63
It would seem that both the SYSTEM and SYSAUX tablespaces are the problem, however I do not have the first idea of what to do to overcome this; I have tried dropping a couple of large tables from the DB, but this has only freed up space in the USER tablespace.
Any help greatly appreciated.