alexsusanu@docs:Oracle LIMS Monitoring Scripts $
alexsusanu@docs
:~$ cat Oracle LIMS Monitoring Scripts.md

Home → ORACLE → Oracle LIMS Monitoring Scripts

Oracle LIMS Monitoring Scripts

PowerShell functions for monitoring Oracle database systems in LIMS environments, including connection testing, performance monitoring, and health checks.

Prerequisites

Required Oracle Components:
- Oracle Data Access Components (ODAC) or Oracle Client
- Oracle.DataAccess.dll or Oracle.ManagedDataAccess.dll
- Appropriate Oracle drivers

PowerShell Setup:

# Load Oracle .NET Data Provider
Add-Type -Path "C:\Oracle\ODTwithODAC\odp.net\managed\common\Oracle.ManagedDataAccess.dll"
# Or for unmanaged version:
# Add-Type -Path "C:\Oracle\product\client\ODP.NET\bin\4\Oracle.DataAccess.dll"

Oracle Connection and Health Functions

Test Oracle Connection for LIMS

#region Oracle Connection and Health
function Test-OracleForLIMS {
    param(
        [string]$ServerName = "localhost",
        [string]$Port = "1521",
        [string]$ServiceName = "ORCL",
        [string]$DatabaseName = "LIMS",
        [string]$Username = "lims_user",
        [string]$Password,
        [int]$TimeoutSeconds = 30
    )

    # Build Oracle connection string
    $connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ServerName)(PORT=$Port))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)));User Id=$Username;Password=$Password;Connection Timeout=$TimeoutSeconds;"

    Write-Host "Testing Oracle connection for LIMS..." -ForegroundColor Yellow
    Write-Host "Server: $ServerName`:$Port" -ForegroundColor Gray
    Write-Host "Service: $ServiceName" -ForegroundColor Gray
    Write-Host "Schema: $Username" -ForegroundColor Gray

    try {
        # Test basic connectivity
        $stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
        $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
        $connection.Open()

        # Get server information
        $command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand(@"
            SELECT 
                (SELECT BANNER FROM V`$VERSION WHERE ROWNUM = 1) AS VERSION,
                SYS_CONTEXT('USERENV','SERVER_HOST') AS SERVER_HOST,
                SYS_CONTEXT('USERENV','DB_NAME') AS DATABASE_NAME,
                USER AS CURRENT_USER,
                SYSDATE AS SERVER_TIME,
                SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INSTANCE_NAME
            FROM DUAL
"@, $connection)

        $reader = $command.ExecuteReader()
        $serverInfo = @{}
        if ($reader.Read()) {
            $serverInfo = @{
                Version = $reader["VERSION"].ToString()
                ServerHost = $reader["SERVER_HOST"].ToString()
                DatabaseName = $reader["DATABASE_NAME"].ToString()
                User = $reader["CURRENT_USER"].ToString()
                ServerTime = $reader["SERVER_TIME"]
                InstanceName = $reader["INSTANCE_NAME"].ToString()
            }
        }
        $reader.Close()

        # Test a simple query performance
        $testCommand = New-Object Oracle.ManagedDataAccess.Client.OracleCommand("SELECT COUNT(*) FROM USER_TABLES", $connection)
        $testResult = $testCommand.ExecuteScalar()

        $stopwatch.Stop()
        $connection.Close()

        $result = [PSCustomObject]@{
            Status = "Success"
            Server = "$ServerName`:$Port"
            ServiceName = $ServiceName
            Schema = $Username
            ResponseTimeMs = $stopwatch.ElapsedMilliseconds
            OracleVersion = $serverInfo.Version
            ServerHost = $serverInfo.ServerHost
            DatabaseName = $serverInfo.DatabaseName
            ConnectedAs = $serverInfo.User
            ServerTime = $serverInfo.ServerTime
            InstanceName = $serverInfo.InstanceName
            TableCount = $testResult
            Timestamp = Get-Date
        }

        Write-Host "✓ Connection successful" -ForegroundColor Green
        Write-Host "Response time: $($stopwatch.ElapsedMilliseconds)ms" -ForegroundColor Gray
        Write-Host "Connected as: $($serverInfo.User)" -ForegroundColor Gray
        Write-Host "Server time: $($serverInfo.ServerTime)" -ForegroundColor Gray
        Write-Host "Instance: $($serverInfo.InstanceName)" -ForegroundColor Gray

        return $result
    }
    catch {
        Write-Host "✗ Connection failed" -ForegroundColor Red
        Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red

        # Provide specific guidance for common Oracle errors
        if ($_.Exception.Message -like "*ORA-01017*") {
            Write-Host "Hint: Invalid username/password - check credentials" -ForegroundColor Yellow
        }
        elseif ($_.Exception.Message -like "*ORA-12541*") {
            Write-Host "Hint: TNS:no listener - check Oracle listener service and firewall" -ForegroundColor Yellow
        }
        elseif ($_.Exception.Message -like "*ORA-12154*") {
            Write-Host "Hint: TNS:could not resolve service name - check service name or TNS configuration" -ForegroundColor Yellow
        }
        elseif ($_.Exception.Message -like "*timeout*") {
            Write-Host "Hint: Check network connectivity and Oracle database responsiveness" -ForegroundColor Yellow
        }

        return [PSCustomObject]@{
            Status = "Failed"
            Server = "$ServerName`:$Port"
            ServiceName = $ServiceName
            Schema = $Username
            Error = $_.Exception.Message
            Timestamp = Get-Date
        }
    }
}

