Notifications
Clear all

How to Reclaim Diskspace used by Oracle XE Database?  

   RSS

2

How to Reclaim Diskspace used by Oracle XE Database?

3 Answers
1

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;

@GregMerrill
FYI - I just attempted this today (2020-10-17) and it worked well for me. Other strategies like just dropping schemas didn't help, but after dropping all PLT schemas and following the instructions above, my DB recovered without a re-install. (This was on Oracle XE 11)

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;
    
0

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.

© 2020 One Network Enterprises. All rights reserved. Privacy Policy