tag:help-archives.hannonhill.com,2010-02-09:/discussions/how-do-i/497-oracle-rapid-database-growthCascade CMS: Discussion 2012-01-10T15:56:09Ztag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-08-31T17:59:23Z2011-08-31T17:59:23ZOracle - Rapid Database Growth<div><p>Doris,</p>
<blockquote>
<p>Our suspicion is we might have content contributors uploading
either huge images or even videos.</p>
</blockquote>
<p>This is most likely the case for this rapid growth, as log files
and other files that are not content are <em>not</em> stored within
the database. All binary data is stored within the "cxml_blob"
table, so this is where you will find such files. As for a query,
please try the following:</p>
<pre>
<code>SELECT id, OCTET_LENGTH(data) FROM cxml_blob order by OCTET_LENGTH(data);</code>
</pre>
<p>Thanks!</p></div>Joeltag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-08-31T18:11:03Z2011-08-31T18:11:03ZOracle - Rapid Database Growth<div><p>In addition to what Joel mentioned above, you'll also want to
make sure that you have your <strong>Max Asset Versions</strong>
preference (in System Menu -> Content Preferences) set to a
finite number (rather than 0 for unlimited). This is especially
important if you happen to have Web Services scripts that trigger
edits for numerous assets on a daily basis.</p></div>Timtag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-08-31T19:33:08Z2011-08-31T19:33:08ZOracle - Rapid Database Growth<div><p>OCTET_LENGTH is not valid for ORACLE it is a MSSQL command</p></div>uta-cms-supporttag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-08-31T21:02:02Z2011-08-31T21:02:02ZOracle - Rapid Database Growth<div><p>Doris,</p>
<p>Please try the query below instead:</p>
<pre>
<code>SELECT id, dbms_lob.getLength(data) FROM YouDatabaseNameHere.cxml_blob order by dbms_lob.getLength(data) DESC;</code>
</pre>
<p>Thanks!</p></div>Joeltag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-09-30T13:55:05Z2011-09-30T13:55:05ZOracle - Rapid Database Growth<div><p>Hi Joel,</p>
<p>I'm not sure what the attached information is giving us?</p>
<p>Thanks - Doris</p></div>uta-cms-supporttag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-09-30T15:26:44Z2011-09-30T15:26:44ZOracle - Rapid Database Growth<div><p>Doris,</p>
<p>Sorry, that probably wasn't the best way to list that data. The
query below will list the binary files within your database from
largest to smallest.</p>
<pre>
<code>SELECT f.cachePath, b.id, dbms_lob.getLength(data) FROM dbNameHere.cxml_blob b join dbNameHere.cxml_foldercontent f on b.id=f.fileBlobId order by dbms_lob.getLength(data) DESC nulls last;</code>
</pre>
<p>Thanks!</p></div>Joeltag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-09-30T15:45:09Z2011-09-30T15:45:09ZOracle - Rapid Database Growth<div><p>Joel,</p>
<p>As an interested but lazy observer, can you modify the query to
list the site associated with each file?</p></div>rrhilltag:help-archives.hannonhill.com,2010-02-09:Comment/96647422011-10-03T18:49:20Z2011-10-03T18:49:20ZOracle - Rapid Database Growth<div><p>Sure thing Doris, please try the query below.</p>
<pre>
<code>SELECT s.name as site_name, f.cachePath, b.id, dbms_lob.getLength(data) FROM dbNameHere.cxml_blob b join dbNameHere.cxml_foldercontent f on b.id=f.fileBlobId join dbNameHere.cxml_site s on s.id = f.siteid order by dbms_lob.getLength(data) DESC nulls last;</code>
</pre>
<p>Thanks!</p></div>Joel