Skip to main content

We have our IFS on their managed cloud services and store our documents in the oracle database.

Obviously we have now already about 880 GB of document data in our database (tablespace IFSAPP_LOB) which means that the backup (and restore!) take very long.


We assume that there are a lot of obsolete documents and unoptimizied documents regarding their size. 
How can we analyze these documents regarding age and size?

Can check from a query in the database, similar to bellow

SELECT t.doc_class,
       t.doc_no,
       t.doc_rev,
       (dbms_lob.getlength(t.file_data)) / 1024 / 1024 AS file_size_MB,
       t.rowversion
  FROM EDM_FILE_STORAGE_TAB t, doc_issue_tab d
 WHERE t.doc_class = d.doc_class
   AND t.doc_no = d.doc_no
   AND t.doc_sheet = d.doc_sheet
   AND t.doc_rev = d.doc_rev
   AND d.rowstate in ('Obsolete')


thanks for your input. Since we cannot access the tables directly but only the views, I rewrote your statement:

SELECT t.doc_class,
       d.title,
       t.doc_no,
       t.doc_rev,
       (dbms_lob.getlength(t.file_data)) / 1024 / 1024 AS file_size_MB,
       t.objversion,
       d.doc_status
  FROM IFSAPP.EDM_FILE_STORAGE t, IFSAPP.doc_issue d
 WHERE t.doc_class = d.doc_class
   AND t.doc_no = d.doc_no
   AND t.doc_sheet = d.doc_sheet
   AND t.doc_rev = d.doc_rev;

 

This helps, thanks!


Reply