Solved

Documents in Oracle Database

  • 9 July 2020
  • 2 replies
  • 436 views

Userlevel 1
Badge +4

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?

icon

Best answer by Michael Biedermann 30 July 2020, 10:00

View original

2 replies

Userlevel 6
Badge +14

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')

Userlevel 1
Badge +4

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