AnsweredAssumed Answered

How many big files there are in my Alfresco?

Question asked by luca on Sep 16, 2010
Hi,
I was stumbling in the Alfresco Database to make some statistics and I was trying to answer to this post title question.

I made this query to find documents greater than 100MB:

select t.* from (
select np.node_id, to_number(substring(string_value, 'size=*([0-9]{1,15})'), '999999999999999')/(1024*1024) as dimension, np.string_value
from alf_node_properties np, alf_qname qn
where qn.id = np.qname_id
and qn.local_name = 'content'
) as t
where t.dimension> 100
order by t.dimension desc;

N.B. this script is tested with Postgres 8 and in my environment it takes about 1 minute to run (we have about 50GB of repository)

I made this post principally to help anyone that has my same needs, but, just in case, is there some more efficent way?
Does anyone have other useful script to share?

Outcomes