[Deploying Sakai] Finding large files and their respective owners

[Leon Kolchinsky]

Hello John,

Thanks for your help.
It's working ;)

As a consequence we decided that it would be a good idea to migrate to a FS
storage in the near future.

Thanks again for your help,
Cheers,
Leon



On Thu, Oct 28, 2010 at 01:54, John Leasia <jleasia at umich.edu> wrote:

>  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/20101101/0c33dc29/attachment.html