March 8, 2011

Connect as sysdba

Sometimes, when working with Oracle database, you need to connect as sysdba or sysoper.
A user must connect AS SYSDBA or AS SYSOPER if he wants to perform one of the tasks that require sysdba or sysoper privileges(such as to shutdown or to startup an instance. If he connects as SYSDBA, he becomes SYS, if he connects as SYSOPER, he becomes PUBLIC.
On the command line, using sql-plus this is pretty simple. Just add "as sysdba" to your connect statement.
connect sys/my_secret_password as sysdba
However when you are working with a graphical user interface logging in as sysdba isn't that evident. Basically because the login box that you get presented just allows you to insert a username and a password. So where goes the "as sysdba" part?

Using SQL Developer

Just add the "as sysdba" statement after your username. I needed this not so long ago, I luckily found it after some Googl-ing. My sql-plus isn't up to par, so really being able to use a GUI is really a time saver.

Using Quest Toad
If you are using Toad, the answer is fairly simple. They have provided a little drop down list called "connect as" which allows you to select between "normal", "sysdba" and "sysoper".