Oracle 11g SYSDBA login

Question

I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use SQLPLUS "/ AS SYSDBA". But I want to connect as sysdba using a gui tool. So what is the equivalent USERNAME/PASSWORD which I can use to connect to the database (I think sysdba is just a role and has nothing to do with username or password. The '/' defaults to some value)

Also I tried to grant SYSDBA TO a user(say TESTUSER/TESTPASSWORD) using GRANT command. But at the next login the access seems to get reverted somehow.

Need your suggestion to resolve this issue .

Thanks in advance

Answer 1

It depends on the GUI but they all should have a way of selecting the SYSDBA privilege. Toad has it appear as a drop-down box called 'Connect as:' as does Oracle Enterprise Manager. For SQL Developer you use the 'Role' box when creating a new connection.

As far as / as sysdba is concerned it just means that operating system authentication is being used so no password is needed. You can only use it if you are logged in as the oracle user or a member of the dba group (or for Windows, I believe as an administrator).

When you grant a user the SYSDBA privilege it only means they have the option to log in with the SYSDBA privilege and not that they will have dba privileges when they log in normally. The same is true for the SYS user: you can log in as SYS without the SYSDBA privilege and so lack the ability to shutdown the database, for example.

Answer 2

I just use the java "sqldeveloper" client tool that Oracle provides for free. Before you do that though, you need to tell Oracle to allow connections from remote machines: its similar to MSSQL and MySQL in that respect.

Using 11g express, the user that creates the schema(database) is the user that is the dba user (by inference).