Update Java using python script.

 #!/usr/bin/env python3
import csv
import os
import sys
import paramiko
from datetime import datetime

# ============================================================
# CONFIGURATION
# ============================================================
CSV_FILE = "jdk_servers.csv"
JDK_TAR = "/home/oracle/Desktop/jdk-8u501-linux-x64.tar.gz"
SSH_USER = "oracle"
SSH_PORT = 22
LOG_FILE = "jdk_upgrade.log"

# ============================================================
# Logging
# ============================================================
def log(message):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    msg = f"[{timestamp}] {message}"
    print(msg)
    with open(LOG_FILE, "a") as f:
        f.write(msg + "\n")

# ============================================================
# Execute remote command
# ============================================================
def execute(ssh, command):
    log(f"COMMAND: {command}")
    stdin, stdout, stderr = ssh.exec_command(command)
    output = stdout.read().decode().strip()
    error = stderr.read().decode().strip()
    exit_code = stdout.channel.recv_exit_status()
    if output:
        log(output)
    if error:
        log(error)
    return exit_code, output, error

# ============================================================
# Upgrade JDK
# ============================================================
def upgrade_jdk(server, jdk_path):
    log("")
    log("=" * 70)
    log(f"Starting JDK upgrade on {server}")
    log(f"JDK Path: {jdk_path}")
    log("=" * 70)
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(
        paramiko.AutoAddPolicy()
    )
    try:
        log(f"Connecting to {server}...")
        ssh.connect(
            hostname=server,
            port=SSH_PORT,
            username=SSH_USER,
            timeout=30
        )
        log("SSH connection successful.")
        # ----------------------------------------------------
        # Check JDK tar
        # ----------------------------------------------------
        exit_code, _, _ = execute(
            ssh,
            f"test -f {JDK_TAR}"
        )
        if exit_code != 0:
            return (
                "FAILED",
                "",
                "",
                "JDK tar file not found"
            )
        # ----------------------------------------------------
        # Check existing JDK
        # ----------------------------------------------------
        exit_code, old_version, _ = execute(
            ssh,
            f"{jdk_path}/bin/java -version"
        )
        if exit_code != 0:
            old_version = "UNKNOWN"
            log(
                "WARNING: Unable to determine current JDK version."
            )
        # ----------------------------------------------------
        # Timestamp
        # ----------------------------------------------------
        timestamp = datetime.now().strftime(
            "%Y%m%d_%H%M%S"
        )
        backup_path = (
            f"{jdk_path}_backup_{timestamp}"
        )
        temp_path = (
            f"{jdk_path}_new_{timestamp}"
        )
        # ----------------------------------------------------
        # Check whether JDK directory exists
        # ----------------------------------------------------
        exit_code, _, _ = execute(
            ssh,
            f"test -d {jdk_path}"
        )
        if exit_code != 0:
            return (
                "FAILED",
                old_version,
                "",
                f"JDK path does not exist: {jdk_path}"
            )
        # ----------------------------------------------------
        # Capture ownership
        # ----------------------------------------------------
        exit_code, ownership, _ = execute(
            ssh,
            f"stat -c '%U:%G' {jdk_path}"
        )
        if exit_code != 0:
            return (
                "FAILED",
                old_version,
                "",
                "Unable to determine JDK ownership"
            )
        log(f"JDK ownership: {ownership}")
        # ----------------------------------------------------
        # Backup current JDK
        # ----------------------------------------------------
        log("Creating JDK backup...")
        exit_code, _, _ = execute(
            ssh,
            f"mv {jdk_path} {backup_path}"
        )
        if exit_code != 0:
            return (
                "FAILED",
                old_version,
                "",
                "Unable to backup existing JDK"
            )
        log(
            f"Backup created: {backup_path}"
        )
        # ----------------------------------------------------
        # Create temporary directory
        # ----------------------------------------------------
        execute(
            ssh,
            f"mkdir -p {temp_path}"
        )
        # ----------------------------------------------------
        # Extract new JDK
        # ----------------------------------------------------
        log("Extracting new JDK...")
        exit_code, _, _ = execute(
            ssh,
            f"tar -xzf {JDK_TAR} "
            f"-C {temp_path} "
            f"--strip-components=1"
        )
        if exit_code != 0:
            log("JDK extraction failed.")
            # Rollback
            execute(
                ssh,
                f"rm -rf {temp_path}"
            )
            execute(
                ssh,
                f"mv {backup_path} {jdk_path}"
            )
            return (
                "ROLLED_BACK",
                old_version,
                "",
                "JDK extraction failed"
            )
        # ----------------------------------------------------
        # Set ownership
        # ----------------------------------------------------
        log(
            f"Setting ownership: {ownership}"
        )
        exit_code, _, _ = execute(
            ssh,
            f"chown -R {ownership} {temp_path}"
        )
        if exit_code != 0:
            log("Ownership update failed.")
            execute(
                ssh,
                f"rm -rf {temp_path}"
            )
            execute(
                ssh,
                f"mv {backup_path} {jdk_path}"
            )
            return (
                "ROLLED_BACK",
                old_version,
                "",
                "Unable to set ownership"
            )
        # ----------------------------------------------------
        # Activate new JDK
        # ----------------------------------------------------
        log("Activating new JDK...")
        exit_code, _, _ = execute(
            ssh,
            f"mv {temp_path} {jdk_path}"
        )
        if exit_code != 0:
            log("Unable to activate new JDK.")
            execute(
                ssh,
                f"rm -rf {temp_path}"
            )
            execute(
                ssh,
                f"mv {backup_path} {jdk_path}"
            )
            return (
                "ROLLED_BACK",
                old_version,
                "",
                "Unable to activate new JDK"
            )
        # ----------------------------------------------------
        # Verify
        # ----------------------------------------------------
        log("Verifying new JDK...")
        exit_code, new_version, _ = execute(
            ssh,
            f"{jdk_path}/bin/java -version"
        )
        if exit_code != 0:
            log(
                "New JDK verification failed."
            )
            # Rollback
            execute(
                ssh,
                f"rm -rf {jdk_path}"
            )
            execute(
                ssh,
                f"mv {backup_path} {jdk_path}"
            )
            return (
                "ROLLED_BACK",
                old_version,
                "",
                "Java verification failed"
            )
        # ----------------------------------------------------
        # SUCCESS
        # ----------------------------------------------------
        log(
            f"JDK upgrade successful on {server}"
        )
        return (
            "SUCCESS",
            old_version,
            new_version,
            f"Backup: {backup_path}"
        )
    except Exception as e:
        log(
            f"ERROR connecting/upgrading {server}: {e}"
        )
        return (
            "FAILED",
            "",
            "",
            str(e)
        )
    finally:
        ssh.close()

