{"id":547,"date":"2015-06-19T04:39:26","date_gmt":"2015-06-19T04:39:26","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=547"},"modified":"2015-06-19T05:40:03","modified_gmt":"2015-06-19T05:40:03","slug":"schema-size","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=547","title":{"rendered":"Schema size"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nTotal schema size<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nCOLUMN TABLE_NAME FORMAT A32\r\nCOLUMN OBJECT_NAME FORMAT A32\r\nCOLUMN OWNER FORMAT A10\r\n\r\nselect sum(Meg) total_size_in_MB from \r\n(\r\nSELECT\r\n   owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg\r\nFROM\r\n(SELECT segment_name table_name, owner, bytes\r\n FROM dba_segments\r\n WHERE segment_type = 'TABLE'\r\n UNION ALL\r\n SELECT i.table_name, i.owner, s.bytes\r\n FROM dba_indexes i, dba_segments s\r\n WHERE s.segment_name = i.index_name\r\n AND   s.owner = i.owner\r\n AND   s.segment_type = 'INDEX'\r\n UNION ALL\r\n SELECT l.table_name, l.owner, s.bytes\r\n FROM dba_lobs l, dba_segments s\r\n WHERE s.segment_name = l.segment_name\r\n AND   s.owner = l.owner\r\n AND   s.segment_type = 'LOBSEGMENT'\r\n UNION ALL\r\n SELECT l.table_name, l.owner, s.bytes\r\n FROM dba_lobs l, dba_segments s\r\n WHERE s.segment_name = l.index_name\r\n AND   s.owner = l.owner\r\n AND   s.segment_type = 'LOBINDEX')\r\nWHERE owner in UPPER('&owner')\r\nGROUP BY table_name, owner\r\nHAVING SUM(bytes)\/1024\/1024 > 1  \/* Ignore really small tables *\/\r\nORDER BY SUM(bytes) desc\r\n)\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nCOLUMN TABLE_NAME FORMAT A32<br \/>\nCOLUMN OBJECT_NAME FORMAT A32<br \/>\nCOLUMN OWNER FORMAT A10<\/p>\n<p>select sum(Meg) total_size_in_MB from<br \/>\n(<br \/>\nSELECT<br \/>\n   owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg<br \/>\nFROM<br \/>\n(SELECT segment_name table_name, owner, bytes<br \/>\n FROM dba_segments<br \/>\n WHERE segment_type = 'TABLE'<br \/>\n UNION ALL<br \/>\n SELECT i.table_name, i.owner, s.bytes<br \/>\n FROM dba_indexes i, dba_segments s<br \/>\n WHERE s.segment_name = i.index_name<br \/>\n AND   s.owner = i.owner<br \/>\n AND   s.segment_type = 'INDEX'<br \/>\n UNION ALL<br \/>\n SELECT l.table_name, l.owner, s.bytes<br \/>\n FROM dba_lobs l, dba_segments s<br \/>\n WHERE s.segment_name = l.segment_name<br \/>\n AND   s.owner = l.owner<br \/>\n AND   s.segment_type = 'LOBSEGMENT'<br \/>\n UNION ALL<br \/>\n SELECT l.table_name, l.owner, s.bytes<br \/>\n FROM dba_lobs l, dba_segments s<br \/>\n WHERE s.segment_name = l.index_name<br \/>\n AND   s.owner = l.owner<br \/>\n AND   s.segment_type = 'LOBINDEX')<br \/>\nWHERE owner in UPPER('&owner')<br \/>\nGROUP BY table_name, owner<br \/>\nHAVING SUM(bytes)\/1024\/1024 > 1  \/* Ignore really small tables *\/<br \/>\nORDER BY SUM(bytes) desc<br \/>\n)<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nSchema size (grouped by tables)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nCOLUMN TABLE_NAME FORMAT A32\r\nCOLUMN OBJECT_NAME FORMAT A32\r\nCOLUMN OWNER FORMAT A10\r\n\r\nSELECT\r\n   owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg\r\nFROM\r\n(SELECT segment_name table_name, owner, bytes\r\n FROM dba_segments\r\n WHERE segment_type = 'TABLE'\r\n UNION ALL\r\n SELECT i.table_name, i.owner, s.bytes\r\n FROM dba_indexes i, dba_segments s\r\n WHERE s.segment_name = i.index_name\r\n AND   s.owner = i.owner\r\n AND   s.segment_type = 'INDEX'\r\n UNION ALL\r\n SELECT l.table_name, l.owner, s.bytes\r\n FROM dba_lobs l, dba_segments s\r\n WHERE s.segment_name = l.segment_name\r\n AND   s.owner = l.owner\r\n AND   s.segment_type = 'LOBSEGMENT'\r\n UNION ALL\r\n SELECT l.table_name, l.owner, s.bytes\r\n FROM dba_lobs l, dba_segments s\r\n WHERE s.segment_name = l.index_name\r\n AND   s.owner = l.owner\r\n AND   s.segment_type = 'LOBINDEX')\r\nWHERE owner in UPPER('&owner')\r\nGROUP BY table_name, owner\r\nHAVING SUM(bytes)\/1024\/1024 > 1  \/* Ignore really small tables *\/\r\nORDER BY SUM(bytes) desc\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nCOLUMN TABLE_NAME FORMAT A32<br \/>\nCOLUMN OBJECT_NAME FORMAT A32<br \/>\nCOLUMN OWNER FORMAT A10<\/p>\n<p>SELECT<br \/>\n   owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg<br \/>\nFROM<br \/>\n(SELECT segment_name table_name, owner, bytes<br \/>\n FROM dba_segments<br \/>\n WHERE segment_type = 'TABLE'<br \/>\n UNION ALL<br \/>\n SELECT i.table_name, i.owner, s.bytes<br \/>\n FROM dba_indexes i, dba_segments s<br \/>\n WHERE s.segment_name = i.index_name<br \/>\n AND   s.owner = i.owner<br \/>\n AND   s.segment_type = 'INDEX'<br \/>\n UNION ALL<br \/>\n SELECT l.table_name, l.owner, s.bytes<br \/>\n FROM dba_lobs l, dba_segments s<br \/>\n WHERE s.segment_name = l.segment_name<br \/>\n AND   s.owner = l.owner<br \/>\n AND   s.segment_type = 'LOBSEGMENT'<br \/>\n UNION ALL<br \/>\n SELECT l.table_name, l.owner, s.bytes<br \/>\n FROM dba_lobs l, dba_segments s<br \/>\n WHERE s.segment_name = l.index_name<br \/>\n AND   s.owner = l.owner<br \/>\n AND   s.segment_type = 'LOBINDEX')<br \/>\nWHERE owner in UPPER('&owner')<br \/>\nGROUP BY table_name, owner<br \/>\nHAVING SUM(bytes)\/1024\/1024 > 1  \/* Ignore really small tables *\/<br \/>\nORDER BY SUM(bytes) desc<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Total schema size COLUMN TABLE_NAME FORMAT A32 COLUMN OBJECT_NAME FORMAT A32 COLUMN OWNER FORMAT A10 select sum(Meg) total_size_in_MB from ( SELECT owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type = &#8216;TABLE&#8217; UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":237,"menu_order":0,"comment_status":"closed","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-547","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-8P","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=547"}],"version-history":[{"count":3,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/547\/revisions"}],"predecessor-version":[{"id":550,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/547\/revisions\/550"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/237"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}