Re: SYS vs. SYSTEM

> 1. Can someone clarify the difference between logging in as SYS and SYSTEM
> via SQL*PLUS?

SYS and SYSTEM are two different accounts in the database. Each have their own tables, etc.  

> 2. If in both cases you log in as SYSDBA (via SQL*PLUS), is there a
> difference?

If you connect as SYSDBA, then there isn't a difference. See the following:

 SQL> connect / as sysdba
Connected.
 SQL> show user
USER is "SYS"
 SQL> connect system
Enter password:
Connected.
 SQL> show user
USER is "SYSTEM"
 SQL> connect system as sysdba
Enter password:
Connected.
 SQL> show user
USER is "SYS"  

> 3. What does it mean in each case if you **don't** specify "AS SYSDBA" when
> logging in via SQL*PLUS. Don't they both belong to SYSDBA role anyway?

SYSDBA is not a traditional role like CONNECT, RESOURCE, etc. You can't see it when you query DBA_ROLES:

 SQL> select role from dba_roles where role='SYSDBA';

no rows selected

The Concepts Guide explains SYSDBA in a lot of detail.

> 4. Why does Enterprise Manager Console, in some of my installations (can't
> remember, perhaps 8i?), not let me specify "AS SYSDBA" when logging in as
> SYSTEM, but only recognizes "AS NORMAL"?

Because SYSTEM may not have been granted SYSDBA to use. Therefore, NORMAL is only allowed.

> 5. Why with Oracle 9.2.0.3 am I able to log in as
> "SYS/CHANGE_ON_INSTALL_at_mydatabase AS SYSDBA" when the database setup
> required me to change the password for SYS and SYSTEM? It also allows me to
> log in as SYSTEM/MANAGER as well, although I supplied a new password during
> database creation. These are fresh databases newly created under 9.2.0.3,
> not migrations.

Is the user you are logged into the server part of the DBA group (Unix) or ORA_DBA group (Winz)? If that is the case, then you are granted access to the database. Try to do this from a remote system.

Most of this is explained in the Concepts Guide and Administrator's Guide. You can find copies of these docs, and many more, on http://tahiti.oracle.com.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"