SQL*Plus Login Configuration (login.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.

What This Script Does

Configures the SQLPlus environment with custom settings and creates a dynamic prompt that displays the current user and instance name. This script is typically placed in the SQLPlus search path to automatically execute when starting SQL*Plus sessions.

The Script

rem  login.sql
rem
ttitle 'SQL*Plus Login Configuration'
rem
set term off
set pause off
rem
col the_sid noprint new_val the_sid
col the_user noprint new_val the_user
rem
select instance_name the_sid, user the_user
from v$instance
/
rem
set sqlprompt "&the_user@&the_sid:SQL> "
rem
undefine the_sid
undefine the_user
rem
set serveroutput on
set term on
set trimspool on
set linesize 200
set pagesize 100
set timing on
set time on
set suffix sql
set verify off
set num 18
set editfile C:/logs

Usage

-- Automatic execution when placed in SQL*Plus path
-- Or manual execution:
@login.sql

Parameters

The script requires no input parameters and automatically detects the current instance and user.

Required Privileges

SELECT on V$INSTANCE
-- (Usually available to all users)

Key Configuration Settings

Prompt Customization

-- Creates prompt showing: username@instance:SQL>
-- Example: SCOTT@PROD:SQL>
set sqlprompt "&the_user@&the_sid:SQL> "

Display Settings

set linesize 200        -- Set line width to 200 characters
set pagesize 100        -- Set page length to 100 lines
set timing on           -- Show execution timing
set time on             -- Show current time
set serveroutput on     -- Enable DBMS_OUTPUT display

Behavior Settings

set verify off          -- Suppress substitution variable display
set trimspool on        -- Remove trailing spaces in spool files
set suffix sql          -- Default file extension for scripts
set num 18              -- Number column width
set pause off           -- Disable pause between pages

File Settings

set editfile C:/logs  -- Default edit file location

Customization Options

Modify Prompt Format

-- Simple prompt
set sqlprompt "SQL> "

-- Instance only
set sqlprompt "&the_sid:SQL> "

-- Include date/time
set sqlprompt "&the_user@&the_sid (&_DATE):SQL> "

Adjust Display Settings

-- For wide monitors
set linesize 300

-- For detailed output
set pagesize 200

-- For minimal output
set pagesize 20

Add Custom Settings

-- Additional useful settings
set echo off            -- Don't echo commands
set feedback off        -- Suppress row count feedback
set heading on          -- Show column headings
set wrap on             -- Wrap long lines

Environment Variables

The script uses SQL*Plus substitution variables:

• &the_user - Current database username
• &the_sid - Current instance name

Common Use Cases

Development Environment Setup

-- Place login.sql in SQL*Plus search path
-- Automatic execution for consistent environment

DBA Administration

-- Clear identification of current connection
-- Consistent formatting for all sessions

Team Standardization

-- Ensure all team members have same settings
-- Reduce formatting inconsistencies

Production Safety

-- Clear instance identification in prompt
-- Prevent accidental operations on wrong environment

Installation

Windows

1. Place in %ORACLE_HOME%\sqlplus\admin\ directory
2. Or place in current working directory
3. Or specify in SQLPATH environment variable

Unix/Linux

1. Place in $ORACLE_HOME/sqlplus/admin/ directory
2. Or place in home directory
3. Or specify in SQLPATH environment variable

Benefits

Visual Confirmation

• Clear indication of current database connection
• Prevents accidental operations on wrong instance
• Easy identification in multiple SQL*Plus sessions

Consistent Environment

• Standardized display settings
• Predictable output formatting
• Improved readability

Productivity Enhancement

• Timing information for performance analysis
• Proper line width and page settings
• Server output enabled for debugging

Performance Impact

• Minimal Impact: Executes once per session
• Quick Execution: Simple queries and settings
• No Ongoing Overhead: Settings persist for session

Troubleshooting

Prompt Not Updating

• Verify V$INSTANCE access permissions
• Check for syntax errors in script
• Ensure proper file placement

Settings Not Applied

• Confirm script is in SQL*Plus search path
• Check for conflicting glogin.sql settings
• Verify file permissions

Related Scripts

• name - Database name query
• Database-specific login scripts for different environments
• Team-specific customization scripts