Link to home
Start Free TrialLog in
Avatar of missymadi
missymadi

asked on

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

Avatar of ajexpert
ajexpert
Flag of United States of America image

Here is the syntax

sqlplus /nolog <uid>/<pwd>@<instance> @<scriptpath>
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
dalebetts:
I think missymadi is asking to execute from Batch File
Avatar of David VanZandt
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.
ASKER CERTIFIED SOLUTION
Avatar of dalebetts
dalebetts
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial