Notifications
Clear all

How to Reclaim Diskspace used by Oracle XE Database?

   RSS

2
Topic starter

How to Reclaim Diskspace used by Oracle XE Database?

5 Answers
2

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)

@GregMerrill
I am trying to run "ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM_DATA.DBF' resize 150M;" but getting following error

Error starting at line : 9 in command -
ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\SYSTEM_DATA.DBF' resize 150M
Error report -
ORA-03297: file contains used data beyond requested RESIZE value
03297. 00000 - "file contains used data beyond requested RESIZE value"
*Cause: Some portion of the file in the region to be trimmed is
currently in use by a database object
*Action: Drop or move segments containing extents in this region prior to
resizing the file, or choose a resize value such that only free
space is in the trimmed.

 

Could you please help?

@sshriwastav did you already drop all schemas? Because it sounds like those tablespaces contain too much real data to be reduced to 150M. If you have not already dropped all schemas, you must complete that first. If you've already done that, perhaps you can try resizing to something slightly larger than 150M

Finally - I notice you are on Oracle 11. Strongly recommend you update to 18. See https://portal.onenetwork.com/pages/viewpage.action?pageId=86704263&searchId=6T6QIGYOX

0
Topic starter

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
Topic starter

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.

0

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.

0

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

1. find the high water mark:
  log in with servicename:xepdb1 and user SYSTEM, run

select * from dba_extents
order by block_id desc

  Get the top list of names of the database table or indexes.  If it is index, find the table names by running
    SELECT INDEX_NAME, OWNER,TABLE_NAME
  FROM all_indexes
  WHERE INDEX_NAME='IDX_NAME';

2. truncate those tables

If the top list includes unused database, drop the database. 
Otherwise,  log in to the user database of each of the top tables.  
  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)='TABLE1')
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 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. 

 

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