This same discussion came up recently for Windows systems, so you must be suffering the equivalent issue on *NIX. So, for example, on Linux, we expect the user owning the Oracle software to be identified in /etc/group as in the dba group. Here is the other recent discussion on this topic:
And adding "as sysdba" after the user in the username input field has no effect.
SQL Developer Team
ok, but in this case please explain how these people have managed bypass this restriction Problem connecting to Oracle "as sysdba" from Oracle SQL Developer
What restriction are you talking about? That link doesn't show anyone using 'sys as sysdba' as the username.
Your first image shows you trying to use 'Advanced' as the connection type.
Both images show you using 'sys as sysdba' for the username.
Use the proper username and post the results for a basic connection. Also are you using a password file?
Also, why do you think you need to login as SYS?
because i see SYSTEM has not all dba permissions.
SQL> select * from dba_sys_privs where GRANTEE = 'SYS';
159 rows selected.
SQL> select * from dba_sys_privs where grantee = 'SYSTEM';
44 rows selected.
and under SYSTEM user i do not see some v$tables and views.
That is why i need to login only as SYS via sqldeveloper so as sqlplus is rather inconvenient for me. Especially if i have to work with huge tables. And i hope you understand.
I think the various links posted in this discussion are all helpful. However, as Jeff says, this is all about a specific point of database security configuration, over which SQL Developer has no influence. As such, we always prefer to redirect you to a more appropriate discussion space, e.g., General Questions.
Note that when you logon as sysdba via SQL*Plus / oracle OS user, you are talking about a special case which SQL Developer can never take advantage of -- SQL Developer connects using a JDBC Thin or OCI/Thick driver. So relevant items (discussed in the links) are things like...
1. Being in the DBA group
2. Whether or not sqlnet.ora contains a value for SQLNET.AUTHENTICATION_SERVICES=
3. Whether a (ORAPWD) password file exists (the value for (2) defaults to EXCLUSIVE, but that's the same as NONE if no password file exists).
4. Whether the user you connect with appears in the result set of SELECT * FROM V$PWFILE_USERS;
And so on...
[oracle@node1 sqldeveloper]$ groups oracle
oracle : dba
[root@node1 //]# cat /d01/oracle/PROD/11.2.0/network/admin/PROD_node1/sqlnet.ora | grep -i SQLNET.AUTHENTICATION_SERVICES=
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> SELECT * FROM V$PWFILE_USERS;
no rows selected
SQL> show user;
USER is "SYS"
It would seem your lack of SYSDBA access, except under the special case of OS authentication, is confirmed by no rows selected from the SELECT * FROM V$PWFILE_USERS query. As mentioned before, responders on this SQL Developer tool forum tend not to be experts in that area. You will be better served by responses from the General Questions forum.
I suppose moving or renaming the ORAPWD file would result in no rows selected, as would revoking SYSDBA from SYS (if that's even possible!), but let's see what ideas experts on the other forum have.
should i move this topic to another (general) forum or just create a new one there?
Although posts in multiple forums are not usually appropriate I think it is in this case so I suggest that you create a new thread in the Database General forum
In that new thread provide a link to this current thread but provide the relevant info needed for someone there to help you. A responder in that thread should NOT have to read this current thread to find out what your problem is; this thread will just be for supporting info if they need it.
In this current thread post a link to the new thread.