# ============================================================
# Main
# ============================================================
def main():
    if not os.path.isfile(CSV_FILE):
        print(
            f"ERROR: CSV file not found: {CSV_FILE}"
        )
        sys.exit(1)
    results = []
    with open(
        CSV_FILE,
        newline=""
    ) as csvfile:
        reader = csv.DictReader(csvfile)
        required_columns = {
            "server",
            "oracle_home"
        }
        if not required_columns.issubset(
            reader.fieldnames
        ):
            print(
                "ERROR: CSV must contain "
                "'server' and 'oracle_home' columns."
            )
            sys.exit(1)
        for row in reader:
            server = row["server"].strip()
            jdk_path = row["oracle_home"].strip()
            status, old_version, new_version, remarks = (
                upgrade_jdk(
                    server,
                    jdk_path
                )
            )
            results.append({
                "server": server,
                "jdk_path": jdk_path,
                "old_version": old_version,
                "new_version": new_version,
                "status": status,
                "remarks": remarks
            })
    # ========================================================
    # Write result CSV
    # ========================================================
    output_file = (
        f"jdk_upgrade_result_"
        f"{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
    )
    with open(
        output_file,
        "w",
        newline=""
    ) as csvfile:
        fieldnames = [
            "server",
            "jdk_path",
            "old_version",
            "new_version",
            "status",
            "remarks"
        ]
        writer = csv.DictWriter(
            csvfile,
            fieldnames=fieldnames
        )
        writer.writeheader()
        writer.writerows(results)
    print("")
    print("=" * 70)
    print("JDK UPGRADE COMPLETED")
    print("=" * 70)
    print(
        f"Result file: {output_file}"
    )

