Reclaiming the space PSFILE_ATTDET

PeopleSoft table PSFILE_ATTDET is used to store attachment.

Non Production environments generally would not require these attachments.Since this table contains Long Objects, ORACLE DB creates SYS_LOB% tables.

At one of our client database was consuming almost 70G in attachments.

Below are the steps to free up this space and reclaim it.

1.  Enable Row movement before the truncate step.

ALTER TABLE SYSADM.PSFILE_ATTDET enable row movement;

2. Truncate table SYSADM.PSFILE_ATTDET

3. ALTER TABLE SYSADM.PSFILE_ATTDET SHRINK SPACE;

4. ALTER TABLE SYSADM.PSFILE_ATTDET MODIFY LOB(FILE_DATA) (SHRINK SPACE);

5. Alter the database to re-size the datafiles.
ALTER DATABASE DATAFILE '/psimage.dbf'


Some helpful queries

select sum(bytes)/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='PSIMAGE';