Re: SYSTEM's default and temp tablespace

In article <394E2846.1F629839_at_mar.dfo-mpo.gc.ca>,   James Hanway <hanwayj_at_mar.dfo-mpo.gc.ca> wrote:
> Morning all,
>
> Whats the general rule for the SYSTEM account's default/temporay
> tablespaces?
>
> I've always thought *both* should be set to the SYSTEM tablespace, but
 I
> have seen a number of other setups like: SYSTEM/TEMP, TOOLS/TEMP, etc.
> Is SYSTEM/SYSTEM the generally accepted defualt/temporary tablespace
> setup?

If you should happen to run a sort as system (say, to generate some management reports), and the sort happens to require more space than sort_area_size (hey, it could happen), then a temporary segment will be created in the system tablespace. This does not go away, since Oracle thinks if you used it once, you will probably use it again, so it hangs around in your system tablespace. Naturally, system tablespace is critical, and there is no {cheap|sane} way to defragment it, so the net effect is you are using up a critical resource for no good reason.

jg

--
These opinions mine
mailto:joel-garry_at_nospam.home.com
Remove nospam to mail
http://ourworld.compuserve.com/homepages/joel_garry


Sent via Deja.com http://www.deja.com/
Before you buy.