Sometimes you can't shrink a tempfile because blocks are being used.
The best way is to create a new tempfile, then re-assign users to use the new one.
Then drop the old one and do the same process over to re-create the original temp space:
E.g.:
How to shrink the TEMPORARY TABLESPACE:
1) Create new temp tablespace
-------------------------------
create temporary tablespace TEMP1 tempfile '/your_path_here/temp02.dbf' size 500M
autoextend off extent management local uniform size 1024K;
2) Re-assign all existing users to use the new TEMP space:
------------------------------------------------------------
(dynamically create a script that re-assigns all users)
set pagesize 0;
select 'alter user '||username||' temporary tablespace TEMP1;' from dba_users;
...
...
(then run that script)
alter user SYS temporary tablespace TEMP1;
alter user SYSTEM temporary tablespace TEMP1;
alter user OUTLN temporary tablespace TEMP1;
alter user DBSNMP temporary tablespace TEMP1;
...
...
etc...
3) Change the system wide default TEMP tablespace:
--------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
4) Drop old TEMPORARY tablespace:
------------------------------------
DROP TABLESPACE YOUR_ORIGINAL_TEMP_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
/
Then simply redo the process with step 1) if you want to re-create the Original TEMPORARY
TABLESPACE with a different (most likely smaller size).
Chris :-)
-----Original Message-----
From: Nkansah Derek [mailto:oracledba-ezmlmshield-x61646444.[Email address protected]
Sent: Tuesday, September 05, 2006 10:37 AM
To: LazyDBA Discussion
Subject: RE: temp file resize
TEMPFILE instead of DATAFILE!!!
-----Original Message-----
From: Hal Owens
[mailto:oracledba-ezmlmshield-x16126630.[Email address protected]
Sent: 24 August 2006 20:23
To: LazyDBA Discussion
Subject: RE: temp file resize
Thanks!
>From: "Chandrasekhar Rao Dharmapurinagaraja "
><oracledba-ezmlmshield-x76248810.[Email address protected]
>To: "LazyDBA Discussion" <[Email address protected]
>Subject: RE: temp file resize
>Date: Fri, 25 Aug 2006 00:31:39 +0530
>
>Change ALTER DATABASE DATAFILE to ALTER DATABASE TEMPFILE
>
>-----Original Message-----
>From: Hal Owens
>[mailto:oracledba-ezmlmshield-x66176189.[Email address protected]
>Sent: Friday, August 25, 2006 12:22 AM
>To: LazyDBA Discussion
>Subject: temp file resize
>
>Tried to resize a tempfile using:
>
>ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\WAFPDEV\TEMP01.DBF' RESIZE
>50M;
>
>
>But got this error:
>
>ORA-01516: nonexistent log file, datafile, or tempfile
>"E:\ORACLE\ORADATA\WAFPDEV\TEMP01.DBF"
>
>
>Double checked name and got:
>
>SELECT FILE_NAME FROM DBA_TEMP_FILES;
>
>E:\ORACLE\ORADATA\WAFPDEV\TEMP01.DBF
>
>
>Any ideas?
>
>Thanks
>
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
>terms:http://www.lazydba.com/legal.html
>
>
>Disclaimer:
>This email and any files attached hereto are confidential and intended
>for the sole use of the individual or entity to
>which they are addressed. If the reader of this message is not the
>intended recipient, you are hereby notified that any
>unauthorized disclosure, dissemination, distribution, copying or the
taking
>of any action in reliance on the information
>herein is strictly prohibited. If you are not the intended recipient,
>please contact the sender and delete all copies.
>
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these
>terms:http://www.lazydba.com/legal.html
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
This e-mail (and any attachments) contains information, which is confidential and intended solely for the attention and use of the
named addressee(s). If you are not the intended recipient you must not copy, distribute or use it for any purpose or disclose the
contents to any person. If you have received this e-mail in error, please notify us immediately at [Email address protected] The
information contained in this e-mail (and any attachments) is supplied in good faith, but the sender shall not be under any
liability in damages or otherwise for any reliance that may be placed upon it by the recipient. Any comments or opinions expressed
are those of the originator not of NTT Europe Ltd unless otherwise expressly stated.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page