Saving and Running Scripts in Oracle

Back to: Oracle DBA Tutorials

Saving and Running Scripts in Oracle Database

In this article, I am going to discuss Saving and Running Scripts in Oracle Database. Please read our previous article where we discussed Locking and Unlocking User Accounts in Oracle.

Saving and Running Scripts in Oracle Database

In this article, we will learn how to save and run the scripts using sqlplus.

Step 1: Open the Linux machine and log in as oracle user. Open terminal. Check the present working directory

Step 2: Connect to the database as “Sqlplus / as sysdba”.

Step 3: Run a simple query. This might be any query so that we need to check how to save a script.

Step 4: Suppose I have to save this query and in the output there is a command to save the query.

Command: save filename(save script.sql)

There must be a doubt about where the file has been saved. In the beginning, we have seen the present working directory. The file will save in the same path.

You can see the file is saved in the same folder as above. The files present with the script.sql is the same in the above /home/oracle path.

Step 5: There is a script present in the file system. We need to run that file in the database. We have a simple step to run any file in the database.

Command: @filename.sql

Step 6: Let’s try one more time in other directories so that it will be easy for the next time while saving the script. Go to the desktop folder and open the terminal and login into the database as “sqlplus /as sysdba”

Run any sample query on the database. For ex: select instance_name from v$instance;

Try saving this query and output using the command: save script.sql

Now, this file is saved in the desktop folder because we have changed the directory to the desktop.

Now we will run this file again to test if the script is working fine or not.

The script is working fine and we got the output.

Step 7: In the future, there will be a situation where while logging into the database itself we need to run a script. In that case, we use this method. This method invoked the script file while logging in to the database and run the script without any interruptions. Now we will try to run the script while logging into the database. It is the same method as we connect to the database using tnsnames.

Syntax: sqlplus username/password@database_aliasname @script.sql

Command: sqlplus system/goodluck@jayadb @script.sql

We have tried this method using the system user and completed it successfully without any interruptions. Let’s try to run this using the sys user. We have to enter the role while running the script.

Command: sqlplus sys/goodluck@jayadb as sysdba @script.sql

We have learned how to save a script and how to run a script in the SQL plus and outside the sqlplus by invoking the sqlplus.

In the next article, I am going to discuss Saving and Running Scripts in Oracle Database. Here, in this article, I try to explain Saving and Running Scripts in Oracle Database and I hope you enjoy this Saving and Running Scripts in Oracle Database article.