How to Run Oracle DBA Scripts

Use the steps below to prepare your Windows environment, verify Oracle connectivity, run a single script in SQL*Plus, or execute multiple scripts in batch mode. These scripts are designed to report on different areas in Oracle and IFS ERP. They do not insert, update, or delete records.

Running Our DBA Scripts

Prerequisites (Windows Environment)

Ensure the following components are in place on the Windows machine:

Oracle Client, or a full Oracle Database installation, is installed
The client version is compatible with the target Oracle database
TNSNAMES.ORA is properly configured
The Oracle client bin directory is included in the Windows PATH
You have valid credentials for the SYSTEM user
The SQL script is stored locally, such as C:\oracle\scripts

SQL Script Requirements

Each script will:

Accept the IFS application owner, IFSAPP, as a parameter
Generate an output file using the same name as the script

Verify Oracle Client and TNS Configuration Steps

Before running the scripts, verify that Oracle networking is working correctly from the machine where the scripts will run.

Step 1: Open Command Prompt.

Step 2: Issue the TNSPING command in the following format:

tnsping <TNS_ALIAS>

Example:

tnsping prod10

A successful result confirms:

TNS resolution is working
Network connectivity to the Oracle listener is valid

Execute Script Using SQL*Plus (Single Execution)

Run the script from Command Prompt using the SYSTEM user and pass IFSAPP as the first parameter.

Syntax

sqlplus system/<password>@<TNS_ALIAS> @<script_name>.sql IFSAPP

Example

sqlplus system/manager@IFSDB @ifs_space_analyzer.sql IFSAPP

Execute Script Using SQL*Plus (Batch Execution)

Many DBAs configure a directory for each database to run the scripts on a scheduled basis and store the output in a separate folder per day. The steps below show one way to batch your reports.

Step 1: Save the scripts into a common folder, such as:

C:\oracle\scripts

Step 2: Create a folder to hold the tuning reports for each database. For example, to hold reports for a database named prod10, create:

C:\oracle\tuning\prod10

This keeps the prod10 reports separate from other database reports.

Step 3: Save the following text into a batch file in your newly created database report folder using the name:

Launch_All_DBA_Scripts.bat

This batch file will execute each script in the C:\oracle\scripts folder one at a time in alphabetical order, waiting 3 seconds before launching the next script. Make sure your C:\oracle\scripts folder only contains the reports you want executed in batch mode.

@echo.
@echo Oracle Database Health Check and Performance Scripts
@echo Author : Alan Clark of eNSYNC Solutions
@echo Website : https://ensyncsolutions.com
@echo Email : [email protected]
@echo Phone : (913) 302-9899
@echo.
@echo Need help optimizing your Oracle database for IFS ERP?
@echo Contact us for expert DBA services, performance tuning,
@echo and troubleshooting to keep your system running smoothly.
@echo.
@echo Visit our website for more solutions: https://ensyncsolutions.com
@echo.

REM Modify the following variables to match your environment
set scriptdirname=C:\oracle\scripts
set ifsapppowner=”IFSAPP”

set dirname=%date:~-4,4%%date:~-10,2%%date:~7,2%
mkdir %dirname%
cd %dirname%

@ECHO OFF
color A
for %%I in (.) do set CurrDirName=%%~nxI
echo %CurrDirName%
DIR %scriptdirname%\*.sql /B > files_list.dat
FOR /F “tokens=1,2 delims=.” %%G IN (files_list.dat) DO (
SETLOCAL EnableDelayedExpansion
@echo.
@echo Processing File:%%G.%%H …
@echo ——————————————————–
@echo Calling SQL*Plus to load the job via a script.
@echo Launching: %scriptdirname%\%%G.%%H

REM Modify the SQL*Plus command line to update the password and connection string.
start sqlplus “SYSTEM/your_password@connection_name” @%scriptdirname%\%%G.%%H %ifsapppowner%

@echo.
@echo File %%G.%%H Launched.
TIMEOUT /T 3

ENDLOCAL
)
del files_list.dat

Step 4: Modify the following parameters before running the batch file: