Temp segment size problem!

Printable View

08-19-2002, 05:57 AM
amitstora

Hi!

Working on Oracle v8i with OS as NT!

I have this problem:

The DB constains around 2 GB of data but the Temp segment consisting of temp01.dbf file
has shot up to 4 GB.This DB was created with default temp segment size and after that
import was done on this newly created DB with 2 GB of data.

Suddenly, the Temp segment consisting of temp01.dbf file has shot up to 4 GB.

Now i am left with only 15 MB of space in D drive where Oracle is stored.Other drives are not
having much space left either.

Size of Oradata folder is 6 GB. I need to resize the temp segment to around 500 MB.I hope
that this size would work with no problems for 2 GB data and that not much sorting would
be done on this temp size!I hope I am correct!

Moreover,I canot resize the temp file to 500 MB as Oracle throws up this error message:
used size is beyond the resize file size.I believe I have to drop the existing temp file of
4 GB and recreate a new temp file of 500 MB with the following command :

ALTER TABLESPACE TEMP
DATAFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' SIZE 500M
AUTOEXTEND ON NEXT 1M;

Is this command Ok for the temp segment and would the DB work if I drop and recreate a new
temp segment as above!Pleae advise!

Please correct me I am wrong!

Regards,

Amit.
08-19-2002, 06:07 AM
julian

Drop the TEMP tablespace and run this:

Code:

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

You might face a problem with dropping the TEMP tablespace (the drop command might just hang). If possible, shutdown, startup and then drop and run the statement above.
08-19-2002, 07:50 AM
chao_ping

Quote:

Originally posted by julian
Drop the TEMP tablespace and run this:

Code:

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\ORACLE\ORADATA\PVPL\TEMP01.DBF' SIZE 512M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

You might face a problem with dropping the TEMP tablespace (the drop command might just hang). If possible, shutdown, startup and then drop and run the statement above.

If drop the tablespace hang, so will shutdown immediate,right?
IF he is using temporary tablespace, the drop should be pretty fast.
08-19-2002, 08:06 AM
julian

Quote:

Originally posted by chao_ping
If drop the tablespace hang, so will shutdown immediate,right?

Right!