if __name__ == "__main__":
    main()

Update Java using python script (Python 2.6.6 compatible script)

 #!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import sys
import csv
import tarfile
import shutil
import subprocess
from datetime import datetime

# ============================================================
# CONFIGURATION
# ============================================================
CSV_FILE = "/home/oracle/Desktop/jdk_servers.csv"
JDK_TAR = "/home/oracle/Desktop/jdk-8u501-linux-x64.tar.gz"
LOG_FILE = "/home/oracle/Desktop/jdk_upgrade.log"

# ============================================================
# Logging
# ============================================================
def log(message):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    msg = "[{0}] {1}".format(timestamp, message)
    print msg
    try:
        logfile = open(LOG_FILE, "a")
        logfile.write(msg + "\n")
        logfile.close()
    except Exception:
        pass

# ============================================================
# Execute local command
# ============================================================
def execute_command(command):
    log("Executing: {0}".format(command))
    process = subprocess.Popen(
        command,
        shell=True,
        stdout=subprocess.PIPE,
        stderr=subprocess.STDOUT
    )
    output = process.communicate()[0]
    try:
        output = output.decode("utf-8")
    except Exception:
        pass
    output = output.strip()
    if output:
        log(output)
    return process.returncode, output

# ============================================================
# Check JDK tar file
# ============================================================
def check_jdk_tar():
    log("Checking JDK package...")
    if not os.path.isfile(JDK_TAR):
        log("ERROR: JDK package not found:")
        log(JDK_TAR)
        return False
    log("JDK package found: {0}".format(JDK_TAR))
    try:
        tar = tarfile.open(JDK_TAR, "r:gz")
        tar.close()
    except Exception as error:
        log(
            "ERROR: Invalid JDK tar file: {0}".format(error)
        )
        return False
    return True

# ============================================================
# Check current JDK
# ============================================================
def check_current_jdk(jdk_path):
    java_path = os.path.join(
        jdk_path,
        "bin",
        "java"
    )
    if not os.path.isfile(java_path):
        log(
            "ERROR: Java binary not found: {0}".format(
                java_path
            )
        )
        return False, "UNKNOWN"
    log("Current JDK version:")
    returncode, output = execute_command(
        "{0} -version".format(java_path)
    )
    if returncode != 0:
        return False, "UNKNOWN"
    return True, output

# ============================================================
# Get ownership
# ============================================================
def get_ownership(jdk_path):
    returncode, output = execute_command(
        "stat -c '%U:%G' '{0}'".format(jdk_path)
    )
    if returncode != 0:
        return None
    return output.strip()

# ============================================================
# Backup existing JDK
# ============================================================
def backup_jdk(jdk_path, backup_path):
    log("Backing up existing JDK...")
    if not os.path.isdir(jdk_path):
        log(
            "ERROR: JDK directory does not exist: {0}".format(
                jdk_path
            )
        )
        return False
    try:
        os.rename(
            jdk_path,
            backup_path
        )
        log(
            "JDK backup created: {0}".format(
                backup_path
            )
        )
        return True
    except Exception as error:
        log(
            "ERROR: Unable to backup JDK: {0}".format(
                error
            )
        )
        return False

