---
title: "Session Management Quick Reference"
---

# Session Management Quick Reference

Quick reference guide for HeliosDB-Lite session management and monitoring.

## Quick Commands

### View All Sessions

```sql
-- Generic (all protocols)
SELECT * FROM helios_sessions;

-- PostgreSQL style
SELECT * FROM pg_stat_activity;

-- Oracle style
SELECT * FROM v$session;
```

### View Active Queries

```sql
-- All active queries
SELECT session_id, username, current_query
FROM helios_sessions
WHERE state = 'active';

-- PostgreSQL style
SELECT pid, usename, query
FROM pg_stat_activity
WHERE state = 'active';

-- Oracle style
SELECT sid, username, sql_text
FROM v$session
WHERE status = 'ACTIVE';
```

### Count Sessions by Protocol

```sql
SELECT protocol, COUNT(*) AS session_count
FROM helios_sessions
GROUP BY protocol;
```

### Find Idle Sessions

```sql
-- Sessions idle for more than 5 minutes
SELECT session_id, username, last_activity
FROM helios_sessions
WHERE state = 'idle'
AND (CURRENT_TIMESTAMP - last_activity) > INTERVAL '5 minutes';
```

## Session States

| State | Description |
|-------|-------------|
| `active` | Currently executing a query |
| `idle` | Connected but not executing |
| `idle_in_transaction` | Idle within a transaction block |

## System Table Schema

```sql
CREATE TABLE helios_sessions (
    session_id INT8 PRIMARY KEY,
    protocol TEXT NOT NULL,
    username TEXT NOT NULL,
    client_address TEXT NOT NULL,
    client_port INT4 NOT NULL,
    connect_time TIMESTAMP NOT NULL,
    last_activity TIMESTAMP NOT NULL,
    current_query TEXT,
    state TEXT NOT NULL
);
```

## Python Client Examples

### PostgreSQL

```python
import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    database='heliosdb',
    user='myuser',
    password='mypass'
)

cursor = conn.cursor()

# View sessions
cursor.execute("SELECT * FROM pg_stat_activity")
sessions = cursor.fetchall()

# View my session
cursor.execute("SELECT pg_backend_pid()")
my_pid = cursor.fetchone()[0]
```

### Oracle

:::note[Availability]
The Oracle listener is planned for an upcoming HeliosDB-Lite release; current builds
serve the PostgreSQL wire protocol only. See the [Oracle Protocol Quick Reference](/docs/lite/guides/quickstart/oracle_quick_reference/).
:::

```python
import oracledb

conn = oracledb.connect(
    user='myuser',
    password='mypass',
    dsn='localhost:1521/heliosdb'
)

cursor = conn.cursor()

# View sessions
cursor.execute("SELECT * FROM v$session WHERE username IS NOT NULL")
sessions = cursor.fetchall()
```

## Monitoring Queries

### Session Activity Report

```sql
SELECT
    protocol,
    COUNT(*) AS total_sessions,
    SUM(CASE WHEN state = 'active' THEN 1 ELSE 0 END) AS active,
    SUM(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) AS idle
FROM helios_sessions
GROUP BY protocol;
```

### Top Users by Session Count

```sql
SELECT
    username,
    COUNT(*) AS session_count,
    MAX(last_activity) AS last_seen
FROM helios_sessions
GROUP BY username
ORDER BY session_count DESC
LIMIT 10;
```

### Long-Running Queries

```sql
SELECT
    session_id,
    username,
    current_query,
    (CURRENT_TIMESTAMP - last_activity) AS duration
FROM helios_sessions
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 10;
```

## Troubleshooting

### Problem: No sessions showing

**Check**: Is the server running?
```bash
nc -z localhost 5432  # PostgreSQL
nc -z localhost 1521  # Oracle
```

**Check**: Are connections authenticated?
```sql
SELECT COUNT(*) FROM helios_sessions;
```

### Problem: Sessions stuck in 'active'

**Solution**: Clear stale sessions
```sql
-- Identify stale sessions (active > 1 hour)
SELECT session_id, username, current_query
FROM helios_sessions
WHERE state = 'active'
AND (CURRENT_TIMESTAMP - last_activity) > INTERVAL '1 hour';
```

### Problem: Too many idle sessions

**Solution**: Configure connection timeout
```toml
[session]
idle_timeout_seconds = 300  # 5 minutes
```

## View Mappings

### PostgreSQL → HeliosDB

| PostgreSQL | HeliosDB |
|------------|----------|
| `pg_stat_activity.pid` | `helios_sessions.session_id` |
| `pg_stat_activity.usename` | `helios_sessions.username` |
| `pg_stat_activity.state` | `helios_sessions.state` |
| `pg_stat_activity.query` | `helios_sessions.current_query` |

### Oracle → HeliosDB

| Oracle | HeliosDB |
|--------|----------|
| `v$session.sid` | `helios_sessions.session_id` |
| `v$session.username` | `helios_sessions.username` |
| `v$session.status` | `helios_sessions.state` (mapped) |
| `v$session.sql_text` | `helios_sessions.current_query` |

## Configuration

```toml
[session]
# Maximum sessions per user
max_sessions_per_user = 10

# Maximum total sessions
max_total_sessions = 1000

# Idle timeout in seconds
idle_timeout_seconds = 3600

# Enable query history tracking
track_query_history = true

[monitoring]
# Enable session tracking
session_tracking = true

# Log session events
log_session_events = true

# Log query execution
log_query_execution = true
```

## Performance Tips

1. **Use indexes**: System tables should have indexes on frequently queried columns
2. **Limit results**: Always use LIMIT when querying session tables
3. **Batch updates**: Update session state in batches when possible
4. **Monitor locks**: Watch for lock contention on session registry
5. **Clean idle sessions**: Regularly clean up idle sessions