CODEWITHSUNDEEP
sqloracle-databasemetadata
Tom C
Tom C

Reputation: 125

Oracle system information query - Database instance level

I am writing a performance/system monitoring tool to augment load testing for my team's product and I am trying to store database system information with the results bundle but do not know how to write the query to capture this in Oracle (I'm a developer not a DBA).

I have this all working the way I want for SQL Server, but I need to do the same for Oracle. Below is a query I found online for this is SQL Server:

SELECT CONVERT(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS 'computerNamePhysicalNetBIOS', 
CONVERT(varchar(128),SERVERPROPERTY('MachineName')) AS 'machineName', 
CONVERT(varchar(128),SERVERPROPERTY('Edition')) AS 'edition', 
CONVERT(varchar(128),SERVERPROPERTY('ProductLevel')) AS 'productLevel', 
CONVERT(varchar(128),SERVERPROPERTY('ProductVersion')) AS 'productVersion', 
CONVERT(varchar(128),SERVERPROPERTY('BuildClrVersion')) AS 'buildClrVersion', 
CONVERT(INT,SERVERPROPERTY('ProcessID')) AS 'processID', 
CONVERT(INT,SERVERPROPERTY('EngineEdition')) AS 'engineEdition', 
CONVERT(INT,SERVERPROPERTY('HadrManagerStatus')) AS 'hadrManagerStatus', 
CONVERT(INT,SERVERPROPERTY('IsHadrEnabled')) AS 'hadrEnabled', 
CONVERT(INT,SERVERPROPERTY('IsAdvancedAnalyticsInstalled')) AS 'advancedAnalyticsInstalled', 
CONVERT(INT,SERVERPROPERTY('IsClustered')) AS 'clustered', 
CONVERT(INT,SERVERPROPERTY('IsPolybaseInstalled')) AS 'polybaseInstalled', 
CONVERT(INT,SERVERPROPERTY('IsXTPSupported')) AS 'xtpSupported', 
CONVERT(INT,SERVERPROPERTY('LCID')) AS 'lcid', 
CONVERT(varchar(128),SERVERPROPERTY('ResourceVersion')) AS 'resourceVersion', 
CONVERT(varchar(128),SERVERPROPERTY('ServerName')) AS 'serverName', 
CONVERT(varchar(128),APP_NAME() )AS 'appName', 
CONVERT(INT,DB_ID()) AS 'dbId', 
CONVERT(varchar(128),DB_NAME()) AS 'dbName'

I don't really expect a one-to-one column match between the above query and Oracle's version, but in general, how can I get very similar information from Oracle?

Upvotes: 0

Views: 1870

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17934

I don't really expect a one-to-one column match between the above query and Oracle's version, but in general, how can I get very similar information from Oracle?

Most of that stuff, if it exists at all in the Oracle database, will be accessible through V$ views in the Oracle database. To get you started, here are some that are going to be most relevant to answering your question:

select * from v$instance;

select * from v$version;

select * from v$sql_feature;

select * from v$license;

select * from v$option;

If you want to get a complete list of V$ views to look around better,

select * from dict where table_name like 'V$%';

Upvotes: 3

APC
APC

Reputation: 146239

Some of those things are specific to MSSQL and have no meaning in Oracle. But you can get many of them with sys_context() using the userenv namespace.

For instance, to get the database name:

select sys_context('userenv', 'DB_NAME') as db_name
from dual;

Upvotes: 3

Related Questions