Get Oracle LIMS Performance Data

function Get-OracleLIMSPerformance {
    param(
        [string]$ServerName = "localhost",
        [string]$Port = "1521",
        [string]$ServiceName = "ORCL",
        [string]$Username = "lims_user",
        [string]$Password,
        [string]$ConnectionString
    )

    if (-not $ConnectionString) {
        $ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$ServerName)(PORT=$Port))(CONNECT_DATA=(SERVICE_NAME=$ServiceName)));User Id=$Username;Password=$Password;Connection Timeout=30;"
    }

    $performanceQueries = @{
        "DatabaseInfo" = @"
            SELECT 
                NAME AS DATABASE_NAME,
                DBID,
                CREATED,
                LOG_MODE,
                OPEN_MODE,
                DATABASE_ROLE,
                PLATFORM_NAME
            FROM V`$DATABASE
"@

        "TablespaceUsage" = @"
            SELECT 
                TS.TABLESPACE_NAME,
                ROUND(TS.TOTAL_SIZE_MB, 2) AS TOTAL_SIZE_MB,
                ROUND(TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0), 2) AS USED_SIZE_MB,
                ROUND(NVL(FS.FREE_SIZE_MB, 0), 2) AS FREE_SIZE_MB,
                ROUND(((TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0)) / TS.TOTAL_SIZE_MB) * 100, 2) AS PERCENT_USED
            FROM 
                (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_SIZE_MB 
                 FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) TS
            LEFT JOIN 
                (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_SIZE_MB 
                 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS
            ON TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
            ORDER BY PERCENT_USED DESC
"@

        "ActiveSessions" = @"
            SELECT 
                PROGRAM,
                MACHINE,
                USERNAME,
                COUNT(*) AS SESSION_COUNT,
                COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) AS ACTIVE_COUNT,
                COUNT(CASE WHEN STATUS = 'INACTIVE' THEN 1 END) AS INACTIVE_COUNT
            FROM V`$SESSION
            WHERE USERNAME IS NOT NULL
            GROUP BY PROGRAM, MACHINE, USERNAME
            ORDER BY SESSION_COUNT DESC
"@

        "WaitEvents" = @"
            SELECT 
                EVENT,
                TOTAL_WAITS,
                ROUND(TIME_WAITED_MICRO/1000000, 2) AS TIME_WAITED_SECONDS,
                ROUND(AVERAGE_WAIT, 2) AS AVERAGE_WAIT_MS,
                ROUND((TIME_WAITED_MICRO/1000000) / (SELECT SUM(TIME_WAITED_MICRO/1000000) FROM V`$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL*Net%') * 100, 2) AS PCT_WAIT_TIME
            FROM V`$SYSTEM_EVENT
            WHERE EVENT NOT LIKE 'SQL*Net%'
            AND EVENT NOT LIKE '%idle%'
            AND TIME_WAITED_MICRO > 0
            ORDER BY TIME_WAITED_MICRO DESC
            FETCH FIRST 10 ROWS ONLY
"@

        "BlockingSessions" = @"
            SELECT 
                w.SESSION_ID AS WAITING_SESSION,
                h.SESSION_ID AS HOLDING_SESSION,
                w.ORACLE_USERNAME AS WAITING_USER,
                h.ORACLE_USERNAME AS HOLDING_USER,
                o.OBJECT_NAME,
                w.MODE_REQUESTED,
                h.MODE_HELD,
                w.LOCK_TYPE,
                s1.PROGRAM AS WAITING_PROGRAM,
                s2.PROGRAM AS HOLDING_PROGRAM,
                s1.MACHINE AS WAITING_MACHINE,
                s2.MACHINE AS HOLDING_MACHINE
            FROM DBA_WAITERS w
            JOIN DBA_BLOCKERS h ON w.HOLDING_SESSION = h.HOLDING_SESSION
            LEFT JOIN DBA_OBJECTS o ON w.ROW_WAIT_OBJ# = o.OBJECT_ID
            LEFT JOIN V`$SESSION s1 ON w.SESSION_ID = s1.SID
            LEFT JOIN V`$SESSION s2 ON h.SESSION_ID = s2.SID
"@

        "LongRunningQueries" = @"
            SELECT 
                s.SID,
                s.USERNAME,
                s.MACHINE,
                s.PROGRAM,
                s.STATUS,
                ROUND((SYSDATE - s.LOGON_TIME) * 24 * 60, 2) AS SESSION_MINUTES,
                sql.SQL_TEXT,
                s.LAST_CALL_ET AS SECONDS_SINCE_LAST_CALL
            FROM V`$SESSION s
            LEFT JOIN V`$SQLTEXT sql ON s.SQL_ID = sql.SQL_ID AND sql.PIECE = 0
            WHERE s.USERNAME IS NOT NULL
            AND s.STATUS = 'ACTIVE'
            AND s.LAST_CALL_ET > 300  -- More than 5 minutes
            ORDER BY s.LAST_CALL_ET DESC
"@

        "TopSQLByElapsedTime" = @"
            SELECT 
                SQL_ID,
                EXECUTIONS,
                ROUND(ELAPSED_TIME/1000000, 2) AS ELAPSED_SECONDS,
                ROUND(ELAPSED_TIME/EXECUTIONS/1000000, 4) AS AVG_ELAPSED_SECONDS,
                ROUND(CPU_TIME/1000000, 2) AS CPU_SECONDS,
                BUFFER_GETS,
                DISK_READS,
                FIRST_LOAD_TIME,
                LAST_ACTIVE_TIME,
                SUBSTR(SQL_TEXT, 1, 100) AS SQL_TEXT_PREVIEW
            FROM V`$SQLSTATS
            WHERE EXECUTIONS > 0
            AND SQL_TEXT NOT LIKE '%V`$%'
            ORDER BY ELAPSED_TIME DESC
            FETCH FIRST 10 ROWS ONLY
"@

        "DatabaseSize" = @"
            SELECT 
                'TOTAL DATABASE SIZE' AS METRIC,
                ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
            FROM DBA_DATA_FILES
            UNION ALL
            SELECT 
                'TOTAL LOG SIZE' AS METRIC,
                ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
            FROM V`$LOG
            UNION ALL
            SELECT 
                'TEMP FILES SIZE' AS METRIC,
                ROUND(SUM(BYTES)/1024/1024/1024, 2) AS SIZE_GB
            FROM DBA_TEMP_FILES
"@
    }

    $results = @{}
    Write-Host "Gathering Oracle performance data for LIMS..." -ForegroundColor Yellow

    try {
        $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($ConnectionString)
        $connection.Open()

        foreach ($query in $performanceQueries.GetEnumerator()) {
            Write-Host "Collecting $($query.Key)..." -ForegroundColor Cyan
            try {
                $command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($query.Value, $connection)
                $adapter = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
                $dataset = New-Object System.Data.DataSet
                $adapter.Fill($dataset) | Out-Null
                $results[$query.Key] = $dataset.Tables[0]
            }
            catch {
                Write-Warning "Failed to execute $($query.Key): $($_.Exception.Message)"
                $results[$query.Key] = $null
            }
        }

        $connection.Close()

        # Display key findings
        Write-Host "`nOracle Performance Summary:" -ForegroundColor Green

        # Database size
        if ($results["DatabaseSize"]) {
            $totalSizeGB = ($results["DatabaseSize"] | Where-Object {$_.METRIC -eq "TOTAL DATABASE SIZE"}).SIZE_GB
            Write-Host "Database size: $($totalSizeGB)GB" -ForegroundColor Cyan
        }

        # Tablespace usage
        if ($results["TablespaceUsage"]) {
            $maxUsage = ($results["TablespaceUsage"] | Measure-Object PERCENT_USED -Maximum).Maximum
            if ($maxUsage -gt 90) {
                Write-Host "⚠ WARNING: Tablespace usage up to $maxUsage%" -ForegroundColor Red
            } else {
                Write-Host "✓ Tablespace usage under control (max: $maxUsage%)" -ForegroundColor Green
            }
        }

        # Active sessions
        if ($results["ActiveSessions"]) {
            $totalSessions = ($results["ActiveSessions"] | Measure-Object SESSION_COUNT -Sum).Sum
            $activeSessions = ($results["ActiveSessions"] | Measure-Object ACTIVE_COUNT -Sum).Sum
            Write-Host "Sessions: $totalSessions total, $activeSessions active" -ForegroundColor Cyan
        }

        # Blocking sessions
        if ($results["BlockingSessions"] -and $results["BlockingSessions"].Rows.Count -gt 0) {
            Write-Host "⚠ WARNING: $($results["BlockingSessions"].Rows.Count) blocking sessions detected!" -ForegroundColor Red
        } else {
            Write-Host "✓ No blocking sessions detected" -ForegroundColor Green
        }

        # Long running queries
        if ($results["LongRunningQueries"] -and $results["LongRunningQueries"].Rows.Count -gt 0) {
            $maxRuntime = ($results["LongRunningQueries"] | Measure-Object SECONDS_SINCE_LAST_CALL -Maximum).Maximum
            Write-Host "⚠ WARNING: $($results["LongRunningQueries"].Rows.Count) long-running queries (max: $([math]::Round($maxRuntime/60,1)) minutes)" -ForegroundColor Yellow
        } else {
            Write-Host "✓ No long-running queries detected" -ForegroundColor Green
        }

        return $results
    }
    catch {
        Write-Error "Failed to gather Oracle performance data: $($_.Exception.Message)"
        return $null
    }
}

Generate Oracle LIMS Health Check Script

function New-OracleLIMSHealthScript {
    param(
        [string]$OutputPath = "C:\temp\oracle_lims_health.sql",
        [string]$SchemaName = "LIMS"
    )

    $sqlScript = @"
-- Oracle LIMS Health Check Script
-- Generated: $(Get-Date)
-- Schema: $SchemaName

SET PAGESIZE 100
SET LINESIZE 200
SET FEEDBACK ON

PROMPT ================================================
PROMPT ORACLE LIMS HEALTH CHECK
PROMPT Generated: $(Get-Date)
PROMPT ================================================

PROMPT
PROMPT 1. DATABASE INFORMATION
SELECT 
    NAME AS DATABASE_NAME,
    DBID,
    TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') AS CREATED,
    LOG_MODE,
    OPEN_MODE,
    DATABASE_ROLE,
    PLATFORM_NAME,
    TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS CURRENT_TIME
FROM V`$DATABASE;

PROMPT
PROMPT 2. INSTANCE INFORMATION
SELECT 
    INSTANCE_NAME,
    HOST_NAME,
    VERSION,
    STATUS,
    STARTUP_TIME,
    DATABASE_STATUS
FROM V`$INSTANCE;

PROMPT
PROMPT 3. TABLESPACE USAGE
SELECT 
    TS.TABLESPACE_NAME,
    ROUND(TS.TOTAL_SIZE_MB, 2) AS TOTAL_MB,
    ROUND(TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0), 2) AS USED_MB,
    ROUND(NVL(FS.FREE_SIZE_MB, 0), 2) AS FREE_MB,
    ROUND(((TS.TOTAL_SIZE_MB - NVL(FS.FREE_SIZE_MB, 0)) / TS.TOTAL_SIZE_MB) * 100, 2) AS PCT_USED
FROM 
    (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_SIZE_MB 
     FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) TS
LEFT JOIN 
    (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_SIZE_MB 
     FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FS
ON TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
ORDER BY PCT_USED DESC;

PROMPT
PROMPT 4. ACTIVE SESSIONS BY PROGRAM
SELECT 
    PROGRAM,
    USERNAME,
    COUNT(*) AS SESSION_COUNT,
    COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) AS ACTIVE_COUNT
FROM V`$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY PROGRAM, USERNAME
HAVING COUNT(*) > 2
ORDER BY SESSION_COUNT DESC;

PROMPT
PROMPT 5. BLOCKING SESSIONS (CRITICAL IF ANY)
SELECT COUNT(*) AS BLOCKING_COUNT FROM DBA_BLOCKERS;

SELECT 
    w.SESSION_ID AS WAITING_SID,
    h.SESSION_ID AS HOLDING_SID,
    w.ORACLE_USERNAME AS WAITING_USER,
    h.ORACLE_USERNAME AS HOLDING_USER,
    o.OBJECT_NAME,
    w.MODE_REQUESTED,
    h.MODE_HELD
FROM DBA_WAITERS w
JOIN DBA_BLOCKERS h ON w.HOLDING_SESSION = h.HOLDING_SESSION
LEFT JOIN DBA_OBJECTS o ON w.ROW_WAIT_OBJ# = o.OBJECT_ID
WHERE ROWNUM <= 10;

PROMPT
PROMPT 6. LONG RUNNING SESSIONS (>5 MINUTES ACTIVE)
SELECT 
    SID,
    USERNAME,
    MACHINE,
    PROGRAM,
    STATUS,
    ROUND(LAST_CALL_ET/60, 2) AS MINUTES_SINCE_LAST_CALL,
    LOGON_TIME
FROM V`$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
AND LAST_CALL_ET > 300
ORDER BY LAST_CALL_ET DESC;

PROMPT
PROMPT 7. TOP WAIT EVENTS
SELECT 
    EVENT,
    TOTAL_WAITS,
    ROUND(TIME_WAITED_MICRO/1000000, 2) AS TIME_WAITED_SEC,
    ROUND(AVERAGE_WAIT, 2) AS AVG_WAIT_MS
FROM V`$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL*Net%'
AND EVENT NOT LIKE '%idle%'
AND TIME_WAITED_MICRO > 0
AND ROWNUM <= 10
ORDER BY TIME_WAITED_MICRO DESC;

PROMPT
PROMPT 8. DATABASE ALERTS (V`$ALERT_HISTORY)
SELECT 
    TO_CHAR(ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') AS ALERT_TIME,
    MESSAGE_TYPE,
    MESSAGE_LEVEL,
    HOST_ADDRESS,
    SUBSTR(MESSAGE, 1, 100) AS MESSAGE_PREVIEW
FROM V`$ALERT_HISTORY
WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1
ORDER BY ORIGINATING_TIMESTAMP DESC;

PROMPT
PROMPT 9. LIMS SCHEMA SPECIFIC CHECKS
-- Check for tables with no recent activity
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    LAST_ANALYZED,
    TRUNC(SYSDATE - LAST_ANALYZED) AS DAYS_SINCE_ANALYZED
FROM DBA_TABLES
WHERE OWNER = UPPER('$SchemaName')
AND (TABLE_NAME LIKE '%SAMPLE%' OR TABLE_NAME LIKE '%TEST%' OR TABLE_NAME LIKE '%RESULT%')
AND LAST_ANALYZED IS NOT NULL
ORDER BY DAYS_SINCE_ANALYZED DESC;

PROMPT
PROMPT 10. INVALID OBJECTS IN LIMS SCHEMA
SELECT 
    OBJECT_TYPE,
    OBJECT_NAME,
    STATUS,
    LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OWNER = UPPER('$SchemaName')
AND STATUS != 'VALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

PROMPT
PROMPT Health check complete. Review any issues above.
PROMPT Check completed at: 
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS COMPLETION_TIME FROM DUAL;
"@

    $sqlScript | Out-File -FilePath $OutputPath -Encoding UTF8
    Write-Host "Oracle LIMS health check script created: $OutputPath" -ForegroundColor Green
    Write-Host "Run this in SQL*Plus: @$OutputPath" -ForegroundColor Cyan
    Write-Host "Or from command line: sqlplus username/password@database @$OutputPath" -ForegroundColor Cyan
}
#endregion

Usage Examples

# Usage examples and quick commands
Write-Host @"

ORACLE LIMS MONITORING COMMANDS:
=================================

1. Test Oracle connection:
   Test-OracleForLIMS -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD" -Username "lims_user" -Password "your_password"

2. Get comprehensive performance data:
   `$perf = Get-OracleLIMSPerformance -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD" -Username "lims_user" -Password "your_password"
   `$perf["BlockingSessions"]    # View blocking sessions
   `$perf["LongRunningQueries"]  # View slow queries
   `$perf["TablespaceUsage"]     # View tablespace usage

3. Generate health check script:
   New-OracleLIMSHealthScript -SchemaName "LIMS_PROD"

4. Connection with TNS alias:
   Test-OracleForLIMS -ServerName "ORACLESERVER01" -ServiceName "LIMSPROD.domain.com" -Username "lims_user" -Password "your_password"

5. Quick tablespace check:
   `$perf = Get-OracleLIMSPerformance -ServerName "SERVER" -ServiceName "SID" -Username "user" -Password "pass"
   `$perf["TablespaceUsage"] | Where-Object {`$_.PERCENT_USED -gt 80}

