{"id":631,"date":"2014-06-15T10:31:52","date_gmt":"2014-06-15T10:31:52","guid":{"rendered":"http:\/\/localhost\/__sites\/3d_blog\/?p=631"},"modified":"2014-06-24T19:21:41","modified_gmt":"2014-06-24T19:21:41","slug":"find-the-monsters","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=631","title":{"rendered":"find the monsters"},"content":{"rendered":"<blockquote><p>Goal: how to find the big tables in the oracle database<\/p><\/blockquote>\n<p>Just a quick way to localize the monsters in the database. Of course you need dba rights.<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n  select e.owner || '.' || e.SEGMENT_NAME obj, e.*\r\n    from sys.DBA_EXTENTS e\r\n   where e.SEGMENT_TYPE = 'TABLE'\r\n     and owner = 'SCHEMA_NAME_HERE' -- or comment this line\r\norder by e.BYTES desc\r\n<\/pre>\n<p>If you see the monsters, you can start thinking about the solution.<br \/>\nAnd the indexes:<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n  select e.owner || '.' || e.SEGMENT_NAME obj, e.*\r\n    from sys.DBA_EXTENTS e\r\n   where e.SEGMENT_TYPE = 'INDEX'\r\n     and owner = 'SCHEMA_NAME_HERE' -- or comment this line\r\norder by e.BYTES desc\r\n<\/pre>\n<p>more selects:<br \/>\n1. list of schema vs. size<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n  select e.owner, to_char(sum(e.bytes),'999G999G999G999') total_size\r\n    from sys.DBA_EXTENTS e\r\ngroup by e.owner\r\norder by total_size desc\r\n<\/pre>\n<p>2. list of object vs. size<\/p>\n<pre data-enlighter-language=\"sql\" class=\"EnlighterJSRAW\">\r\n  select e.owner || '.' || e.SEGMENT_NAME obj,\r\n         to_char(sum(e.bytes),'999G999G999G999') total_size\r\n    from sys.DBA_EXTENTS e\r\ngroup by e.owner,e.SEGMENT_NAME\r\norder by total_size desc\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Goal: how to find the big tables in the oracle database Just a quick way to localize the monsters in the database. Of course you need dba rights. If you see the monsters, you can start thinking about the solution. And the indexes: more selects: 1. list of schema vs. size 2. list of object [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-631","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=631"}],"version-history":[{"count":12,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/631\/revisions"}],"predecessor-version":[{"id":695,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/631\/revisions\/695"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}