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
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
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
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
I think missymadi is asking to execute from Batch File
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.
#!/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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sqlplus /nolog <uid>/<pwd>@<instance> @<scriptpath>