What is a default tablespace for a user?

Why is user SYS setup in oracle to have assigned default tablespaces SYSTEM and temporary table space TEMPORARY and the use SYSTEM is setup to have default USR and temporary SYSTEM?

I also notice that another application was creating user with a temporary tablespace SYSTEM.

Shouldn't it all users be assigned TEMPORARY as their temporary table space. After all, isn't the reason the tablespace is defined as of type temporary?

And this USR tablespace, is it really necessary? The only one assigned to it is SYSTEM and there is no objects in the table. Shouldn't system be using the SYSTEM tablespace as default?

Ans1:
Why things are done a certain way could only be answered by the developers. At
least things are moving away from everything defaulting to the system tablespace,
but high on my wish list would have to be two new initialization parameters,
default_tablespace and temporary_tablespace!

SYS probably defaults to the SYSTEM tablespace 'cause that's where we want the
data dictionary and it saves having to put a tablespace clause on lots of object
creation statements. SYSTEM is probably safe to change from its defaults, but as
always, err on the side of caution and change it back again before an upgrade,
"just in case".

Ans2:
Sys has a temp tablespace of system because an instance must have the system tablespace online and therefore it means that sys's default and temp tablespace are always online ..

Ans3:
There were some pretty good posts on this topic a couple of months back. You can search the archives.

The consensus was that SYSTEM is just a DBA id that Oracle comes with that Oracle products like Forms and ReportWriter expect to be their owner. It is safe to change the default and temporary tablespace parameters for it.

SYS on the other hand should be left alone. It is the database owner and is used to start, stop, recover, and upgrade the database and should not be used for anthing else, except running queries or doing grants on sys owned objects like the dbms packages and v$tables as a part of normal operations and procedures.

Ans4:
> Why is user SYS setup in oracle to have assigned default tablespaces SYSTEM
> and temporary table space TEMPORARY and the use SYSTEM is setup to have
> default USR and temporary SYSTEM?

Oracle standard, feel free to change.

> I also notice that another application was creating user with a temporary
> tablespace SYSTEM.

Not a good thing if the SYSTEM table space fills up, you database is
liable to stop working.

> Shouldn't it all users be assigned TEMPORARY as their temporary table space.

No realy, but it is a good idea.
> After all, isn't the reason the tablespace is defined as of type temporary?
>
Yes, but you can define multiply temporary tablespace to distribute IO.

> And this USR tablespace, is it really necessary? The only one assigned to it
> is SYSTEM and there is no objects in the table. Shouldn't system be using the
> SYSTEM tablespace as default?

I don't bother about USR, I just use SYSTEM.

Hosted by www.Geocities.ws