# ============================================================
# Extract new JDK
# ============================================================
def extract_jdk(temp_path):
    log("Creating temporary directory:")
    log(temp_path)
    try:
        os.makedirs(temp_path)
    except Exception as error:
        log(
            "ERROR: Unable to create directory: {0}".format(
                error
            )
        )
        return False
    log("Extracting JDK...")
    try:
        tar = tarfile.open(
            JDK_TAR,
            "r:gz"
        )
        tar.extractall(
            temp_path
        )
        tar.close()
    except Exception as error:
        log(
            "ERROR: JDK extraction failed: {0}".format(
                error
            )
        )
        shutil.rmtree(
            temp_path,
            ignore_errors=True
        )
        return False
    # --------------------------------------------------------
    # Handle top-level directory inside tar
    # --------------------------------------------------------
    try:
        contents = os.listdir(
            temp_path
        )
        if len(contents) == 1:
            first_item = os.path.join(
                temp_path,
                contents[0]
            )
            if os.path.isdir(first_item):
                log(
                    "JDK archive contains top-level directory: {0}".format(
                        contents[0]
                    )
                )
                items = os.listdir(first_item)
                for item in items:
                    source = os.path.join(
                        first_item,
                        item
                    )
                    destination = os.path.join(
                        temp_path,
                        item
                    )
                    shutil.move(
                        source,
                        destination
                    )
                os.rmdir(
                    first_item
                )
    except Exception as error:
        log(
            "ERROR processing extracted JDK: {0}".format(
                error
            )
        )
        shutil.rmtree(
            temp_path,
            ignore_errors=True
        )
        return False
    return True

# ============================================================
# Verify extracted JDK BEFORE activation
# ============================================================
def verify_new_jdk(temp_path):
    java_path = os.path.join(
        temp_path,
        "bin",
        "java"
    )
    if not os.path.isfile(java_path):
        log(
            "ERROR: New Java binary not found."
        )
        return False, ""
    log("Checking NEW JDK before activation:")
    returncode, output = execute_command(
        "{0} -version".format(java_path)
    )
    if returncode != 0:
        log(
            "ERROR: New JDK Java execution failed."
        )
        return False, output
    # --------------------------------------------------------
    # Check that this is JDK 8u501
    # --------------------------------------------------------
    if "1.8.0_501" not in output:
        log(
            "WARNING: Expected JDK version 1.8.0_501."
        )
        log(
            "Actual version output:"
        )
        log(output)
        return False, output
    log(
        "New JDK version 1.8.0_501 verified."
    )
    return True, output

# ============================================================
# Set ownership
# ============================================================
def set_ownership(path, ownership):
    if not ownership:
        return True
    log(
        "Setting ownership {0} on {1}".format(
            ownership,
            path
        )
    )
    returncode, output = execute_command(
        "chown -R {0} '{1}'".format(
            ownership,
            path
        )
    )
    return returncode == 0

# ============================================================
# Activate JDK
# ============================================================
def activate_jdk(temp_path, jdk_path):
    log("Activating new JDK...")
    try:
        os.rename(
            temp_path,
            jdk_path
        )
    except Exception as error:
        log(
            "ERROR: Unable to activate new JDK: {0}".format(
                error
            )
        )
        return False
    log(
        "New JDK installed at: {0}".format(
            jdk_path
        )
    )
    return True

