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.