SQL*Plus Environment Setup

🔧 SQL*Plus Environment Setup

Set up your SQL*Plus environment to easily access all Oracle Day by Day scripts from any directory.

Overview

By configuring your SQLPATH environment variable, you can run any Oracle Day by Day script from anywhere on your system using simple commands like @db.sql instead of typing full file paths.

Quick Setup

1. Download Scripts

First, get all the Oracle Day by Day scripts:

# Clone or download the complete script collection
git clone https://github.com/oracledaybyday/oracle-scripts.git
# OR download from: https://oracledaybyday.com/downloads/scripts.zip

2. Set SQLPATH Environment Variable

Windows (Command Prompt)

# Temporary (current session only)
set SQLPATH=C:\OracleScripts;%SQLPATH%

# Permanent (all sessions)
setx SQLPATH "C:\OracleScripts;%SQLPATH%"

Windows (PowerShell)

# Temporary (current session only)
$env:SQLPATH = "C:\OracleScripts;$env:SQLPATH"

# Permanent (current user)
[Environment]::SetEnvironmentVariable("SQLPATH", "C:\OracleScripts;$env:SQLPATH", "User")

Linux/Unix/macOS

# Add to your ~/.bashrc, ~/.profile, or ~/.zshrc
export SQLPATH="/home/username/OracleScripts:$SQLPATH"

# Reload your shell configuration
source ~/.bashrc

3. Verify Setup

Test that SQL*Plus can find the scripts:

-- Connect to your database
sqlplus username/password@database

-- Test running a script (should work from any directory)
SQL> @db.sql

📁 Recommended Directory Structure

Organize your Oracle Day by Day scripts in a logical structure:

C:\OracleScripts\                    (or /home/username/OracleScripts/)
├── database-info/
│   ├── db.sql
│   ├── gvinst.sql
│   ├── vdb.sql
│   └── ...
├── performance/
│   ├── gvsess.sql
│   ├── gvlock.sql
│   ├── vsqltopfind.sql
│   └── ...
├── administration/
│   ├── gvpga.sql
│   ├── pgmcnt.sql
│   └── ...
└── security/
    ├── gvpwd.sql
    ├── role_definitions.sql
    └── ...

🔍 Using the Scripts

Once SQLPATH is configured, you can use scripts easily:

Basic Usage

-- Quick database info
SQL> @db.sql

-- Performance analysis
SQL> @gvsess.sql

-- Connection counts
SQL> @pgmcnt.sql

From Any Directory

# You can be in any directory
cd /tmp
sqlplus hr/hr@orcl

SQL> @db.sql          -- Still works!
SQL> @gvlock.sql      -- Scripts found automatically

📋 Script Categories Available

Database Information (29 scripts)

Essential database and instance queries

@db.sql              -- Quick database overview
@gvinst.sql          -- RAC instance status
@varchlog.sql        -- Archive log analysis
@vlog.sql            -- Redo log status

Performance Analysis (159 scripts)

Performance monitoring and tuning tools

@gvsess.sql          -- Active sessions
@gvlock.sql          -- Lock analysis
@vsqltopfind.sql     -- Top SQL statements
@high_wait_times.sql -- Wait time analysis

Administration (46 scripts)

DBA maintenance and management utilities

@pgmcnt.sql          -- Program connection count
@gvpga.sql           -- PGA memory analysis
@gvbp.sql            -- Buffer pool statistics

Security Analysis (7 scripts)

User, role, and privilege management

@gvpwd.sql           -- Password verification
@role_definitions.sql -- Role definitions
@vuserall.sql        -- User session search

Schema Analysis (59 scripts)

Table, index, and object analysis tools

@dtable.sql          -- Table analysis
@dindex.sql          -- Index analysis
@qindexddl.sql       -- Index DDL generation

ASM Analysis (7 scripts)

Automatic Storage Management utilities

@gvasmdsk.sql        -- ASM disk performance
@vasmdsk.sql         -- ASM disk status
@vasmdskg.sql        -- ASM disk group info

OEM Management (9 scripts)

Oracle Enterprise Manager tools

@tablespace_growth_oem.sql -- Tablespace growth tracking

⚙️ Advanced Configuration

Multiple Script Directories

You can include multiple directories in SQLPATH:

Windows

setx SQLPATH "C:\OracleScripts;C:\CustomScripts;C:\TeamScripts"

Linux/Unix/macOS

export SQLPATH="/home/user/OracleScripts:/opt/oracle/scripts:/shared/dba-scripts:$SQLPATH"

Create a login.sql file in your SQLPATH to customize your SQL*Plus environment:

-- login.sql - Executed automatically when SQL*Plus starts
SET PAGESIZE 50
SET LINESIZE 130
SET SERVEROUTPUT ON
SET TIMING ON
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "

-- Define common aliases
DEFINE db = "@db.sql"
DEFINE sessions = "@gvsess.sql"
DEFINE locks = "@gvlock.sql"

-- Welcome message
PROMPT
PROMPT Oracle Day by Day Scripts Available
PROMPT Type @db.sql for database info
PROMPT Type @gvsess.sql for active sessions
PROMPT Type @help.sql for script list
PROMPT

Creating Script Aliases

Add aliases to your login.sql for frequently used scripts:

-- Quick aliases for common scripts
DEFINE d = "@db.sql"
DEFINE s = "@gvsess.sql"
DEFINE l = "@gvlock.sql"
DEFINE p = "@pgmcnt.sql"
DEFINE t = "@vsqltopfind.sql"

-- Now you can use:
-- SQL> @d      (runs db.sql)
-- SQL> @s      (runs gvsess.sql)
-- SQL> @l      (runs gvlock.sql)

🔗 Script Downloads

Individual Categories:

Complete Collection:

🚨 Important Notes

Security Considerations

Only add trusted script directories to SQLPATH
Review scripts before running in production
Use separate script directories for different environments

Path Order

SQL*Plus searches directories in SQLPATH order
First match wins - place most important directories first
Local directory (.) is searched first by default

Troubleshooting

Script Not Found

SQL> @mytest.sql
SP2-0310: unable to open file "mytest.sql"

Check SQLPATH environment variable: echo %SQLPATH% (Windows) or echo $SQLPATH (Unix)
Verify script exists in one of the SQLPATH directories
Check file permissions

Environment Variable Not Set

# Check current SQLPATH
echo $SQLPATH

# If empty, verify your shell profile was updated and sourced
source ~/.bashrc

Quick Start Guide - Getting started with Oracle Day by Day
Installation Guide - Database setup and prerequisites
Script Reference - Complete script documentation