Re: Difference between "create temporary tablespace..." and "create tablespace .... temporary"?

Hi Bob,

"bob" <freebob_at_vip.sina.com> wrote in message news:aqa7lq$1d0$1_at_mail.cn99.com...
> create temporary tablespace temp1 tempfile .......

Creates temp file(s) (rather than a datafile) which doesn't allocate storage when created (allocates when blocks are required), which doesn't appear in the data file views, which can be created in a locally managed tablespace, which doesn't have to be backed up and which can't be recovered.

> and
> create tablespace temp2 .......temporary;

Creates data file(s) (rather than a temp file) which allocates all storage when created, which does appear in the datafile views, which can only be created in a dictionary managed tablespace and which also doesn't have to be backed up per se although your backup script has to do a bit more work.

>
> what's difference If user's default temporary tablespace is temp1 or
temp2?

From a users point of view there should be minimal difference although if many temp extents need to be created, it would be beneficial to be in a LMT.

Both options are better than a user being defined to a permanent temporary tablespace, or heaven forbid the System tablespace although my recommendation would be to use temp files and LMTs.

Cheers

Richard

>
>
>
Received on Wed Nov 06 2002 - 01:22:07 CST