How to Reclaim Diskspace used by Oracle XE Database?
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
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.
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.
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
if segment_type is 'LOBSEGMENT', find the table name by
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
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.