RE: Shrink Temp Tablespace
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <BurtonL@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Jan 2006 17:49:31 -0500
Laura,
I just did this quick test:
SQL> create temporary tablespace mjbtemp tempfile
'/export/home/oracle/mjbtemp01.dbf' size 1000m;
Tablespace created.
SQL> alter database tempfile '/export/home/oracle/mjbtemp01.dbf' resize
500m;
Database altered.
SQL> drop tablespace mjbtemp;
Tablespace dropped.
The trick is, in the ALTER DATABASE command, specify a 'tempfile', not a
'datafile', to get past the problem you're having.
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"There are 10 types of people in the world: Those who understand
binary, and those who don't."
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Burton, Laura
Sent: Thursday, January 05, 2006 5:41 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Shrink Temp Tablespace
Oracle 9.2.0.7
Wanting to shrink the temp tablespace. On metalink I found the 'alter
database datafile ' ... ' resize 500m' but this does not work; I guess
due to it being a temp tablespace it is not listed in the
dba_data_files. The error I get is that there is no datafile with the
information I entered. Could not find anything which added temp into
the mix.
I found where I can create another temp tablespace, point everyone to
that tablespace, and then drop the 'old' temp tablespace. A lot of
sugar for a dime it sounds like.
Any other way of doing this?
Thank you in advance,
Laura
BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD
Other related posts:
