RE: Shrink Temp Tablespace

Thank you to the ones who responded. You all suggested the same thing and it did work.  

Thank you for your time! Laura  

From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com] Sent: Thursday, January 05, 2006 4:50 PM To: Burton, Laura; oracle-l_at_freelists.org Subject: RE: Shrink Temp Tablespace  

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Burton, Laura
Sent: Thursday, January 05, 2006 5:41 PM
To: oracle-l_at_freelists.org
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