{"id":62,"date":"2008-03-31T13:14:33","date_gmt":"2008-03-31T17:14:33","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/03\/31\/oracle-database-health-checks\/"},"modified":"2008-03-31T13:14:33","modified_gmt":"2008-03-31T17:14:33","slug":"oracle-database-health-checks","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/03\/31\/oracle-database-health-checks\/","title":{"rendered":"Oracle database health checks"},"content":{"rendered":"<p>Checking for any down data files:<\/p>\n<pre class=\"code\">\nselect file_name, tablespace_name, status, bytes\/1000000 as mb, maxbytes\/1000000 as mb_max \nfrom dba_data_files \nwhere status<>'AVAILABLE';\n<\/pre>\n<p>Checking for any down control files:<\/p>\n<pre class=\"code\">\nselect name, status\nfrom v$controlfile \nwhere status is not null;\n<\/pre>\n<p>Checking for any down tablespaces:<\/p>\n<pre class=\"code\">\nselect tablespace_name, status, contents, extent_management\nfrom dba_tablespaces\nwhere status<>'ONLINE';\n<\/pre>\n<p>Checking to see if a tablespace is getting close to max size (less than 10% free):<\/p>\n<pre class=\"code\">\nselect total.name as tblspace,\n  nvl(free_space, 0) as free,\n  nvl(total_space-free_space, 0) as used,\n  total_space,\n  round(nvl(free_space,0)\/total_space,2)*100 as pct_free\nfrom \n  (\n    select tablespace_name, sum(bytes\/1024\/1024) as free_Space \n    from sys.dba_free_space \n    group by tablespace_name\n  ) free, \n  (\n    select b.name, sum(bytes\/1024\/1024) as total_space\n    from sys.v_$datafile a, sys.v_$tablespace b \n    where a.ts# = b.ts#\n    group by b.name\n  ) total \nwhere free.tablespace_name(+) = total.name \nand total.name='TABLESPACE_NAME_HERE' \nand round(nvl(free_space, 0)\/total_space,2)<=0.10;\n<\/pre>\n<p>Suspicious of someone stealing your system passwords and logging in remotely?  Use this to check for system logins from machines you do not expect.<\/p>\n<pre class=\"code\">\nselect username, osuser, program, machine \nfrom sys.v_$session \nwhere upper(machine) in('LIST_VALID_MACHINES_HERE','DBA_PC','BOSS_PC') \nand upper(username) in('SYS','SYSTEM');\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The statements in this note provides some samples on checking the health status of an Oracle database.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-62","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/62","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=62"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/62\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=62"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=62"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=62"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}