We are trying to shrink the Oracle SYSTEM tablespace to release approximately 10 gigabytes to the Windows 2000 operating system. So far we have moved 2 large tables from SYSTEM to another tablespace (on another drive) using the ALTER TABLE MOVE TABLESPACE command. This has reduced the SYSTEM usage by about 10 gigabytes, but Oracle is still keeping the 10 gigabytes as already allocated and NOT allowing it for use by Windows for other purposes.
All attempts to modify the SYSTEM tablespace using alter table, or alter tablespace have failed, either with error ORA-25143 or with "cannot modify local storage," et cetera. Obviously we cannot drop SYSTEM and redefine its extents. So our question is, how do we shrink the SYSTEM tablespace to the minimum extent?
This was first published in September 2004