What is the difference between the users SYS and SYSTEM? What is the difference between the tables owned by SYS and SYSTEM? Do both users share the same tablespaces?
The SYS user is the owner of all of Oracle's internally maintained objects (the data dictionary, etc). The SYSTEM user is the "default" DBA account.
The objects created when a database is first created are owned by SYS who then gives access privileges to some accounts/roles (for example to the DBA role). Since SYSTEM has the DBA role, SYSTEM can see the objects created by SYS.
If you'll pardon the analogy, you can compare the relationship and capabilities of SYS and SYSTEM to a Prison Warden and a Prison Guard. The warden contols everything but gives the guards alot of duties related to the running of things. But, without the warden, the guards wouldn't have a job.
As far as the tablespaces, you may change the default tablespace for any user to anything you please after the database has been created. Initially however both SYS and SYSTEM are set to use the SYSTEM tablespace by default.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.