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.

Action: Try connect sys/password as sysdba or connect sys/password as sysoper.

 

ORA-28009 oracle error is raised when the sys user tries to connect without providing "as sysdba" . This s controlled by the O7_DICTIONARY_ACCESSIBILITY parameter. When this parameter value is set to FALSE, sys users experience this ORA-28009 error. Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings.

 

Example for the ORA-28009 error:
Connecting as sys user without sysdba privilege.

SQL> alter user SYS identified by SYS;
SQL> connect sys/sys ERROR: ORA-28009: connection to sys should be as sysdba or sysoper Warning: You are no longer connected to ORACLE.

Connecting as sys user with sysdba privilege.
SQL> connect sys/sys as sysdba
Connected.
SQL>

O7_DICTIONARY_ACCESSIBILITY parameter info:
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE

Modifying the O7_DICTIONARY_ACCESSIBILITY parameter value:

SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile; System altered.

In order make the chnages effective shutdown and startup the database:
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE

Now sys user should be able connect with out "as sysdba" as verified below:

SQL> conn sys/sys Connected.

 

CONCLUSION:

 

Oracle Recommendation is not to set O7_DICTIONARY_ACCESSIBILITY to TRUE. So in order to avoid this ORA-28009 error, always explicitly specify " as sysdba " when connecting as sys user.

 

Search Results

User login

'