How to Reclaim Diskspace used by Oracle XE Database?

A new answer from Sergey Soshnikov - Sergey has examined that if you delete all non-builtin users (i.e. all schema users created for ONE, but not builtins like SYSTEM or DBSNMP), then take the following steps, it reclaims space:

use following sql to get tablespace sizes and file paths

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

Use following sqls to shrink data files, with auto-extend turned on they will grow up again at some point:

ALTER DATABASE DATAFILE 'E:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF' resize 150M;
ALTER DATABASE DATAFILE 'E:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM_DATA.DBF' resize 150M;
ALTER DATABASE DATAFILE 'E:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM_INDX.DBF' resize 150M;

I recently found this approach useful:

Run this query to see how much space can be reclaimed by resizing datafiles:

with block_size as (
  select value block_size from v$parameter where name = 'db_block_size'
)
select file_name,
       ceil( (nvl(hwm,1)* (select block_size from block_size))/1024/1024 ) smallest,
       ceil( blocks* (select block_size from block_size)/1024/1024) currsize,
       ceil( blocks* (select block_size from block_size)/1024/1024) -
       ceil( (nvl(hwm,1)* (select block_size from block_size))/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+);

Run this query to generate SQLs which will resize those datafiles to be as small as possible.

with 
block_size as (
  select value block_size from v$parameter where name = 'db_block_size'
)
select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*(select block_size from block_size))/1024/1024 )  || 'm;' cmd
from dba_data_files a, 
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) 
  and ceil( blocks*(select block_size from block_size)/1024/1024) -
      ceil( (nvl(hwm,1)*(select block_size from block_size))/1024/1024 ) > 0
      

Demo databases are running Oracle XE database and the space is limited to 11g maximum disk space. Application tasks performed during a demo session might issue significant DML statements and hence the undo and temp might be filled up and hence the space. Dropping the schema alone and recreating it will not release the space, but the undo tablespace and temp tablespace has to be created new and re-assigned to the instance and database. See how to do that below and it can be done as the user PLT_INSTALLER.

1. Undo Tablespace Cleaning and reassignment:

   create undo tablespace undotbs datafile '/u01/app/oracle/oradata/XE/undotbs.dbf' size 128M autoextend on next 5M maxsize unlimited extent management local;
   
   alter system set undo_tablespace='undotbs' scope=both;
   
   drop tablespace undotbs1 including contents and datafiles;
   

   Note:- Step 1 can be repeated if the new undo tablespace (undotbs) fills up by providing a different name for example undotbs1 and the datafile spec will change to undotbs1.dbf under the same directory location.

2. Temporary Tablespace Cleaning and reassignment:

   create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/XE/temp1.dbf' SIZE 64M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED extent management local uniform size 1m;
   
   alter database default temporary tablespace temp1;
   
   drop tablespace temp including contents and datafiles;
   

   Note:- Step 2 can be repeated if the new temporary tablespace (temp1) fills up by providing a different name for example temp and the tempfile spec will change to temp.dbf under the same directory location.

   If you want the existing schema/user account has to be maintained without dropping and recreating, then follow Step 3 below to shrink and compact disk space that was fragmented due to DML's.

