{"id":13,"date":"2011-10-26T05:24:00","date_gmt":"2011-10-26T05:24:00","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=13"},"modified":"2011-10-26T05:24:00","modified_gmt":"2011-10-26T05:24:00","slug":"sql-script-to-find-the-top-50-tables-by-size","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2011\/13\/","title":{"rendered":"SQL Script to find the top 50 tables by size"},"content":{"rendered":"<div dir=\"ltr\">Here is a SQL to identify the top 50 tables. When I say table, it could be a table, partitioned table or materialized view. Takes into consideration the indexes defined on the table and lob segments in the table, thus gives a complete picture of the table size. When you truncate the table, this much would be the space released&#8230;<\/p>\n<pre class=\"lang:default decode:true  crayon-selected\">\nselect top50.owner, top50.table_name, meg, a.num_rows\u00a0\nfrom dba_tables a,\u00a0\n\u00a0 \u00a0(Select * from (\nSELECT\n\u00a0 \u00a0owner, table_name, TRUNC(sum(bytes)\/1024\/1024) Meg\nFROM\n(SELECT segment_name table_name, owner, bytes\n\u00a0FROM dba_segments\n\u00a0WHERE segment_type like 'TABLE%'\n\u00a0UNION ALL\n\u00a0SELECT i.table_name, i.owner, s.bytes\n\u00a0FROM dba_indexes i, dba_segments s\n\u00a0WHERE s.segment_name = i.index_name\n\u00a0AND \u00a0 s.owner = i.owner\n\u00a0AND \u00a0 s.segment_type like 'INDEX%'\n\u00a0UNION ALL\n\u00a0SELECT l.table_name, l.owner, s.bytes\n\u00a0FROM dba_lobs l, dba_segments s\n\u00a0WHERE s.segment_name = l.segment_name\n\u00a0AND \u00a0 s.owner = l.owner\n\u00a0AND \u00a0 s.segment_type = 'LOBSEGMENT'\n\u00a0UNION ALL\n\u00a0SELECT l.table_name, l.owner, s.bytes\n\u00a0FROM dba_lobs l, dba_segments s\n\u00a0WHERE s.segment_name = l.index_name\n\u00a0AND \u00a0 s.owner = l.owner\n\u00a0AND \u00a0 s.segment_type = 'LOBINDEX')\nGROUP BY table_name, owner\nHAVING SUM(bytes)\/1024\/1024 &gt; 10 \u00a0\/* Ignore small tables *\/\nORDER BY SUM(bytes) desc\n) where rownum &lt; 51) top50\nwhere top50.owner =a.owner\nand top50.table_name = a.table_name\norder by meg desc, num_rows desc;<\/pre>\n<p>Enjoy!<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Here is a SQL to identify the top 50 tables. When I say table, it could be a table, partitioned table or materialized view. Takes into consideration the indexes defined on the table and lob segments in the table, thus gives a complete picture of the table size. When you truncate the table, this much &#8230; <a title=\"SQL Script to find the top 50 tables by size\" class=\"read-more\" href=\"https:\/\/bijoos.com\/oraclenotes\/2011\/13\/\" aria-label=\"Read more about SQL Script to find the top 50 tables by size\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-scripts-tools"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/13","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":0,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}