All the attachments in the file folders which are not referenced by any object in PLM, are considered as orphan files.
When you are using Agile PLM as your tool for Product Life Cycle Management, on one or the other day, you will definitely reach a point point where you will be running out of space on your database servers, at that point if you closely monitor your database you may find the following cases.
- The file attachments referred to the change orders which are old by decades.
- Large files ( 1000 MBs + size) attachments referred to items and the items are been obsolete for very long time
- Huge design files from the CAD systems are attached to the Agile objects which are never used.
- File attachments which are not referred to any objects in agile.
Working in high tech and semicon industries, we see that the large files referred to one or multiple objects can be identified easily by the advance search, since PLM is be validated system, recent decade records are not suggested to be removed from the PLM, if the industry is been using the system from a very long time and if the organization decided to to remove the data/files from the PLM system which are more than 10 years old, that can be easily achieved by the batch program ( will discuss this in another blog).
But the files which are not referred to any object in agile are hard to find.
In order to get rid of those files, the only option is to hit the database.
1. Run the script to list orphaned folder
||attachment_number||'”,”‘
||delete_flag||'”,”‘
||filename||'”,”‘
||file_size||'”‘
from
attachment a,
files f ,
version v,
version_file_map vfm
where (f.id=vfm.file_id)
and (vfm.version_id=v.id)
and (v.attach_id=a.id)
and not exists (select * from attachment_map m where m.attach_id = a.id )
and not exists (select * from relationship r where a.id = r.eff_objid)
and not exists (select * from relationship r where a.id = r.ctr_objid)
order by (a.attachment_number);
3. Run batch code which inputs the CSV file and reads the file folder number and hard delete the object from Agile.
The purge task will automatically move the vault files to the purge folder.
4. Archive the purged files before deleting if necessary. Purged files are no longer needed since they are not referenced in the schema.
