{"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":"2025-06-08T02:05:12","modified_gmt":"2025-06-08T02:05:12","slug":"check-database-status","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/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},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4113","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4113","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}