# ============================================================
# Rollback
# ============================================================
def rollback(
    jdk_path,
    backup_path,
    temp_path
):
    log("==============================================")
    log("STARTING ROLLBACK")
    log("==============================================")
    # Remove failed/new JDK
    if os.path.exists(jdk_path):
        failed_path = (
            jdk_path +
            "_failed_" +
            datetime.now().strftime(
                "%Y%m%d_%H%M%S"
            )
        )
        try:
            os.rename(
                jdk_path,
                failed_path
            )
            log(
                "Failed JDK moved to: {0}".format(
                    failed_path
                )
            )
        except Exception:
            shutil.rmtree(
                jdk_path,
                ignore_errors=True
            )
    # Remove temporary directory
    if os.path.exists(temp_path):
        shutil.rmtree(
            temp_path,
            ignore_errors=True
        )
    # Restore backup
    if os.path.exists(backup_path):
        try:
            os.rename(
                backup_path,
                jdk_path
            )
            log(
                "Old JDK restored successfully."
            )
        except Exception as error:
            log(
                "CRITICAL: Unable to restore backup: {0}".format(
                    error
                )
            )
            return False
    else:
        log(
            "CRITICAL: Backup JDK not found!"
        )
        return False
    return True

# ============================================================
# Upgrade one JDK
# ============================================================
def upgrade_jdk(server, jdk_path):
    log("")
    log("==============================================")
    log(
        "Processing server: {0}".format(server)
    )
    log(
        "JDK path: {0}".format(jdk_path)
    )
    log("==============================================")
    # --------------------------------------------------------
    # Validate JDK
    # --------------------------------------------------------
    valid, old_version = check_current_jdk(
        jdk_path
    )
    if not valid:
        return (
            "FAILED",
            old_version,
            "",
            "Current JDK validation failed"
        )
    # --------------------------------------------------------
    # Get ownership
    # --------------------------------------------------------
    ownership = get_ownership(
        jdk_path
    )
    if ownership:
        log(
            "Current ownership: {0}".format(
                ownership
            )
        )
    # --------------------------------------------------------
    # Timestamp
    # --------------------------------------------------------
    timestamp = datetime.now().strftime(
        "%Y%m%d_%H%M%S"
    )
    backup_path = (
        jdk_path +
        "_backup_" +
        timestamp
    )
    temp_path = (
        jdk_path +
        "_new_" +
        timestamp
    )
    # --------------------------------------------------------
    # Backup
    # --------------------------------------------------------
    if not backup_jdk(
        jdk_path,
        backup_path
    ):
        return (
            "FAILED",
            old_version,
            "",
            "JDK backup failed"
        )
    # --------------------------------------------------------
    # Extract
    # --------------------------------------------------------
    if not extract_jdk(
        temp_path
    ):
        rollback(
            jdk_path,
            backup_path,
            temp_path
        )
        return (
            "ROLLED_BACK",
            old_version,
            "",
            "JDK extraction failed"
        )
    # --------------------------------------------------------
    # Set ownership
    # --------------------------------------------------------
    if ownership:
        if not set_ownership(
            temp_path,
            ownership
        ):
            rollback(
                jdk_path,
                backup_path,
                temp_path
            )
            return (
                "ROLLED_BACK",
                old_version,
                "",
                "Ownership update failed"
            )
    # --------------------------------------------------------
    # Verify BEFORE activation
    # --------------------------------------------------------
    valid, new_version = verify_new_jdk(
        temp_path
    )
    if not valid:
        rollback(
            jdk_path,
            backup_path,
            temp_path
        )
        return (
            "ROLLED_BACK",
            old_version,
            new_version,
            "New JDK verification failed"
        )
    # --------------------------------------------------------
    # Activate
    # --------------------------------------------------------
    if not activate_jdk(
        temp_path,
        jdk_path
    ):
        rollback(
            jdk_path,
            backup_path,
            temp_path
        )
        return (
            "ROLLED_BACK",
            old_version,
            new_version,
            "JDK activation failed"
        )
    # --------------------------------------------------------
    # Final verification
    # --------------------------------------------------------
    valid, final_version = check_current_jdk(
        jdk_path
    )
    if not valid:
        rollback(
            jdk_path,
            backup_path,
            temp_path
        )
        return (
            "ROLLED_BACK",
            old_version,
            final_version,
            "Final Java verification failed"
        )
    log("==============================================")
    log("JDK UPGRADE SUCCESSFUL")
    log("==============================================")
    log(
        "Old JDK backup: {0}".format(
            backup_path
        )
    )
    log(
        "New JDK: {0}".format(
            jdk_path
        )
    )
    return (
        "SUCCESS",
        old_version,
        final_version,
        "Backup: {0}".format(backup_path)
    )

# ============================================================
# Main
# ============================================================
def main():
    log("==============================================")
    log("Oracle JDK 8u501 Upgrade")
    log("Python 2.6.6 Compatible Script")
    log("==============================================")
    # --------------------------------------------------------
    # Check CSV
    # --------------------------------------------------------
    if not os.path.isfile(CSV_FILE):
        log(
            "ERROR: CSV file not found: {0}".format(
                CSV_FILE
            )
        )
        sys.exit(1)
    # --------------------------------------------------------
    # Check JDK package
    # --------------------------------------------------------
    if not check_jdk_tar():
        sys.exit(1)
    results = []
    # --------------------------------------------------------
    # Read CSV
    # --------------------------------------------------------
    csvfile = open(
        CSV_FILE,
        "r"
    )
    reader = csv.DictReader(
        csvfile
    )
    for row in reader:
        server = row["server"].strip()
        jdk_path = row["oracle_home"].strip()
        status, old_version, new_version, remarks = (
            upgrade_jdk(
                server,
                jdk_path
            )
        )
        results.append({
            "server": server,
            "jdk_path": jdk_path,
            "old_version": old_version,
            "new_version": new_version,
            "status": status,
            "remarks": remarks
        })
    csvfile.close()
    # --------------------------------------------------------
    # Result CSV
    # --------------------------------------------------------
    result_file = (
        "/home/oracle/Desktop/"
        "jdk_upgrade_result_" +
        datetime.now().strftime(
            "%Y%m%d_%H%M%S"
        ) +
        ".csv"
    )
    outfile = open(
        result_file,
        "wb"
    )
    fieldnames = [
        "server",
        "jdk_path",
        "old_version",
        "new_version",
        "status",
        "remarks"
    ]
    writer = csv.DictWriter(
        outfile,
        fieldnames=fieldnames
    )
    writer.writeheader()
    for result in results:
        writer.writerow(
            result
        )
    outfile.close()
    log("")
    log("==============================================")
    log("JDK UPGRADE PROCESS COMPLETED")
    log("==============================================")
    log(
        "Result CSV: {0}".format(
            result_file
        )
    )

if __name__ == "__main__":
    main()

AI-Powered Email Triage and Priority Classification Agent

## AI-Powered Email Triage and Priority Classification Agent

### Problem Statement

We spend significant time reading and sorting large volumes of emails every day. Important or urgent emails can easily get buried among routine notifications, FYI emails, reports, and low-priority communications. Additionally we also have important OEM alerts notification. We can classifiy these alerts notification based on its importance.

This manual email-triage process is repetitive, time-consuming, and can result in delayed responses to critical communications and alert notification.

### Proposed Solution

Implement an **AI-powered Email Triage Agent** that automatically analyzes incoming emails and categorizes them based on business priority:

* 🔴 **Critical** – Immediate attention required

* 🟠 **Important** – Action required within a defined timeframe

* 🟡 **FYI** – Information that may be useful

* ⚪ **Ignore/Low Priority** – Notifications or emails requiring no action

The AI agent would also generate a short summary of each important email and identify the **required action, deadline, sender, and key information**.

### Example

Instead of manually reviewing 2000+ emails, the user could receive:

**🔴 Critical – 3 emails**

* Production database issue – Action required immediately

* Security vulnerability – Remediation required by Friday

* Management escalation – Response required today

**🟠 Important – 12 emails**

* Pending approvals

* Project updates

* Team requests

**🟡 FYI – 45 emails**

* Automated reports

* Status notifications

* General communications

### Expected Benefits

* Reduce time spent manually reviewing emails

* Improve visibility of critical communications

* Reduce the risk of missing important requests

* Automatically identify required actions and deadlines

* Improve employee productivity

* Allow employees to focus on higher-value activities

### Success Metrics

The effectiveness of the solution could be measured through:

* Reduction in time spent on email triage

* Number of critical emails/alerts correctly identified

* Reduction in missed/overdue email actions

* Employee adoption and satisfaction

* Percentage of emails automatically classified

### Business Value

This solution can reduce repetitive administrative work and help employees focus more on **decision-making, problem-solving, and business-critical activities** rather than manually managing email volume.

Create an Oracle SQL TUNING TASK manually

 Create an Oracle SQL TUNING TASK manually with the sql_id 

You can create an SQL TUNING TASK manually ad hoc with the following simple steps. ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.

 In case this is a current sql running use the v$session.

 select sql_id from v$session where sid = :x

 1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task: ===========================================================================

SET SERVEROUTPUT ON
declare 
stmt_task VARCHAR2(40); 
begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); 
end; / =========================================================================== task_id: TASK_69287 

2. Run the SQL TUNING TASK =========================================================================== begin 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287'); 
end; 
/ =========================================================================== == 3. You can monitor the processing of the tuning task with the statement

 SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_ 69287';

 4. When the task has a status=COMPLETED, then run: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual; 

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile. begin DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE); end; / 

6. You can check the database sql profiles with the statement:

 select * from dba_sql_profiles; 

In case you want to disable an sql profile use the statement:

 begin
 DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED'); 
end; 
/

Query is running slow for long time for SQL_ID 
Query is running slow for long time for SQL_ID captured 

Run SQL Tuning Adviosor for the sql_id
------------------------------------- 

Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID

 So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID. 

Recommendations from sqltrpt.sql will be providing the best Explain Plan. 

We can implement these profiles/index rebuild/... suggested after checking with the SME of the database. 

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql 

$sqlplus "/as sysdba" 

Query to see current running sqls 

set pages 50000 lines 32767
col program format a40
col sql_text format a130
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v 
$sqlarea c
 where b.sql_id=c.sql_id 
/ 

Run SQL Tuning Advisor for the SQL_ID

SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:

------------------------------------------------------------------- 

SQL> execute dbms_sqltune.accept_sql_profile(task_name => '',task_owner => 'SYS', replace => TRUE, FORCE_MATCH => TRUE); 

If successful, you should see the following:

PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:

---------------------------------------------------

SQL> execute dbms_sqltune.drop_sql_profile(''); 

If successful, you should see the following: PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; 

Command to Alter SQL Profile in Oracle for the SQL_ID:-

--------------------------------------------------- 

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ('','STATUS','DISABLED');

 If successful, you should see the following: PL/SQL procedure successfully completed. 

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; If you don't know the name of the SQL Profile then use the below query 

SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT% TABLE%NAME%';

 Query
---- 

The SQL_ID is not stored with the profiles. You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%' and sql_id like nvl('&sql_id',sql_id) and sql_profile like nvl('&sql_profile_name',sql_profile) and sql_profile is not null order by 1, 2, 3 /

How to create sql profile

 @Sqlh
 
Select sqlid from @longops and get plan hash value from @sqlh or @sqlver for particular sqlid
 
Not in red sqlid after that Plan Hash value change sqlid and plan hash value bellow pl/sql procedure
 
Copy it in notepad and change sqlid and plan hash value
 
