Managing an Oracle database requires easy access to critical information for performance tuning, troubleshooting, and monitoring. In this post, we\u2019ll show you how to retrieve detailed database and instance information with a simple SQL query. This query provides essential details like the database name, creation date, and status in an organized format.<\/p>\n\n\n\n
The query pulls data from Oracle’s dynamic performance views (v$database and v$instance) to present the information clearly.<\/p>\n\n\n\n
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\nExplanation of the Query:<\/h2>\n\n\n\n
This query retrieves detailed database and instance information in two distinct sections:<\/p>\n\n\n\n
\n- Database Information Section<\/strong>: This section pulls vital data about the Oracle database, such as its name, unique name, creation date, open mode, log mode, and character set.<\/li>\n\n\n\n
- Instance Information Section<\/strong>: This section provides details about the instance running the Oracle database, including the instance name, host name, startup time, instance status, database status, logins allowed, and version.<\/li>\n<\/ol>\n\n\n\n
Each section is clearly labeled with a heading (e.g., “DATABASE INFORMATION” and “INSTANCE INFORMATION”) followed by the relevant details in the subsequent rows.<\/p>\n\n\n\n
Example Output:<\/h3>\n\n\n\n
Running the above query will produce the following output format:<\/p>\n\n\n\n
SECTION_HEADING SECTION_DATA\n----------------------------------- -----------------------------------\nDATABASE INFORMATION\n\nDatabase Name: ORCL\nUnique Name: orcl\nCreation Date: 29-AUG-2023 01:42:36\nOpen Mode: READ WRITE\nLog Mode: NOARCHIVELOG\nDatabase Role: PRIMARY\nCharacter Set:\nNCHAR Character Set:\n\nINSTANCE INFORMATION\n\nInstance Name: orcl\nHost Name: LAPTOP-8NSE56T7\nStartup Time: 15-DEC-2024 18:16:17\nInstance Status: OPEN\nDatabase Status: ACTIVE\nLogins Allowed: ALLOWED\nVersion: 19.0.0.0.0\n\n21 rows selected.<\/code><\/pre>\n\n\n\nOr you can use below query:<\/p>\n\n\n\n
set lines 200 pages 200\ncol name for a15\ndb_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 Tablespace |DB_UNIQUE_NAME |CREATED |OPEN_MODE |LOG_MODE |LOGINS |INSTANCE_NAME |DATABASE_ROLE |HOST_NAME |STARTUP_TIME\n---------------|----------------|-------------------|---------------|---------------|--------- |---------------|----------------------|---------------|-------------------\nORCL |orcl |29\/08\/2023 01:42:36|READ WRITE |NOARCHIVELOG |ALLOWED |orcl |PRIMARY |LAPTOP-8NSE83T7|15\/12\/2024 18:16:17<\/code><\/pre>\n\n\n\nConclusion:<\/h3>\n\n\n\n
In Oracle, being able to quickly gather essential database and instance information is crucial for routine maintenance, troubleshooting, and monitoring. This SQL query provides a clean and effective way to display such information in a structured format, saving you time when you need to assess your Oracle environment. By using v$database<\/code> and v$instance<\/code>, this query can help ensure you have all the important details right at your fingertips.<\/p>\n","protected":false},"excerpt":{"rendered":"Managing an Oracle database requires easy access to critical information for performance tuning, troubleshooting, and monitoring. In this post, we\u2019ll show you how to retrieve detailed database and instance information with a simple SQL query. This query provides essential details like the database name, creation date, and status in an organized format. The query pulls […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-530","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/530","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=530"}],"version-history":[{"count":5,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/530\/revisions"}],"predecessor-version":[{"id":1519,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/530\/revisions\/1519"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}