Datasize of all doc versions in CS 10 except for the latest 20 version of each doc?

Hi,

My need is to find the datasize of all the document versions inside content server 10 except for the latest 20 version of each document.

Example : We have 1000 documents and each has 25 versions, so I need to find sum of datasize of all the documents for the oldest 5 versions only (need not to count the latest 20 version datasize).

I am able to write a query which will result out all the versions except for the latest 20 -

--------------------------------------------------------------

SELECT *
FROM (
SELECT datasize, versionid, docid, version, rownum as rn
FROM dversdata where docid = 12345
ORDER BY version desc
)
where rn > 20;

----------------------------------------------------------------

The above query is giving out all the version information of a particular document except for the latest 20.

I need help to sum up the datasize and also to get the datasize of all the documnet inside CS 10.

Find more posts tagged with

Comments

Colin_Schmidt

To get a sum is simple, but the query below reports the value for each version.

IF you want everything, the entire number of bytes for all documents, all versions, it’ll take a while, but it’s not much different.

Select to_char(sum(dv.datasize), ‘999,999,999,999,999’) “TotalBytes”

from dtree dt

join dversdata dv on dt.dataid = dv.docid

where dt.subtype = 144

This would include the RecycleBin, as well. You could leave off the “Where dt.subtype = 144” since anything that has a DVERSDATA record will have “size” to it and it’ll take it all in. You could separate it by Subtype to find out how much are documents versus everything else. You don’t need the formatting of the value; it’s for looks.

Select dt.subtype, to_char(sum(dv.datasize), ‘999,999,999,999,999’) “TotalBytes”

from dtree dt

join dversdata dv on dt.dataid = dv.docid

where dt.subtype = 144

group by dt.subtype

order by dt.subtype

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, August 23, 2016 7:19 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Datasize of all doc versions in CS 10 except for the latest 20 version of each doc?