"@ -ForegroundColor Green

Key Differences from SQL Server Version

Connection Changes

  • Oracle Connection String: Uses Oracle-specific format with HOST, PORT, SERVICE_NAME
  • Oracle .NET Provider: Uses Oracle.ManagedDataAccess.Client instead of System.Data.SqlClient
  • Authentication: Uses username/password instead of Windows Authentication

Query Changes

  • System Views: Uses Oracle's V$ views instead of SQL Server DMVs
  • Syntax: Oracle SQL syntax (e.g., DUAL, ROWNUM, SYSDATE)
  • Performance Metrics: Oracle-specific wait events and statistics

Monitoring Focus

  • Tablespaces: Oracle equivalent of SQL Server databases
  • Sessions: Oracle sessions instead of connections
  • Wait Events: Oracle wait events instead of wait stats
  • Blocking: Uses DBA_WAITERS/DBA_BLOCKERS instead of sys.dm_exec_requests

Installation Notes

  1. Install Oracle Client or Oracle Data Access Components (ODAC)
  2. Load .NET Provider in PowerShell session
  3. Configure TNS (optional) for easier connection management
  4. Set Environment Variables for Oracle Home if needed
  5. Test connectivity before running performance monitoring

This Oracle version provides equivalent functionality to the SQL Server script while using Oracle-specific commands, views, and best practices for LIMS monitoring.

Last updated: 2025-08-26 20:00 UTC