Today, I tried to get a nice, clean report about objects with reclaimable space from Segment Advisor. It is no problem to display the list in Enterprise Manager Grid|DB Control, but it is not so easy in SQL*Plus.<\/p>\n
This is what i ended up with:<\/p>\n
\n\r\n SELECT\r\n segment_owner ,\r\n segment_name,\r\n round(allocated_space\/1024\/1024) ALLOC_MB ,\r\n round(used_space\/1024\/1024) USED_MB ,\r\n round(reclaimable_space\/1024\/1024) RECLAIM_MB ,\r\n (1-ROUND((used_space\/allocated_space),2))*100 AS reclaim_pct\r\n FROM TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))\r\n WHERE tablespace_name IN ('TS_DATA')\r\nAND segment_type = 'TABLE'\r\nAND segment_owner LIKE '%'\r\nAND segment_name LIKE '%'\r\nAND (reclaimable_space >= 1000000\r\n OR (((1-ROUND((used_space\/allocated_space),2))*100)) > 30)\r\nORDER BY reclaimable_space DESC\r\n<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"Today, I tried to get a nice, clean report about objects with reclaimable space from Segment Advisor. It is no problem to display the list in Enterprise Manager Grid|DB Control, but it is not so easy in SQL*Plus. This is what i ended up with: SELECT segment_owner , segment_name, round(allocated_space\/1024\/1024) ALLOC_MB , round(used_space\/1024\/1024) USED_MB , […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,5,6,34],"tags":[],"class_list":["post-674","post","type-post","status-publish","format-standard","hentry","category-10g","category-oracle-database","category-oracle-enterprise-manager","category-performance-tuning-oracle-database-2"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/674","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=674"}],"version-history":[{"count":6,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/674\/revisions"}],"predecessor-version":[{"id":680,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/674\/revisions\/680"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=674"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}