I have requirement where in,I need to remove a oracle user and this should be done in a shell script.I am new to both sql and shell scripting.I am using below script but not able to remove the user.Can any one help me out
#!/bin/sh
su - oracle;
sqlplus as / sysdba;
startup force;
drop user SM cascade;
You're running a series of commands in the shell, but they are not connected. Your su starts a new shell as the Oracle user, but then exits and your script continues as the original user. You then try to run SQL*Plus as that original user, and (with the command corrected) that OS user doesn't have permission to connect as SYSDBA - that is protected. And the startup/drop commands won't run until SQL*Plus has exited, when they are also just (invalid) shell commands.
Conceptually you want something more like this; running SQL*Plus as an su command rather than a shell, and using a 'heredoc' to give SQL commands:
#/bin/sh
su - oracle -c sqlplus -s -l / as sysdba << !EOF
drop user SM cascade;
exit;
!EOF
The startup command seems rather out of place there so I've left it out, but you can run whatever commands you like before the !EOF marking the end of the heredoc.
But mixing su with a heredoc may not work ("standard in must be a tty"?) depending on your environment. You can put everything except the su - oracle -c in a script instead:
#!/bin/sh
sqlplus -s -l / as sysdba <<!EOF
drop user SM cascade;
exit;
!EOF
and call that from the command line with su - oracle -c "<path to your script>", but your script then needs to be visible to and executable by the Oracle account.
Having a script lying around that will drop a user, especially with cascade, seems rather dangerous; all too easy to run it accidentally at some point, maybe years later, maybe wiping out a lot of important data. I imagine this is just an example but it doesn't really seem like something you'd script at all, particularly for a normal user to be able to run whenever they like.
sqlplus / as sysdba