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?
Best answer by Michael Biedermann
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!
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!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