declare
     ar_hint_table    sys.dbms_debug_vc2coll;
     ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
     cl_sql_text      clob;
     i                pls_integer;
   begin
     with a as (
                select
                     rownum as r_no
                     , a.*
                From table( dbms_xplan.display_awr('913uapvkwtmav',   3122673927, null, 'OUTLINE' )
              ) a
     ),
     b as (
     select
              min(r_no) as start_r_no
     from
              a
     where
              a.plan_table_output = 'Outline Data'
     ),
     c as (
     select
              min(r_no) as end_r_no
     from
              a
           , b
    where
             a.r_no > b.start_r_no
    and      a.plan_table_output = '  */'
    ),
    d as (
    select
             instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
     from
              a
            , b
     where
              r_no = b.start_r_no + 4
     )
     select
              substr(a.plan_table_output, d.start_col) as outline_hints
     bulk collect
     into
              ar_hint_table
     from
              a
            , b
            , c
            , d
     where
         a.r_no >= b.start_r_no + 4
    54    and      a.r_no <= c.end_r_no - 1
     order by
              a.r_no;
 
     select
              sql_text
     into
              cl_sql_text
     from
              sys.dba_hist_sqltext
     where
              sql_id = '913uapvkwtmav';
 
     -- this is only required
     -- to concatenate hints
     -- splitted across several lines
     -- and could be done in SQL, too
     i := ar_hint_table.first;
     while i is not null
     loop
       if ar_hint_table.exists(i + 1) then
         if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
           ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
           ar_hint_table.delete(i + 1);
         end if;
       end if;
       i := ar_hint_table.next(i);
     end loop;
 
     i := ar_hint_table.first;
     while i is not null
     loop
       ar_profile_hints.extend;
       ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
       i := ar_hint_table.next(i);
     end loop;
 
     dbms_sqltune.import_sql_profile(
       sql_text    => cl_sql_text
     , profile     => ar_profile_hints
     , name        => 'SQLP_913uapvkwtmav_3122673927'
     -- use force_match => true
     -- to use CURSOR_SHARING=SIMILAR
 -- behaviour, i.e. match even with
    98    -- differing literals
     , force_match => false
   );
  End;
 
  /
 
 
 
Check sql profile is create or not
 
@sql_profiles          Press Enter
Ask profile name     if don’t know sql profile name Press Enter it will display all prifile
 
 
After creating profile Gather stats of all tables which is used in that sqlid
 
EXEC DBMS_STATS.GATHER_TABLE_STATS('OLAP', '&table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 32, cascade => TRUE);
 
SQL> !cat sql_profiles.sql
 
set pages 300 lines 300;
col name for a40 ;
col created for a50 ;
col last_modified for a50 ;
 
select name , CREATED,LAST_MODIFIED,STATUS from dba_sql_profiles where name like '%&name%'
order by CREATED;
 
 
 
SQL>
SQL>
SQL> !cat disable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'DISABLED');
 
 
 
SQL>
SQL>
SQL> !cat enable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'ENABLED');
 
 
SQL>
SQL>
SQL> !cat drop_profile.sql
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('&PROFILE_NAME');
 
SQL>
 
x
x

Sql queries to check ACTIVE / INACTIVE Sessions

 --Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

--Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

--COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

--TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.program;

--TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module  for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.module;

--TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.action;

--INACTIVE SESSIONS

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where  p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

--INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS GROUP BY  MODULE

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.module;

--INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

--INACTIVE PROGRAM  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

--INACTIVE MODULES  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

--INACTIVE JDBC SESSIONS

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

--COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

FORMS

--TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

--FORMS SESSIONS DETAILS

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;                      

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;         

order by 4;                           

--INACTIVE FORMS SESSIONS DETAILS

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

--UNIQUE SPID

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--COUNT FORMS

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--ZERO HASH VALUE

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

--INACTIVE FORM BY NAME

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

--INACTIVE FORM SESSIONS 

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id        = rf.form_id

AND ft.language       = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins           fl,

gv$process            vp,

apps.fnd_login_resp_forms rf,

gv$session            vs

WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */

AND fl.login_type   = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid          = vp.pid

AND fl.login_id     = rf.login_id

AND rf.end_time    IS NULL

AND rf.audsid       = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

--ACTIVE

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

--MODULE

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr 

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

--Unique Actions

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY  program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr  AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;