Re: I have a couple of questions

Welcome to Oracle. Since you spent the past several years learning SQL Server, you are in for an interesting few months.

Other than similarities in DML, you will find the two systems do not resemble each other very much. In some cases you can expect to stumble over similar terms which have completely different meanings. Some examples: 'schema' in Oracle is roughly what most other rdbms's call 'database'. An Oracle database is something much larger (and can carry literally hundreds of schemas) - you could almost think of it as a virtual disk array, tablespaces being roughly equivalent to virtual disks.

Your first stop should be with Oracle's TechNet at http://otn.oracle.com, and specifically into the Prodcuts section (for white papers and other glossy stuff that really does give an idea of the concepts) and into the Documentation section where you will find ALL the Oracle documentation that is supplied on the product doc CDs.

When you get to the Documentation site (also available through http://docs.oracle.com and http://tahiti.oracle.com) you really need to start with the Concepts manual. This provides a 5,000 foot overview of the system's internals and discusses most of the questions you ask.

Oracle's online documentation is comprehensive, also massive and initimidating for new users. Because of that, I personally recommend starting light with a book called "Oracle Essentials: Oracle9i, Oracle8i & Oracle8 " (see http://www.oreilly.com/catalog/oressentials2 for details). I find this a quick assist in explaining the new and different concepts, one level higher than the Concepts manual.

After that, you should be equipped to go after the manuals as well as the many excellent books that go into the areas that might interest you. When you're there, ask & we'll likely flood you with recommendations & comments.

A few pointers directly related to your email - the stuff in [] references the commands in the SQL Reference manual ...

• Connect and Resource are Roles. They are available for historical reasons. It is wise to learn about roles relatively soon (when you start studying security) and plan on replacing them with roles that you create that have appropriate privileges. (The two roles generally have a lot more, or at least different, privs than required.) [GRANT role TO user]
• Until a transaction is committed, only that session can see the pending changes. Even other instances of the same user can not 'peek' into the middle of a transaction in a different session. [COMMIT, ROLLBACK]
• Using the SYS account for daily work is a definite no-no. This is the same as the main 'Administrator' account in NT or Win 2000, etc (or root, in unix). It owns the 'sys tables', equivalent to owning the kernel. It's just too easy to shot yourself in the foot using that id. [CREATE user, GRANT role, GRANT priv]
• There is also a SYSTEM account, which generally should not be used in a production environment. Basically that is SYS's sidekick & has a few less rights (eg: it can not easily wreck the SYS schema and take the entire database down.)
• Both SYS and SYSTEM (as well as any other userid with DBA role or the appropriate privilege to select from any table) CAN see your user's table and data. Howeer, if no private synonym has been created in the account, and no public synonym exists, access to the table contents will require a qualified name by prefixing the table name with the schema name. [CREATE <public> SYNONYM, SELECT schema.table]
• Only users who need to own resource consuming objects like tables need to have a schema. All other users can access existing schemas according to the rules specificd by the appropriate object GRANTS. So you can have 10 users accessing the same schema (possibly owned by user 11), and each of the 10 users may have different permissions - by table and by operation. [GRANT SELECT ... ON table TO user]
• The SYSTEM tablespace is equivalent to the root file system in unix, or the C:\ for a production windows environment. You do not want most users placing their stuff there, so you create additional tablespaces as required. [CREATE TABLESPACE] and you want to ensure the user ids get those by default [ALTER USER DEFAULT TABLESPACE, ALETR USER TEMPORARY ...]