Test of parameter NOSYSDBA

# create the password file with nosysdba=n
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=n

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:02:59 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy>

# create the password file with nosysdba=y
[dummy1]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password force=y nosysdba=y

# test password file authenticated logon via sqlnet as sysdba
[dummy1]$ sqlplus sys/password@dummy as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:04:33 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:

# test password file authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus sys/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


Enter user-name:

# test dictionary authenticated logon via sqlnet with no sysdba

[dummy1]$ sqlplus system/password@dummy

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:34 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYSTEM @dummy>

# test local OS authentication as sysdba

[dummy1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 12 12:05:58 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SYS AS SYSDBA@dummy1>