{"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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_hide_featured_image":false,"_jetpack_featured_image_duplicate_dismissed":0,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[22],"tags":[],"class_list":["post-13","post","type-post","status-publish","format-standard","hentry","category-scripts-tools"],"acf":[],"jetpack-related-posts":[{"id":8,"url":"https:\/\/bijoos.com\/oraclenotes\/2012\/8\/","url_meta":{"origin":13,"position":0},"title":"SYSAUX Tablespace Growth &#8211; Review and Fix","author":"Biju","date":"November 7, 2012","format":false,"excerpt":"Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database... so need to make sure the contents are reviewed before adding more space... Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":195,"url":"https:\/\/bijoos.com\/oraclenotes\/2013\/195\/","url_meta":{"origin":13,"position":1},"title":"A PL\/SQL Program &#8211; After many many years!","author":"Biju","date":"August 2, 2013","format":false,"excerpt":"Wrote a little pl\/sql program after several years... glad it worked as expected... thought of sharing the code, might be useful for someone out there... The requirement is to drop tables that are older than 60 days from a schema named CR_BKUP periodically. Did not want to just drop the\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1473,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/1473\/","url_meta":{"origin":13,"position":2},"title":"Oracle 12c &#8211; READ Privilege","author":"Biju","date":"December 23, 2014","format":false,"excerpt":"The READ privilege is new in Oracle Database 12c 12.1.0.2. Why do we need a READ privilege when there is SELECT privilege? Well, the SELECT privilege includes few more privilege than a pure read-only privilege! With the SELECT privilege, in addition to reading (or \u201cselecting\u201d from) the table, you are\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":421,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/421\/","url_meta":{"origin":13,"position":3},"title":"DBA_TAB_COLUMNS vs DBA_TAB_COLS","author":"Biju","date":"June 18, 2014","format":false,"excerpt":"Not sure how many DBAs have noticed the difference between DBA_TAB_COLUMNS view and DBA_TAB_COLS view. Both views look pretty identical, if you are not paying too much attention. According to Oracle documentation, DBA_TAB_COLS describes the columns of all tables, views, and clusters in the database. This view differs from \"DBA_TAB_COLUMNS\"\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14,"url":"https:\/\/bijoos.com\/oraclenotes\/2011\/14\/","url_meta":{"origin":13,"position":4},"title":"Script to Grant Privileges and Create Synonyms","author":"Biju","date":"October 24, 2011","format":false,"excerpt":"Extending the previous post\u00a0Granting privileges on all or multiple objects to user\/role\u00a0, here is another script.Often we have a requirement to create read only accounts for a schema. Typically these accounts are named _QUERY. The script when executed will prompt for the user name of the read only account, and\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":352,"url":"https:\/\/bijoos.com\/oraclenotes\/2014\/352\/","url_meta":{"origin":13,"position":5},"title":"Two Cool Utilities in DBMS_UTILITY","author":"Biju","date":"May 18, 2014","format":false,"excerpt":"Oracle Database DBMS_UTILITY package has several useful subprograms. GET_DEPENDENCY shows the objects depended on the object. Here is an example (the procedure uses DBMS_OUTPUT to show the result, hence SET SERVEROUTPUT is required in SQL*Plus): SQL> set serveroutput on SQL> exec dbms_utility.get_dependency('VIEW','HR','EMP_DETAILS_VIEW'); - DEPENDENCIES ON HR.EMP_DETAILS_VIEW ------------------------------------------------------------------ *VIEW HR.EMP_DETAILS_VIEW() PL\/SQL\u2026","rel":"","context":"In &quot;Oracle Database&quot;","block_context":{"text":"Oracle Database","link":"https:\/\/bijoos.com\/oraclenotes\/category\/oracledb\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_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}]}}