Create a Batch with User Login to database and Running Script | Oracle Database

How to Create a Batch with Autologin to database and Running Script - Oracle Database

How to create a script to perform user autologin and run a SQL script in Oracle Database? Batch file to perform automatic tasks in Oracle Database

Create a Batch with Autologin to database and Running Script

While doing everyday activities as a DBA, you often think of making your tasks a lot simpler which are quite repetitive in nature. Everyday activities includes checking Audit logs, Alert Log, Database performance, Backups Monitoring and much more. Going into depth of there activities also includes fetching data from various VIEWS for DBAs, which also mean you'll need to login to database again and again (depending upon session timeout). There are a lot of ways to make your task simpler and this post is regarding giving you idea of how to achieve the same.

This article will guide you to create a Batch file which will help you run your SQL script and will also perform the user login. The LOGIN will be having 2 scenarios,

• the Batch will ask you to enter the credentials
• the Batch will be having the credentials by itself and will perform AUTOLOGIN

If you want to run a script from some particular USER and want your program to PROMPT the Login details and then call the program, you can do something like this,

------------------------------------------CODE BEGINS------------------------------------------------------------
echo off

cls
echo.
echo Enter the User Name Used to Connect to the Oracle Database
echo.
SET /P APPUSER=Name: 

echo.
echo Enter the Password
echo.
SET /P APPPWD=Password: 

echo.
echo Enter the Oracle Host String for the Database Connection
echo.
SET /P STRN=Host: 

cls
echo.
echo.
echo Extracting table data
echo on
sqlplus %APPUSER%/%APPPWD%@%STRN% @E:\Oracle\Scripts\xxxxxx.sql
------------------------------------------CODE ENDS------------------------------------------------------------
And when you'll run the same, the credentials will be asked like

------------------------------------------CODE BEGINS------------------------------------------------------------
Enter the User Name Used to Connect to the Oracle Database

Name: sys

Enter the Password

Password: sys

Enter the Oracle Host String for the Database Connection

Host: oradb11g as sysdba

------------------------------------------CODE ENDS------------------------------------------------------------

OR


If you want to run the same script again and again like 'UTLRP.SQL' then you can create a .BAT or .SH file like the one shown below which will store the login credentials and the script file to be called. See below:

------------------------------------------CODE BEGINS------------------------------------------------------------
echo off

SET APPUSER=sys
SET APPPWD=sys
SET STRN=oradb11g as sysdba

echo on
sqlplus %APPUSER%/%APPPWD%@%STRN% @?\rdbms\admin\utlrp.sql
------------------------------------------CODE ENDS------------------------------------------------------------

Whatever suits you, just copy the text to a notepad and save it with extension .BAT (for batch file). Double click on the saved .BAT file and it will automatically login to database and recompile the invalid objects.

Below is the output of 2nd script which I created to recompile objects,

------------------------------------------CODE BEGINS------------------------------------------------------------
Extracting table data

C:\Users\parain\Desktop>sqlplus sys/sys@oradb11g as sysdba @?\rdbms\admin\utlrp.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jun 9 15:25:50 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2018-06-09 15:25:55

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2018-06-09 15:26:09

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

Function created.

PL/SQL procedure successfully completed.
------------------------------------------CODE ENDS------------------------------------------------------------


I hope this helps !!