{"id":84,"date":"2017-08-13T23:48:21","date_gmt":"2017-08-13T23:48:21","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=84"},"modified":"2017-08-13T23:49:55","modified_gmt":"2017-08-13T23:49:55","slug":"database-change-sizes","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/13\/database-change-sizes\/","title":{"rendered":"Database Change Sizes"},"content":{"rendered":"\n<pre class=\"lang:default decode:true \" >SET SERVEROUTPUT ON;\r\nDECLARE\r\n   --Cursor Declaration\r\n   CURSOR c1\r\n   IS\r\n        SELECT SUM (ROUND ( (tablespace_usedsize * 8 * 1024) \/ 1024 \/ 1024, 2))\r\n                  AS \"used space\",\r\n               snap_id\r\n          FROM DBA_HIST_TBSPC_SPACE_USAGE\r\n         WHERE snap_id IN (SELECT snap_id\r\n                             FROM dba_hist_snapshot\r\n                            WHERE TO_CHAR (BEGIN_INTERVAL_TIME) BETWEEN '16\/10\/2011%18%'\r\n                                                                    AND '20\/10\/2011%18')\r\n      GROUP BY snap_id\r\n      ORDER BY snap_id ASC;\r\n   --Variable Declaretion\r\n   row_counter      NUMBER;\r\n   db_used_size_1   NUMBER;\r\n   db_used_size_2   NUMBER;\r\n   snap_time        TIMESTAMP;\r\n   total_change     NUMBER;\r\nBEGIN\r\n   row_counter := 0;\r\n   total_change := 0;\r\n   db_used_size_2 := 0;\r\n   db_used_size_1 := 0;\r\n   FOR emp_rec IN c1\r\n   LOOP\r\n      row_counter := row_counter + 1;\r\n      IF MOD (row_counter, 2) = 0\r\n      THEN\r\n         db_used_size_2 := emp_rec.\"used space\";\r\n      ELSE\r\n         db_used_size_1 := emp_rec.\"used space\";\r\n      END IF;\r\n      IF row_counter != 1\r\n      THEN\r\n         EXECUTE IMMEDIATE 'select begin_interval_time from dba_hist_snapshot where snap_id =' || emp_rec.snap_id INTO snap_time;\r\n         IF     db_used_size_2 - db_used_size_1 != 0\r\n            AND db_used_size_1 != 0\r\n            AND db_used_size_2 != 0\r\n         THEN\r\n            IF MOD (row_counter, 2) != 0\r\n            THEN\r\n               DBMS_OUTPUT.put_line (\r\n                     SUBSTR (TO_CHAR (snap_time),\r\n                             1,\r\n                             INSTR (snap_time, ',') - 1)\r\n                  || ' -&gt; '\r\n                  || TO_CHAR (db_used_size_1 - db_used_size_2)\r\n                  || 'MB'\r\n                  || '--&gt; %'\r\n                  || TO_CHAR (\r\n                        ROUND (\r\n                             100\r\n                           * (db_used_size_1 - db_used_size_2)\r\n                           \/ db_used_size_1,\r\n                           2)));\r\n               total_change :=\r\n                  total_change + (db_used_size_1 - db_used_size_2);\r\n            ELSE\r\n               DBMS_OUTPUT.put_line (\r\n                     SUBSTR (TO_CHAR (snap_time),\r\n                             1,\r\n                             INSTR (snap_time, ',') - 1)\r\n                  || ' -&gt; '\r\n                  || TO_CHAR (db_used_size_2 - db_used_size_1)\r\n                  || 'MB'\r\n                  || '--&gt; %'\r\n                  || TO_CHAR (\r\n                        ROUND (\r\n                             100\r\n                           * (db_used_size_2 - db_used_size_1)\r\n                           \/ db_used_size_1,\r\n                           2)));\r\n               total_change :=\r\n                  total_change + (db_used_size_2 - db_used_size_1);\r\n            END IF;\r\n         END IF;\r\n      END IF;\r\n   END LOOP;\r\n   DBMS_OUTPUT.put_line ('Total Change ' || TO_CHAR (total_change || ' MB'));\r\nEND;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>SET SERVEROUTPUT ON; DECLARE &#8211;Cursor Declaration CURSOR c1 IS SELECT SUM (ROUND ( (tablespace_usedsize * 8 * 1024) \/ 1024 \/ 1024, 2)) AS &#8220;used space&#8221;, snap_id FROM DBA_HIST_TBSPC_SPACE_USAGE WHERE snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE TO_CHAR (BEGIN_INTERVAL_TIME) BETWEEN &#8217;16\/10\/2011%18%&#8217; AND &#8217;20\/10\/2011%18&#8242;) GROUP BY snap_id ORDER BY snap_id ASC; &#8211;Variable Declaretion row_counter NUMBER; db_used_size_1 &#8230; <a title=\"Database Change Sizes\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/13\/database-change-sizes\/\" aria-label=\"Read more about Database Change Sizes\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[13,17],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-enterprise-manager","tag-capacity","tag-growth"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":2,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"predecessor-version":[{"id":86,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/84\/revisions\/86"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}