{"id":103,"date":"2019-02-15T17:13:55","date_gmt":"2019-02-15T16:13:55","guid":{"rendered":"http:\/\/oracledba.fr\/?p=103"},"modified":"2020-05-29T14:44:49","modified_gmt":"2020-05-29T12:44:49","slug":"recuperer-le-nombres-darchivelogs-generes","status":"publish","type":"post","link":"https:\/\/oracledba.fr\/?p=103","title":{"rendered":"List number of archivelogs"},"content":{"rendered":"\n<p>You can list the number of archivelogs generated this way:<\/p>\n\n\n\n<pre lang=\"sql\">select trunc(COMPLETION_TIME,'DDD') Day, SUM(BLOCKS * BLOCK_SIZE)\/1024\/1024 SIZE_MB, count(*) \"NB_Files\" \nfrom V$ARCHIVED_LOG \ngroup by trunc (COMPLETION_TIME,'DDD') order by 1;\n<\/pre>\n\n\n\n<!--more Lire la suite!-->\n\n\n\n<p>Per hour:<br><\/p>\n\n\n\n<pre lang=\"sql\">select \n  to_char(first_time,'DD-MM-YYYY') day, \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') \"00\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') \"01\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') \"02\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') \"03\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') \"04\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') \"05\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') \"06\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') \"07\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') \"08\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') \"09\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') \"10\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') \"11\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') \"12\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') \"13\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') \"14\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') \"15\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') \"16\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') \"17\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') \"18\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') \"19\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') \"20\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') \"21\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') \"22\", \n  to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') \"23\", \n  COUNT(*) TOT \nfrom v$log_history \nWhere first_time &gt; to_date('01\/02\/2019', 'DD\/MM\/YYYY')\ngroup by to_char(first_time,'DD-MM-YYYY')\norder by day ;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>You can list the number of archivelogs generated this way: select trunc(COMPLETION_TIME,&#8217;DDD&#8217;) Day, SUM(BLOCKS * BLOCK_SIZE)\/1024\/1024 SIZE_MB, count(*) &#8220;NB_Files&#8221; from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,&#8217;DDD&#8217;) order by 1;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[70,68],"class_list":["post-103","post","type-post","status-publish","format-standard","hentry","category-sql","tag-archivelogs","tag-oracle"],"_links":{"self":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=103"}],"version-history":[{"count":10,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions"}],"predecessor-version":[{"id":212,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/103\/revisions\/212"}],"wp:attachment":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}