{"id":4113,"date":"2025-06-08T02:00:07","date_gmt":"2025-06-08T02:00:07","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4113"},"modified":"2026-09-26T13:11:04","modified_gmt":"2026-09-26T07:41:04","slug":"check-database-status","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/check-database-status\/","title":{"rendered":"Check Database Status"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Use the following SQL queries to get key information about the Oracle database and instance status.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. <strong>Detailed Database &amp; Instance Info<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>SET PAGESIZE 50\nSET LINESIZE 200 \nSET VERIFY OFF\nBREAK ON REPORT SKIP 1\nCOLUMN section_heading FORMAT A35\nCOLUMN section_data FORMAT A35\n-- Database Information Section\nSELECT 'DATABASE INFORMATION' AS section_heading, NULL AS section_data FROM dual\nUNION ALL\nSELECT NULL, NULL FROM dual  -- Blank line after heading\nUNION ALL\nSELECT 'Database Name:', db.name FROM v$database db\nUNION ALL\nSELECT 'Unique Name:', db.db_unique_name FROM v$database db\nUNION ALL\nSELECT 'Creation Date:', TO_CHAR(db.created, 'DD-MON-YYYY HH24:MI:SS') FROM v$database db\nUNION ALL\nSELECT 'Open Mode:', db.open_mode FROM v$database db\nUNION ALL\nSELECT 'Log Mode:', db.log_mode FROM v$database db\nUNION ALL\nSELECT 'Database Role:', db.database_role FROM v$database db\nUNION ALL\nSELECT 'Character Set:', \n       (SELECT value FROM v$parameter WHERE name = 'nls_character_set') FROM dual\nUNION ALL\nSELECT 'NCHAR Character Set:', \n       (SELECT value FROM v$parameter WHERE name = 'nls_nchar_character_set') FROM dual\nUNION ALL\nSELECT NULL, NULL FROM dual  -- Blank line after section\n-- Instance Information Section\nUNION ALL\nSELECT 'INSTANCE INFORMATION' AS section_heading, NULL AS section_data FROM dual\nUNION ALL\nSELECT NULL, NULL FROM dual  -- Blank line after heading\nUNION ALL\nSELECT 'Instance Name:', inst.instance_name FROM v$instance inst\nUNION ALL\nSELECT 'Host Name:', inst.host_name FROM v$instance inst\nUNION ALL\nSELECT 'Startup Time:', TO_CHAR(inst.startup_time, 'DD-MON-YYYY HH24:MI:SS') FROM v$instance inst\nUNION ALL\nSELECT 'Instance Status:', inst.status FROM v$instance inst\nUNION ALL\nSELECT 'Database Status:', inst.database_status FROM v$instance inst\nUNION ALL\nSELECT 'Logins Allowed:', inst.logins FROM v$instance inst\nUNION ALL\nSELECT 'Version:', inst.version FROM v$instance inst\nUNION ALL\nSELECT NULL, NULL FROM dual  -- Blank line after section\n;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>2. Compact Summary<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>set lines 200 pages 200\ncol name for a15\ncol db_unique_name for a15\ncol open_mode for a15\ncol log_mode for a15\ncol logins for a15\ncol instance_name for a15\ncol HOST_NAME for a15\nalter session set nls_date_format='DD\/MM\/YYYY HH24:MI:SS';\nselect name,db_unique_name,created,open_mode,log_mode,logins,instance_name,database_role,host_name,startup_time from v$instance,v$database;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udccc Notes:<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use these queries to quickly assess DB health &amp; configuration.<\/li>\n\n\n\n<li>Modify date\/time formats as needed with <code>ALTER SESSION<\/code>.<\/li>\n\n\n\n<li>Can be run via SQL*Plus, SQLcl, or any SQL interface.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Use the following SQL queries to get key information about the Oracle database and instance status. 1. Detailed Database &amp; Instance Info 2. Compact Summary \ud83d\udccc Notes:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225,984],"tags":[],"class_list":["post-4113","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4113"}],"version-history":[{"count":3,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4113\/revisions"}],"predecessor-version":[{"id":4118,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4113\/revisions\/4118"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4113"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}