Reclaiming free space from Oracle
You may occasionally need to reclaim space from Oracle before additional storage can be provisioned.
A new monthly Oracle job reclaimLOBTablespaces has been incorporated which reclaims unused table space for APTARE_TBS_DATA_20M and APTARE_TBS_DATA_1M.
To complete the process successfully,
the portal user should have ALTER DATABASE privilege.
OR
the job should be disabled.
The following are the steps to enable the privileges:
su - aptare
sqlplus as sysdba
alter session set container = scdb;
grant alter database to PORTAL;
The following is the command to disable the job.
exec dbms_scheduler.disable('PORTAL.RECLAIMLOBTABLESPACES');
You can run the following script at any time to reclaim space. It examines every Oracle database file (DBF) for "white space" at the end of the file. If the script discovers more than 256 MB of white space, it re-sizes the DBF file to remove the trailing space. This white space is a result of many insertions and deletions; in addition, white space can occur if you have truncated tables or purged a lot of data.
Log in to the database server as aptare.
Go to the tools directory:
Linux: cd /opt/aptare/database/tools
Windows: cd C:\opt\oracle\database\tools
sqlplus / as sysdba @ reclaim_aptare_tablespace commit; exit