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.

ORA-00060: deadlock detected while waiting for resource

ORA-00936:missing expression

ORA-02063 remote db error and how to fix it

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-04091: table is mutating trigger

ORA-00604: error occurred at recursive SQL level string

PLS-00222: no function with name exists

ORA-00600: internal error code arguments:

ORA-21000:error number argument to raise_application_error is out of range

ORA-00257: archiver error. Connect internal only, until freed.

ORA-00001: unique constraint

ORA-00017: session requested to set trace event