How do I run an SQLPlus script to perform several Administration functions?

HI,

     I have a few administrative commands I need to run against 3 different Oracle databases on the same instance. What is the syntax to connect to each db and execute commands via batch file?

connect to Database A
SQL>Revoke execute on UTL_FILE from Public;
SQL>Revoke execute on UTL_TCP from Public;
SQL>Alter user CTXSYS identified by Test12 account lock password expire;

connect to Database B
SQL>Revoke execute on UTL_FILE from Public;
SQL>Revoke execute on UTL_TCP from Public;
SQL>Alter user CTXSYS identified by Test12 account lock password expire;

Could you recommend a good link\book for running batch files in SQLPlus?

Thank You, Missymadi

missymadiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dalebettsConnect With a Mentor Commented:
windows batch file?

The above would be no good in Windows environment. For windows just create test.bat, with:

set ORACLE_SID=<sid>
sqlplus username/password
Revoke execute on UTL_FILE from Public
/
Revoke execute on UTL_TCP from Public
/
Alter user CTXSYS identified by Test12 account lock password expire
/
0
 
ajexpertCommented:
Here is the syntax

sqlplus /nolog <uid>/<pwd>@<instance> @<scriptpath>
0
 
dalebettsCommented:
Create two files, one named admina.sql with the following:

Revoke execute on UTL_FILE from Public;
Revoke execute on UTL_TCP from Public;
Alter user CTXSYS identified by Test12 account lock password expire;

and one named adminb.sql with:

Revoke execute on UTL_FILE from Public;
Revoke execute on UTL_TCP from Public;
Alter user CTXSYS identified by Test12 account lock password expire;

And then to execute from the SQL prompt type:

@admina or @adminb

You have to ensure that files are in your current directory as you load sqlplus otherwise you have to supply the full path name for example if the files are store in C:\scripts

SQL> @C:\scripts\admina

You don't have to put the .sql suffix on the end of the filename when executing, it will work both ways.

Regards
0
 
ajexpertCommented:
dalebetts:
I think missymadi is asking to execute from Batch File
0
 
DavidSenior Oracle Database AdministratorCommented:
Extending AJ's contribution:
#!/usr/bin/sh
sqlplus /nolog <uid>/<pwd>@<instanceA> <<EOF
Revoke execute on UTL_FILE from Public
/
Revoke execute on UTL_TCP from Public
/
Alter user CTXSYS identified by Test12 account lock password expire
/
EOF

And repeat as needed.  You might want to create a new question and learn how to pass the SID name as an argument to the batch file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.