Connection As Sys Should Be As Sysdba Or Sysoper
Code is as follows -
- Jdbc Ora-28009 Connection As Sys Should Be As Sysdba Or Sysoper
- Oracle Connection As Sys Should Be As Sysdba Or Sysoper
But I am getting following error
'ORA-28009: connection as SYS should be as SYSDBA or SYSOPER' when reconnecting a SYSDBA session Description In v7.4, When you connect as the SYS user (as SYSDBA) and then go to Session Reconnect, SQL Navigator doesn't reconnect, with the error. ORA-28009 connection to sys should be as sysdba or sysoper Exception in Oracle ORA-28009 connection to sys should be as sysdba or sysoper ORA-28009: connection to sys should be as sysdba or sysoper Cause: Connect sys/password is no longer a valid syntax. If you try to connect as sys using SQL.plus it should be either SYSDBA or SYSOPER. From Oracle 9i onwards, this has been implemented in Oracle. All other users will work as normal with username and password.
I tried replacing user as 'sys as sysdba' as mentioned in this SO answer.
I also tried using
as suggested here
but here I am getting
Any suggestions?
1 Answer
The following does work. Only difference is the driver name
JayanJayanNot the answer you're looking for? Browse other questions tagged javaoraclegroovysysdba or ask your own question.
There are two ways to connect to Oracle as a administrator using sqlplus:
sqlplus sys as sysdbasqlplus system/manager
These accounts should be uses for different purposes, I suppose.
Which tasks are these two schemas meant for? When should I use one or the other among them?
Leigh Riffel4 Answers
SYS
- Automatically created when Oracle database is installed
- Automatically granted the
DBArole - Has a default password:
CHANGE_ON_INSTALL(make sure you change it) - Owns the base tables and views for the database data dictionary
- The default schema when you connect as
SYSDBA
Tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. Database users should not connect to the Oracle database using the SYS account.
- Automatically created when Oracle database is installed
- Automatically granted the
DBArole - Has a default password:
MANAGER(make sure you change it) - Used to create additional tables and views that display administrative information
- Used to create internal tables and views used by various Oracle database options and tools
Jdbc Ora-28009 Connection As Sys Should Be As Sysdba Or Sysoper
Never use the SYSTEM schema to store tables of interest to non-administrative users.
From the 11g Oracle Documentation:
SYS AND SYSTEM Users
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
SYSThis account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema.
The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
SYSTEMThis account can perform all administrative functions except the following:
Backup and recovery
Database upgrade
While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.
SYSDBA and SYSOPER System Privileges
SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.
You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.
The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.
When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
Leigh RiffelLeigh RiffelAn example of a major difference between SYS (or any other SYSDBA connection) and every other user: SYS can't do consistent read. One implication of this (there are others) is that you can't do a CONSISTENT=Y export as SYS using the old exp utility.
Oracle employee and expert Tom Kyte is of the opinion that you should rarely ever use either one. Regarding SYS, he points out that it works differently as the example above indicates, but more generally he considers them to be 'owned' by Oracle Corporation. If you make a change or add something in either schema and a problem occurs (e.g., a database upgrade fails), I suspect Oracle Support's answer would be, 'You shouldn't have done that.'
This sounds as if the poster is saying that there are only two ways to connect:
'There are two ways to connect to Oracle as a administrator using sqlplus:
An administrator account is any Oracle user account with either the SYSDBA privilege or the DBA role. SYS is a predefined user with SYSDBA, and SYSTEM is a predefined user with DBA. If there are n administrator accounts in a database, then there are n users who can connect with administrator privileges (by definition)--there are not just two of them.
Another point concerns SQL*Plus. You can connect as SYS using OS authentication at the OS prompt: sqlplus / as sysdba. You can also start SQL*Plus and then CONNECT / AS SYSDBA. You can specify a password at the OS prompt, in a CONNECT statement, or have SQL*Plus prompt you for it. You can use a net service name. You can (and should) change the SYSTEM password. And so on.
What the poster meant to say, I think, is that there are at least two administrator accounts in an Oracle database, and if SYSTEM has the default password, and if OS authentication is set up, then these are two (of many) examples of how SYS and SYSTEM can log in to the database using SQL*Plus.
The question of the difference between SYS and SYSTEM is different and has been answered.