[Deploying Sakai] Finding large files and their respective owners

[John Leasia]

Leon,
a problem is that the 'who uploaded' is in the =sakai_event and 
sakai_session tables which likely are very large, and so the query could 
be very long running and have a possible impact on users. If you weren't 
so interested in who uploaded, but just wanted to know which files it 
would be less  impact.
This might work
select cr.file_size, smap.eid, cr.resource_id
from content_resource cr, sakai_session ss, sakai_event se, 
sakai_user_id_map smap
where  ss.session_id = se.session_id
and se.ref = '/content' || cr.resource_id
and se.event = 'content.new'
and smap.user_id = ss.session_user
and cr.file_size > 1073741824

and if the who isn't important, faster would be
select cr.file_size,  cr.resource_id
from content_resource cr,  sakai_user_id_map smap
where  cr.file_size > 1073741824

Another way might be to find the large files and also get the site owner 
of the site they are in - that might be close enough to answer the 'who 
uploaded' question, and wouldn't need the sakai_session table. Maybe this
select file_size, createdby, resource_id
from content_resource, sakai_site
where context = site_id
and file_size > 1073741824

John

Leon Kolchinsky wrote:
> Hello All,
>
> We're looking for a way to find large files (let's say >1GB) stored in 
> our Sakai 2.5.4 DB (Oracle).
> Can someone provide me with SQL query I could run to find a list of 
> those files and their respective owners (users) which uploaded those 
> files into our Sakai instance.
>
> We're currently storing all content in the DB ant it's starting to be 
> really edgy.
> Users storing huge files in the DB and this is not a good practice.
>
> Thanks,
> Leon
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20101027/0dbd22c6/attachment.html