Essential Scripts Every Oracle DBA Should Keep Handy

database administration Hits: 26

Many times, we’re looking for a simple script to check ASM space usage, identify blocking sessions, or even review RMAN backups. But often these scripts were left on a previous laptop, or lost in some forgotten folder on the server.

In this article, I’ll introduce a set of scripts that I always keep in my ORACLE_PATH — those handy little tools that should always be “behind your ear.”

What is ORACLE_PATH?

ORACLE_PATH is one of Oracle’s environment variables that helps sqlplus (or similar tools) locate .sql files without requiring the full path.

Example:

Let’s say in your .bash_profile or .bashrc you have:

 
export ORACLE_PATH=/u01/app/oracle/sql

With this in place, if there’s a file named checkasm.sql in that directory, you can run it in SQL*Plus simply by:

 
SQL> @checkasm

No need to type the full path.

📌 Related article on my site (about customizing the SQL*Plus prompt):
Prompt changing on SQL*Plus

Script Introductions

Each script can be introduced with its title, quick run path, and a short description:

blocking_session.sql

  • Purpose: Shows sessions that are blocking others. Much faster than any other method to see what type of blocker, what kind of wait, and when it’s happening.

  • Best for: Quick diagnosis of locking issues.

checkasm.sql

  • Purpose: Check ASM disk usage.

  • Tip: Should be part of your daily checklist if you use ASM.

checkgrd.sql

  • Purpose: Shows the archive log gap between Primary and Standby.

  • Tip: Daily checklist item.

checkrman.sql

  • Purpose: Displays details of the latest RMAN backup jobs.

  • Tip: Daily checklist item.

invobj.sql

  • Purpose: Finds INVALID objects.

  • Usage: I always run this before and after any database change to ensure my work didn’t invalidate objects.

lrun.sql

  • Purpose: Shows long operations (longops).

  • When: Whenever users report slowness, this is one of the first checks I make.

inst.sql

  • Purpose: Displays instance info such as role, hostname, logins, etc.

  • When: Before executing sensitive commands like shutdown, I always check this to make sure I’m on the right system.

find.sql

  • Purpose: Search for a table or view name in the dictionary using &tablename.

  • Example: Suppose you know there’s a view about the scheduler but don’t recall the exact name. Just type part of it, and you’ll get the full list.

login.sql

  • Purpose: Customize the SQL*Plus prompt with user and SID.

  • Benefit: You can immediately see the session number. Very useful if you need to troubleshoot or kill a session quickly.

who.sql

  • Purpose: Shows currently active users along with system and program info.

space.sql and prespace.sql

  • Purpose: Reports tablespace usage (dynamic and static).

  • Usage: Run prespace.sql the first time to create helper views, then space.sql works smoothly afterwards.

topactive.sql

  • Purpose: Identifies SQLs being run concurrently by multiple active sessions.

  • Benefit: Helps spot performance issues faster.

redo2.sql

  • Purpose: Shows the number of redo logs generated per hour per day.

  • Benefit: Great for spotting unexpected spikes in redo volume.

sid.sql

  • Purpose: Full info about a specific session using its SID.

  • Note: I didn’t write this script myself; I first used it at Aria, and since then I take it with me everywhere.

sid_sql.sql

  • Purpose: Shows the last SQL executed by a specific session.

checkfra.sql

  • Purpose: Checks how much of the Fast Recovery Area (FRA) is used vs allocated.

  • Tip: Another daily checklist item.

Final Thoughts

These scripts are simple — but vital. Just like that pencil behind your ear.
If you keep them in your ORACLE_PATH, you can run them anytime with just @filename and cover most of your day-to-day DBA needs.

📂 GitHub repository:
https://github.com/vahiddb/oracle-dba-scripts