• Arun Kumar

SQLPATH Environment Variable in Oracle

To execute any .sql script, we will have to go to specific folder which contains .sql script, start sqlplus and then run the script. It would be amazing if we can run any .sql script inside SQLPLUS from any location!


What is SQLPATH Variable?


SQLPATH points a location on server which contains all the .sql scripts. Anytime you execute a .sql script without giving the script location, sqlplus will first search the script inside SQLPATH location.


If the script is not available in the SQLPATH location, then sqlplus search the script in the current folder where you started sqlplus from.



Set SQLPATH Parameter


First create a location which will contain all your .sql scripts

mkdir -p /home/oracle/scripts

Copy all the .sql scripts into above location and set SQLPATH parameter in .bash_profile

export SQLPATH=/home/oracle/scripts

Now you can be in any location, start sqlplus and simply execute any .sql script

cd /tmp                            --> start sqlplus from /tmp loc
sqlplus / as sysdba
SQL> @database-size.sql            --> runs from $SQLPATH loc

Enjoy! 😋😋😋

1,106 views

Recent Posts

See All

Oracle Optimizer determines the cost of each execution plan based on database, schema, table and other statistics. The changes inside database result in stale statistics. As a DBA, you must gather sta

Imagine an application user gave accidental salary hike of 50% instead of 5% to all employees! To initiate a database point-in-time recovery, we must know the exact time the query was executed against