Good Day Everyone:
I am currently using Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production, and one of the limitations is the 12GB database size.
I started running into some issues because I am reaching the upper limit so I decided to delete some rows from the table that has 98% of all the data housed within the DB. After deleting 70% of the table, I noticed the size of the DB did not reduce at all. A couple of queries showed the offending column and segment name to be:
<table><tbody><tr><td style="height:15.0pt;width:81pt;"><strong>OWNER</strong></td><td style="border-left-style:none;width:67pt;"><strong>TABLE_NAME</strong></td><td style="border-left-style:none;width:81pt;"><strong>COLUMN_NAME</strong></td><td style="border-left-style:none;width:143pt;"><strong>SEGMENT_NAME</strong></td><td style="border-left-style:none;width:98pt;"><strong>TABLESPACE_NAME</strong></td><td style="border-left-style:none;width:47pt;"><strong>SIZE_MB</strong></td></tr><tr><td style="border-top-style:none;height:15.0pt;"><strong>SANDBOX</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>XML_TAB</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>SYS_NC00004$</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>SYS_LOB0000085419C00004$$</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>USERS</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>10746.13</strong></td></tr></tbody></table>
The table has a few columns, but one of the columns is of the XMLTYPE:
<table><tbody><tr><td style="height:15.0pt;width:81pt;"><strong>COLUMN_NAME</strong></td><td style="border-left-style:none;width:67pt;"><strong>DATA_TYPE</strong></td><td style="border-left-style:none;width:81pt;"><strong>DATA_LENGTH</strong></td><td style="border-left-style:none;width:143pt;"><strong>DATA_PRECISION</strong></td><td style="border-left-style:none;width:98pt;"><strong>DATA_SCALE</strong></td></tr><tr><td style="border-top-style:none;height:15.0pt;"><strong>XML</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>XMLTYPE</strong></td><td style="border-left-style:none;border-top-style:none;"><strong>2000</strong></td><td style="border-left-style:none;border-top-style:none;"> </td><td style="border-left-style:none;border-top-style:none;"> </td></tr></tbody></table>
I've researched online how to possibly “rebuild” the LOB segment, but even running the following command results in the same issue:
ALTER TABLE XML_TAB MOVE LOB(SYS_NC00004$) STORE AS (TABLESPACE USERS);
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.
12954. 00000 - "The request exceeds the maximum allowed database size of 12 GB."
*Cause: The 12 GB space limit of Oracle Database Express Edition was reached.
*Action: Delete some files to create space or upgrade the database to the
full edition.
How can I reclaim the space in the database? I'm at a loss on what to do and would appreciate a little help.