3. Shrinking USERS segments

   Note :- You may have to run this from a OS command window since the output have to be spooled to another file. Then the spooled output file has to be executed as the user PLT_INSTALLER from a sqlplus prompt. If sqldeveloper allows you to spool then you may use that as well. I have given the spool line based on the linux OS and based on the p10pltd home location. The script will prompt you to supply the schema owner name for example you can supply demo35_owner and hit enter. Go to /home/p10pltd directory location and via sqlplus login as plt_installer and execute the script @reclaim_unused_space.sql and be patient until it completes its execution and exit the sqlplus.

   Very Important :- This step will take significant amount of time while the spooled script gets executed and also will consume CPU resources. This keeps the existing/current schema wihout dropping and unused space can be re-claimed. During this process the application has to stay shut. The space reclaim is purely based on the available unused space considering the maximum size of the datafile allowed which is 11g in XE.

   set linesize 999 pagesize 0 verify off echo off feedback off trims on spool /home/p10pltd/reclaim_unused_space.sql
   
   select 'alter table ' || owner||'.'||table_name || ' enable row movement '||';' from all_tables where owner = upper('&&schema_owner') union all select 'alter table ' || owner||'.'||table_name || ' shrink space '||';' from all_tables where owner = upper('&&schema_owner') union all select 'alter table ' || owner||'.'||table_name || ' shrink space compact '||';' from all_tables where owner = upper('&&schema_owner') union all select 'alter table ' || owner||'.'||table_name || ' shrink space cascade '||';' from all_tables where owner = upper('&&schema_owner') union all select 'exit' from dual;
   
   spool off;
   

Reclaiming users Disk Space on Oracle XE databases When it reaches the 11gig limit.

Oracle XE database is limited to 11g of users tablespace. When the space threshold is reached you will not be able to perform any DML's or DDL's.

To reclaim space please execute the script drop_user_reclaim_space.sql from a sqlprompt logging in as plt_installer. The script is located at /home/p10pltd. Please note by running this sql script, the existing schema will be dropped and tablespace will be recreated and the schema will be recreated. The script will prompt you to enter value for the schema name and password.

On Oracle XE databases, reclaiming users' disc space When the memory limit of 11 gigabytes is reached.

Users tablespace in the Oracle XE database is restricted to 11g. You won't be able to perform any DMLs or DDLs until the space threshold is reached. Please run the script drop user reclaim space.sql from a sqlprompt when logged in as plt installer to reclaim space./home/p10pltd is the location of the script. Please be aware that by running this sql script, the original schema will be dropped and the tablespace and schema will be recreated. You will be prompted to enter a value for the schema name and password by the script.

If you want to keep the existing databases, I found the following simple operations work:

1. find the high water mark (highest block id):
  log in with servicename:xepdb1 and user SYSTEM, run

select * from dba_extents
order by block_id desc

  Get the top list of segment names with the database name and segment_type.  

2. truncate those tables

If the top list includes unused database, drop the database. 
Otherwise,  log in to the user database of each segment of the top list starting from the 1st one.  

if segment_type is 'Index', find the table name by:

SELECT INDEX_NAME, OWNER,TABLE_NAME
FROM all_indexes
WHERE INDEX_NAME='&INDEX_NAME';

if segment_type is 'LOBSEGMENT', find the table name by
select l.table_name,
l.column_name,
l.segment_name lob_name
from user_lobs l
join user_objects o
on( o.object_name = l.segment_name and object_name in ('&LOBSEG_NAME'));

Once get the table names, find constraints of those tables

select owner,constraint_name,table_name,R_constraint_name,status
from
all_constraints
where
r_constraint_name in
(select constraint_name
from
all_constraints
where
upper(table_name)='&TABLE_NAME')
and owner='Y';
 

Truncate tables by:
alter table  TABLE_NAME DISABLE CONSTRAINT CONSTRAINTNAME;
truncate table TABLE_NAME ;
alter table TABLE_NAME enable CONSTRAINT CONSTRAINTNAME;
 
3. try step 1 to check if the top segments have been removed and high water mark is lower.

 Reducing high water mark like 100K blocks lower let me be able to bootstrap a new branch and my database run for a good time. 

Run this sql commands and check if there is free space or not in your database.

1. select sum(nvl(bytes,0))/1024/1024/1024 Size_GB from user_free_space;
2. SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

• If there is no space follow below link to remove unnecessary spec from DB.

https://community.onenetwork.com/sdk-forum/Question-1795/#post-3671

• If you can see some space and still getting 12 GB space issue then it’s a datafile issue and you can follow steps as mentioned in the below article.

https://www.funoracleapps.com/2020/09/query-to-shrink-datafiles-and-reclaim.html