CODEWITHSUNDEEP
sql-serveroracle-database
krishna kumar
krishna kumar

Reputation: 33

Oracle and SQL Server query

Our team is developing basic monitoring tool for SQL Server and Oracle. We are now looking for this output in Oracle.

{
        "name": "InfraData",
        "time": "2018-01-04T19:49:07.838Z",
        "timeInMin": "2018-01-04T19:49:00Z",
        "sampleRate": 0,
        "iKey": "sq05058e-89te-50eb-a701-70e1f892191g",
        "data_baseType": "Sql_Performance",
        "machine_name": "HPSTSS02",
        "ip_address": "10.0.2.37",
        "logged_in_user": "Administrator",
        "agent_version": "VS212017121d0",
        "output_type": "latencydata",
        "database_name": "CABS_TEST",
        "sql_Database_DB_FileType": "DATA File - Location C:~Program Files~Microsoft SQL Server~MSSQL12.MSSQLSERVER~MSSQL~DATA~CABS_TEST.mdf",
        "sql_Database_DB_readLatency": 7,
        "sql_Database_DB_writeLatency": 0,
        "sql_Database_DB_Latency": 7,
        "sql_db_num_of_reads": 455,
        "sql_db_num_of_writes": 1,
        "sql_db_size_on_disk_bytes": 536870912,
        "id": "c0cd6c53-7869-44c8-8bd6-11a1b0724cce",
        "_version_": 1588682190333411300,
        "timestamp": "2018-01-04T17:03:21.839Z"
      },

I made a query in oracle using some subquery but there are several bugs, can someone help me with this. I am totally lost.

column GET_HOST_NAME format a25
column GET_HOST_ADDRESS format a20  
select name,sysdate, to_char(sysdate,'HH24:MI:SS AM') as timeInMin,
select sum(SAMPLESIZE) as sampleRate from IND$,
(select max(banner) from v$version) as data_baseType,
(SELECT UTL_INADDR.get_host_name from dual) as machine_name,(select UTL_INADDR.get_host_address from dual) as ip_address,
(select user from dual) as logged_in_user, (select name from v$database) as database_name,
(select FILE_NAME sql_Database_DB_FileType from dba_data_files where FILE_ID = (select min(FILE#) from sys.file$)) as sql_Database_DB_FileType,
(select max(READ_LATENCY) as oracle_Database_DB_readLatency ,max(WRITE_LATENCY) as sql_Database_DB_writeLatency from V_$IOSTAT_NETWORK),
(select (sum(READ_LATENCY)+sum(WRITE_LATENCY)) as sql_Database_DB_Latency from V_$IOSTAT_NETWORK)  from V_$IOSTAT_NETWORK,dual,v$database;
select sum(PHYSICAL_READS_TOTAL) as Oracle_db_num_of_reads,sum(PHYSICAL_WRITES_TOTAL) as Oracle_db_num_of_writes from DBA_HIST_SEG_STAT;
select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"  from dual;

Upvotes: 0

Views: 123

Answers (1)

Brian Leach
Brian Leach

Reputation: 2101

This is really a horrible way to gather this data, but here is your SQL modified to work. I substituted 'USER' for 'NAME' on the first line because I had no idea what name you wanted.

SELECT USER
     , SYSDATE
     , TO_CHAR (SYSDATE, 'HH24:MI:SS AM')
           AS timeinmin
     , (SELECT SUM (samplesize)
          FROM ind$)
           AS samplerate
     , (SELECT MAX (banner)
          FROM v$version)
           AS data_basetype
     , (SELECT UTL_INADDR.get_host_name
          FROM DUAL)
           AS machine_name
     , (SELECT UTL_INADDR.get_host_address
          FROM DUAL)
           AS ip_address
     , (SELECT USER
          FROM DUAL)
           AS logged_in_user
     , (SELECT name
          FROM v$database)
           AS database_name
     , (SELECT file_name sql_database_db_filetype
          FROM dba_data_files
         WHERE file_id = (SELECT MIN (file#)
                            FROM sys.file$))
           AS sql_database_db_filetype
     , (SELECT MAX (read_latency) AS oracle_database_db_readlatency
          FROM v_$iostat_network)
     , (SELECT MAX (write_latency) AS sql_database_db_writelatency
          FROM v_$iostat_network)
     , (SELECT (SUM (read_latency) + SUM (write_latency)) AS sql_database_db_latency
          FROM v_$iostat_network)
  FROM DUAL;

SELECT SUM (physical_reads_total) AS oracle_db_num_of_reads, SUM (physical_writes_total) AS oracle_db_num_of_writes
  FROM dba_hist_seg_stat;

SELECT   (SELECT SUM (bytes) / 1024 / 1024 / 1024 data_size
            FROM dba_data_files)
       + (SELECT NVL (SUM (bytes), 0) / 1024 / 1024 / 1024 temp_size
            FROM dba_temp_files)
       + (SELECT SUM (bytes) / 1024 / 1024 / 1024 redo_size
            FROM sys.v_$log)
       + (SELECT SUM (block_size * file_size_blks) / 1024 / 1024 / 1024 controlfile_size
            FROM v$controlfile)
           "Size in GB"
  FROM DUAL;

Upvotes: 1

Related Questions