Hi all,
Normally there are two ways of creating temporary tablespace
1. CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE
'/u02/oracle/data/lmtemp01.dbf'=20
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
2. CREATE TABLESPACE sort
DATAFILE '/u02/oracle/data/sort01.dbf' SIZE 50M
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
PCTINCREASE 0)
EXTENT MANAGEMENT DICTIONARY
TEMPORARY;
The difference I see in both the commands is one is Locally Managed
tablespace which can take advantage of the space allocation feature
using bitmap.IS it possible to create the second one as Locally managed
using=20 CREATE TABLESPACE sort
DATAFILE '/u02/oracle/data/sort01.dbf' SIZE 50M
DEFAULT STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
PCTINCREASE 0)
EXTENT MANAGEMENT LOCAL
TEMPORARY;

Also for temporary tablespaces created using first command, segments are
allocated during the first sort process and the space is not deallocated
after the sort is over. Suppose the datafile grows from 1 GB to 3 GB
during a sort process the space is not deallocated after the sort is
over.The tablespace shows as full when you view through enterprise
manager. If that is the case how will you find out whether you need to
add extra space to the tablespace for sort operations or there is space
available for sorting before starting a new long running process.

Thanks in advance

JAIMURUGAN
Confidentiality Statement:

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, please note that you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by return email.


Oracle LazyDBA home page