Prerequisites & Environment Setup

Prerequisites & Environment Setup

Quick setup guide to get your Oracle environment ready for the utility scripts.

📋 Prerequisites

Oracle Client Required

You need an Oracle client to run the scripts:

• SQL*Plus (included with Oracle Client/Database)
• SQLcl (Oracle’s modern command line tool)
• Oracle Instant Client (minimum requirement)

Database Privileges

Scripts require appropriate database access:

For monitoring scripts:

GRANT SELECT ANY DICTIONARY TO your_user;
GRANT SELECT_CATALOG_ROLE TO your_user;

For administrative scripts:

GRANT DBA TO your_admin_user;

🚀 Quick Start

1. Test Your Connection

sqlplus username/password@database

-- Verify access
SELECT name, open_mode FROM v$database;
SELECT instance_name FROM v$instance;

2. Try a Script

Copy any script from the site and run it:

-- Example: Database health check
SELECT name, open_mode, database_role, log_mode FROM v$database;

3. Improve Output Formatting

For better script output, add these settings:

SET PAGESIZE 100
SET LINESIZE 132
SET TIMING ON
SET SERVEROUTPUT ON

🔧 Enhanced Setup (Optional)

SQL*Plus Login Script

Create login.sql in your working directory for automatic formatting:

-- login.sql - Auto-loaded by SQL*Plus
SET PAGESIZE 100
SET LINESIZE 132
SET TIMING ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "

-- Common column formatting
COLUMN username FORMAT A20
COLUMN tablespace_name FORMAT A25
COLUMN sql_text FORMAT A60 WORD_WRAPPED

Environment Variables (Linux/Unix)

# Add to ~/.bashrc or ~/.profile
export ORACLE_HOME=/path/to/oracle/client
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin

Database Connection Examples

# Easy connect (no TNS required)
sqlplus hr/password@//server:1521/ORCL

# TNS connection
sqlplus hr/password@PRODDB

# Using wallet/external authentication
sqlplus /@PRODDB

🧪 Test Your Setup

Verify everything works with this quick test:

-- Database info
SELECT name, version, open_mode FROM v$database;

-- Your privileges
SELECT COUNT(*) privileges FROM session_privs;
SELECT COUNT(*) roles FROM session_roles;

-- Performance test
SELECT metric_name, value
FROM v$sysmetric
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2;

🚨 Common Issues

Cannot connect to database

• Verify Oracle client is installed and in PATH
• Check TNS configuration or use easy connect syntax
• Confirm database is running and accessible

Script privilege errors

• Request DBA to grant SELECT_CATALOG_ROLE
• For admin scripts, may need DBA privileges
• Some scripts work with limited privileges

Poor output formatting

• Use SET LINESIZE and PAGESIZE commands
• Create login.sql for automatic formatting
• Consider redirecting output: SPOOL output.txt

🔗 Next Steps

Your environment is ready! Now:

1. Browse Scripts → - Explore the script library
2. Quick Start → - Try essential scripts
3. SQL*Plus Advanced Setup → - SQLPATH configuration