tsejahtera
asked on
TEMP SEGMENT FULL
HI ..i received 0ra 1652
saying that my temp tablespaces is full.
if i don't do anything (addding new datafile or resize)
,is oracle will recycle the temp segment
to do sorting , etc.
if i restart the database, is it going to
empty back the temp segment.
i am using Oracle 8i on solaris.
TQ
saying that my temp tablespaces is full.
if i don't do anything (addding new datafile or resize)
,is oracle will recycle the temp segment
to do sorting , etc.
if i restart the database, is it going to
empty back the temp segment.
i am using Oracle 8i on solaris.
TQ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The following works for me (Windows XP Professional) while in the $ORACLE_BASE\oradata\$ORAC
1) alter database tempfile 'c:\oracle\oradata\sneaker
2) host erase temp.ora
3) alter tablespace temp add tempfile 'c:\oracle\oradata\sneaker
The following shows a list of the commands and the results on my system.
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
10:51:58 By your command, human>host dir temp.ora
Volume in drive C has no label.
Volume Serial Number is 18A8-A11B
Directory of C:\Oracle\oradata\sneakers
03/31/2003 10:51 110,108,672 TEMP.ORA
1 File(s) 110,108,672 bytes
0 Dir(s) 15,041,400,832 bytes free
10:50:44 By your command, human>alter database tempfile 'c:\oracle\oradata\sneaker
Database altered.
Elapsed: 00:00:00.07
10:51:21 By your command, human>host erase temp.ora
10:51:29 By your command, human>alter tablespace temp add tempfile 'c:\oracle\oradata\sneaker
Tablespace altered.
Elapsed: 00:00:00.06
10:51:58 By your command, human>host dir temp.ora
Volume in drive C has no label.
Volume Serial Number is 18A8-A11B
Directory of C:\Oracle\oradata\sneakers
03/31/2003 10:51 10,493,952 TEMP.ORA
1 File(s) 10,493,952 bytes
0 Dir(s) 15,030,906,880 bytes free
10:52:06 By your command, human>
1) alter database tempfile 'c:\oracle\oradata\sneaker
2) host erase temp.ora
3) alter tablespace temp add tempfile 'c:\oracle\oradata\sneaker
The following shows a list of the commands and the results on my system.
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
10:51:58 By your command, human>host dir temp.ora
Volume in drive C has no label.
Volume Serial Number is 18A8-A11B
Directory of C:\Oracle\oradata\sneakers
03/31/2003 10:51 110,108,672 TEMP.ORA
1 File(s) 110,108,672 bytes
0 Dir(s) 15,041,400,832 bytes free
10:50:44 By your command, human>alter database tempfile 'c:\oracle\oradata\sneaker
Database altered.
Elapsed: 00:00:00.07
10:51:21 By your command, human>host erase temp.ora
10:51:29 By your command, human>alter tablespace temp add tempfile 'c:\oracle\oradata\sneaker
Tablespace altered.
Elapsed: 00:00:00.06
10:51:58 By your command, human>host dir temp.ora
Volume in drive C has no label.
Volume Serial Number is 18A8-A11B
Directory of C:\Oracle\oradata\sneakers
03/31/2003 10:51 10,493,952 TEMP.ORA
1 File(s) 10,493,952 bytes
0 Dir(s) 15,030,906,880 bytes free
10:52:06 By your command, human>
P.S. Sorry I forgot to have the datafile TEMP.ORA auto extend.
alter database tempfile 'c:\oracle\oradata\sneaker
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
alter database tempfile 'c:\oracle\oradata\sneaker
Bertram Moshier
Oracle Certified Professional 8i and 9i DBA
http://www.bmoshier.net/bertram
ASKER
tq, i have the chance to restart the database and the temp
tablespace was zerorized.
tq
tablespace was zerorized.
tq
ASKER
saying that i cannot resize.
my temp now is 20GB size.