Database Uptime in Seconds (vopensec.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Database Uptime in Seconds

This script calculates the precise database uptime in seconds using the thread open time, providing an exact measure of how long the database has been running.

Script: vopensec.sql

select ( sysdate - to_date( open_time, 'mm/dd/yy hh24:mi:ss' )  ) * 60 * 60 * 24 open_seconds
  from v$thread
/

What This Script Does

This script provides precise database uptime calculation by:

• Using Thread Open Time: Leverages v$thread.open_time for accuracy
• Seconds Precision: Returns uptime in seconds for exact calculations
• Simple Calculation: Direct date arithmetic for current uptime
• RAC Aware: Works with both single instance and RAC environments

Usage

-- Get database uptime in seconds
@vopensec.sql

-- Convert to more readable formats
SELECT
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as seconds,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60, 1) as minutes,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24, 2) as hours,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 3) as days
FROM v$thread;

Required Privileges

• SELECT privilege on V$THREAD
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

OPEN_SECONDS
------------
    86423.5

-- For multiple instances (RAC):
OPEN_SECONDS
------------
    86423.5
    86421.2
    86419.8

Understanding the Output

Single Instance

• Returns one row with uptime in seconds
• Represents time since database startup

RAC Environment

• Returns one row per instance
• Each row shows uptime for that specific instance
• Slight differences indicate instance restart times

Enhanced Usage Examples

1. Formatted Uptime Display

-- Human-readable uptime format
SELECT
  thread#,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds,
  TRUNC((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'))) || ' days ' ||
  TRUNC(MOD((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24, 24)) || ' hours ' ||
  TRUNC(MOD((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60, 60)) || ' minutes'
  as formatted_uptime
FROM v$thread;

2. Uptime with Instance Information

-- Detailed instance uptime
SELECT
  i.instance_name,
  i.host_name,
  t.thread#,
  TO_DATE(t.open_time, 'mm/dd/yy hh24:mi:ss') as startup_time,
  ROUND((SYSDATE - TO_DATE(t.open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds,
  ROUND((SYSDATE - TO_DATE(t.open_time, 'mm/dd/yy hh24:mi:ss')), 2) as uptime_days
FROM v$thread t, v$instance i
WHERE t.thread# = i.thread#;

3. Uptime Thresholds and Alerts

-- Check if database needs restart (example: > 30 days)
SELECT
  CASE
    WHEN (SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) > 30 THEN 'RESTART_RECOMMENDED'
    WHEN (SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) > 14 THEN 'MONITOR_CLOSELY'
    ELSE 'OK'
  END as status,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2) as uptime_days,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds
FROM v$thread;

Common Use Cases

1. Performance Baseline Calculations

-- Calculate rates per second since startup
SELECT
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds,
  s.name,
  s.value,
  ROUND(s.value / ((SYSDATE - TO_DATE(t.open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60), 2) as per_second
FROM v$sysstat s, v$thread t
WHERE s.name IN ('user calls', 'parse count (total)', 'execute count')
ORDER BY s.name;

2. Monitoring Script Integration

-- Uptime check for monitoring systems
SELECT
  'UPTIME_SECONDS=' || ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) ||
  ';UPTIME_DAYS=' || ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2) ||
  ';STATUS=' || CASE
    WHEN (SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) < 1/24 THEN 'RECENT_START'
    WHEN (SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) > 30 THEN 'LONG_RUNNING'
    ELSE 'NORMAL'
  END as monitoring_output
FROM v$thread;

3. Maintenance Window Planning

-- Calculate when database was last restarted
SELECT
  TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS') as last_startup,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2) as days_since_restart,
  CASE
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '1' THEN 'Sunday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '2' THEN 'Monday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '3' THEN 'Tuesday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '4' THEN 'Wednesday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '5' THEN 'Thursday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '6' THEN 'Friday'
    WHEN TO_CHAR(TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss'), 'D') = '7' THEN 'Saturday'
  END as startup_day_of_week
FROM v$thread;

RAC-Specific Queries

1. Compare Instance Uptimes

-- Find instance uptime differences in RAC
SELECT
  thread#,
  TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss') as startup_time,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) -
    MIN(ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60))
    OVER () as seconds_behind_oldest
FROM v$thread
ORDER BY uptime_seconds DESC;

2. RAC Instance Availability Report

-- RAC uptime summary
SELECT
  COUNT(*) as total_instances,
  MIN(ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2)) as shortest_uptime_days,
  MAX(ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2)) as longest_uptime_days,
  AVG(ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2)) as avg_uptime_days
FROM v$thread;

Performance Calculations

1. Database Activity Rates

-- Calculate activity per second since startup
WITH uptime AS (
  SELECT ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as seconds
  FROM v$thread
  WHERE ROWNUM = 1
)
SELECT
  s.name,
  s.value as total_count,
  u.seconds as uptime_seconds,
  ROUND(s.value / u.seconds, 2) as per_second_rate
FROM v$sysstat s, uptime u
WHERE s.name IN (
  'user calls',
  'parse count (total)',
  'execute count',
  'physical reads',
  'physical writes',
  'redo size'
)
ORDER BY s.value DESC;

2. Memory Usage Over Time

-- SGA usage efficiency over uptime
SELECT
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60) as uptime_seconds,
  ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2) as uptime_days,
  ROUND(SUM(bytes)/1024/1024) as sga_size_mb
FROM v$thread, v$sgainfo
WHERE name = 'Total SGA Size'
GROUP BY open_time;

Automation and Monitoring

1. Uptime Monitoring Function

-- Create function for uptime in seconds
CREATE OR REPLACE FUNCTION get_db_uptime_seconds RETURN NUMBER AS
  uptime_sec NUMBER;
BEGIN
  SELECT ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60)
  INTO uptime_sec
  FROM v$thread
  WHERE ROWNUM = 1;

  RETURN uptime_sec;
END;
/

-- Usage: SELECT get_db_uptime_seconds() FROM dual;

2. Uptime Alert Procedure

-- Create alert for long-running databases
CREATE OR REPLACE PROCEDURE check_database_uptime(p_threshold_days NUMBER DEFAULT 30) AS
  uptime_days NUMBER;
  uptime_seconds NUMBER;
BEGIN
  SELECT
    ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')), 2),
    ROUND((SYSDATE - TO_DATE(open_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60 * 60)
  INTO uptime_days, uptime_seconds
  FROM v$thread
  WHERE ROWNUM = 1;

  IF uptime_days > p_threshold_days THEN
    DBMS_OUTPUT.PUT_LINE('ALERT: Database uptime (' || uptime_days || ' days) exceeds threshold');
    DBMS_OUTPUT.PUT_LINE('Uptime in seconds: ' || uptime_seconds);
    DBMS_OUTPUT.PUT_LINE('Consider scheduling maintenance restart');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Database uptime: ' || uptime_days || ' days (Normal)');
  END IF;
END;
/

Integration with Other Scripts

This script works well with:

• Database Health Check (db.sql) - For comprehensive database status
• Instance Information (gvinst.sql) - For RAC instance details
• System Statistics (sysmetric_cpu.sql) - For performance calculations
• Session Analysis (gvsess.sql) - For current session analysis

Best Practices

1. Regular Monitoring: Include uptime in regular health checks
2. Rate Calculations: Use for calculating per-second performance metrics
3. Maintenance Planning: Track uptime for maintenance window planning
4. RAC Awareness: In RAC environments, check all instance uptimes
5. Baseline Establishment: Use startup time as baseline for performance measurements

This simple but essential script provides the foundation for many database monitoring and performance calculation activities.