Way around to remove datafile from TEMP
Bose, March 22, 2001 - 10:40 am UTC
Since the datafile belongs to TEMP tablespace, you can always drop the TEMP tablespace including contents and then delete the file/s physically from the OS.
what about dependencies
A reader, April 25, 2002 - 11:13 am UTC
I have temp tablespace very big compare to the db
also I have all users assigned temp for all temporary work
like sort..
what happen to them if I drop temp ?
April 25, 2002 - 6:32 pm UTC
They won't be able to sort to disk. They will get an error if you drop it and they need to sort to disk.
If you drop it, recreate it right away.
resizing tempfile in 9.0.2
A reader, August 28, 2002 - 10:24 am UTC
hi
is it possible to resize tempfile in 9.0.2?
I tried:
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 76618332 bytes
Fixed Size 453212 bytes
Variable Size 50331648 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter database tempfile 'D:\ORACLE\ORADATA\PTL920\TEMP01.DBF' resize 500M;
alter database tempfile 'D:\ORACLE\ORADATA\PTL920\TEMP01.DBF' resize 500M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
why? I just started the database and temporary tablespace has not been used yet!
August 28, 2002 - 10:44 am UTC
If this is a bitmapped temporary tablespace, you'll need to drop it right before shutting down and just recreate it upon startup.
They cannot be resized.
what a pain you cannot resize tempfiles in 9.2
A reader, September 07, 2002 - 9:28 pm UTC
that's really a pain!
any logic why they cannot resized?
September 08, 2002 - 9:27 am UTC
If this is a bitmapped temporary tablespace, you'll need to drop it right before
shutting down and just recreate it upon startup.
If you feel that is too much of a restriction, don't use that type of temporary tablespace.
well I can resize bitmapped tbsp in 8.1.7
A reader, September 26, 2002 - 3:18 pm UTC
Hi
you said you cannot resize bitmapped tablespace however I have temporary tablespace locally managed created as follows:
create temporary tablespace temp
tempfile 'D:\ORACLE\ORADATA\RMAN817\TEMP01.DBF' reuse;
it is exactly the same as a temp tablespace in 9i but in 8i you can resize this because I have done it but in 9i not. How so? Any difference between temp tablespace in 8i or 9i? (I dont see any actually, they are both locally managed, tempfiles)
September 26, 2002 - 3:39 pm UTC
that wasn't bitmapped in 8i that is why. (not talking about locally managed, talking about segment space managment, new with 9i)
Sagi, September 27, 2002 - 7:11 am UTC
Hi Tom.
q1) First of all what is the solutino for Tia's Problem. The link you gave was very useful indeed. But You have not told what Tia should do. As already he has executed the below commands on his DB already:
ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;
Now as we know you can not put the DATAFILE to ONLINE or take the TABLESPACE OFFLINE.
I think the Solution-1 to his problem would be:
ALTER DATABASE RECOVER DATAFILE 'filename' ;
ALTER DATABASE DATAFILE 'filename' ONLINE ;
Now to follow the correct method what you have shown in the link i.e. :
He can create a new TEMPORARY TABLESPACE by deciding the total space he needs.
CREATE TEMPORARY TABLESPACE <NewTempTablespace>......
with adequate space
Once this is over he can issue:
DROP TABLESPACE <OldTableSpaceName>
Please let me know is this the correct approach for his problem.
Lastely, you said
"I have temp tablespace very big compare to the db
also I have all users assigned temp for all temporary work
like sort..
what happen to them if I drop temp ?
Followup:
They won't be able to sort to disk. They will get an error if you drop it and
they need to sort to disk.
If you drop it, recreate it right away."
Can you give a small example for this. Please.
Note: SORT_AREA_SIZE is the space that determines the space in memory to be used for operations like UNION, DISTINCT, ORDER BY etc.
If We dont have enough SORT_AREA_SIZE then it creates TEMPORARY SEGMENTS which are on TEMPORARY TABLESPACE. Am I Right?
Regards,
Sagi
dropping a temporary tablespace and recreating it
sm, October 31, 2002 - 8:28 pm UTC
8.1.7.4.0 on HP-UX database size 2.5 g
I had a temporary tablespace that was created like
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE <dbf_file> SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 1048576K
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64K;
Because of a bad query by a user..I got ORA-1652: unable to extend temp segment by 8 in tablespace TEMP (in prod)
The size of the tablespace/datafile had gone to it's max
-- 1 g
I tried to resize it in PROD with
ALTER DATABASE TEMPFILE <dbf_file> RESIZE 500M;
It gave me an error:
*
ORA-03297: file contains used data beyond requested RESIZE value
Then I thought let me recreate the TEMP tablespace..because I also wanted to increase the uniform size of the extent from 64K to 512K (Sort_area_size 128k).
So, I went to an exact replica of our PROD database (TEST database)..and
dropped temp tablespace as:
drop tablespace temp;
and it worked O.K.
then when I tried to create the temp again..it said:
ORA-01119: error in creating database file <dbf_file>
ORA-27038: skgfrcre: file exists
So, I tried to drop the tempfile as:
alter database tempfile <dbf_file> drop;
it gave an error:
ORA-01516: nonexistent log file, datafile or tempfile
<dbf_file>
I shutdown the database and tried the steps to drop the tempfile again..same error..
v$tempfile and dba_temp_files shows "0 rows selected"
Questions:
1. How much have I screwed up the TEST database?
2. How to proceed with PROD database situation..TEMP tablespace in it is sitting at 1g (it's max)...
I want to resize it..and change the uniform size to 512K (as suggested by you)
3. Prevention from ora-1652
Thanks a zillion..
October 31, 2002 - 9:37 pm UTC
$ rm <tmpfilename>
the file still exists in the OS.
or use the REUSE option on the create temporary tablespace .... command to reuse the existing file (it is trying to protect you from overwriting an existing file)
dropping a temporary tablespace and recreating it CONTD.
SM, October 31, 2002 - 8:43 pm UTC
SQLWKS> SELECT * FROM V$SORT_SEGMENT
2>
TABLESPACE_NAME SEGMENT_FI SEGMENT_BL EXTENT_SIZ CURRENT_US TOTAL_EXTE TOTAL_BLOC USED_EXTEN USED_BLOCK FREE_EXTEN FREE_BLOCK ADDED_EXTE EXTENT_HIT FREED_EXTE FREE_REQUE MAX_SIZE MAX_BLOCKS MAX_USED_S MAX_USED_B MAX_SORT_S MAX_SORT_B RELATIVE_F
------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TEMP 0 0 8 0 16383 131064 0 0 16383 131064 14747 4111023 0 0 16383 131064 16383 131064 16383 131064 0
1 row selected.
Data file not existent
Ashwani, September 01, 2003 - 5:13 am UTC
Dear Tom,
V r facing a very funny situation..
1) Select * from dba_temp_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
-------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ---------- --- ---------- ---------- ---------- ---------- ----------
/cppf2arch_local/ofin6/app/proddata/temp02.dbf 1 TEMP 1073750016 131073 AVAILABLE 1 NO 0 0 0 1072693248 130944
/cppf2arch_local/ofin7/app/proddata/temp03.dbf 2 TEMP 1073750016 131073 AVAILABLE 2 NO 0 0 0 1072693248 130944
/cppf2arch_local/ofin7/app/proddata/temp01.dbf 3 TEMP 1073741824 131072 AVAILABLE 3 NO 0 0 0 1072693248 130944
3 rows selected.
.. Now i Say
alter database datafile '/cppf2arch_local/ofin7/app/proddata/temp01.dbf'
2> offline drop;
alter database datafile '/cppf2arch_local/ofin7/app/proddata/temp01.dbf'
*
ORA-01516: nonexistent log file, datafile or tempfile '/cppf2arch_local/ofin7/app/proddata/temp01.dbf'
What is wrong in that ???
I am trying to drop a temp file because it is corrupted..
Pls guide..
Regards,
Ashwani.
September 01, 2003 - 8:33 am UTC
well, the correct approach would be to DROP THE TABLESPACE, not the datafile
and to drop a single TEMPfile, you use TEMPfile, not DATAfile. you asked it to drop the datafile 'foo' and there isn't one, there is a tempfile 'foo' -- but don't drop IT, drop the tablespace.
to drop tempfiles
A reader, September 01, 2003 - 11:06 am UTC
ALTER DATABASE TEMPFILE 'xxx.dbf' DROP;
then simply add another one (temp file of course)
can't I rename the temp file?
A reader, February 19, 2006 - 11:20 am UTC
Hi,
Ok, reading this thread I new that we can drop TEMP
tablespaces.
But I just want to know why this is happening:-
I shutdown immediate and then COPIED (not moved)
the temp_13.dbf to some other location, now I have
two copies of temp_13.dbf one in original location and
other on new location, then I startup mount and then:-
SQL>
select name from v$tempfile
where name like '/data1%';
NAME
---------------------------------------
/data1/cdx/tablespaces/temp/temp_13.dbf
SQL> alter database rename file '/data1/cdx/tablespaces/temp/temp_13.dbf' to
2 '/data8/cdx/tablespaces/temp/temp_13.dbf';
alter database rename file '/data1/cdx/tablespaces/temp/temp_13.dbf' to
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile
"/data1/cdx/tablespaces/temp/temp_13.dbf"
Why is it saying so? I have checked and confirmed that
the files are in there correct locations.
Thanks
February 19, 2006 - 5:13 pm UTC
documentation says:
...
To use this clause for a datafile or tempfile, the database must be mounted. The database can also be open, but the datafile or tempfile being renamed must be offline. In addition, a file with the new name must exist on the system.
...............
so, I think that datafile must have been "online"
easiest thing to do here - create a new temporary tablespace (instantaneous), drop old.
If old tablespace is the DEFAULT then
o create new temp tablespace
o make it the default temp tablespace for the database
o then drop old
cant drop default temporary tablespace
A reader, February 19, 2006 - 11:27 am UTC
Also, when I try to drop the tablespace it
gives me error:-
ORA-12906 cannot drop default temporary tablespace.
Can you please tell me how to drop the default
temporary tablespace.
Thanks
February 19, 2006 - 5:13 pm UTC
see above, create new, make it the DEFAULT, then drop old
in mount
A reader, February 20, 2006 - 1:30 am UTC
Thanks.
The docoumentation says that it should be offline if
database is open, but in my case the database is in mount
mode, so there is no question of temp file being online.
So why its giving the error.
February 20, 2006 - 8:06 am UTC
I set up this script:
...
connect /
set echo on
column fullname new_val f1
column file_name new_val f2
select name fullname, substr(name,instr(name,'/',-1)+1) file_name from v$tempfile;
connect / as sysdba
set echo on
shutdown immediate
!cp &f1 /tmp
startup mount
alter database rename file '&f1' to '/tmp/&f2';
alter database open;
.......
ops$ora10gr2@ORA10GR2> column fullname new_val f1
ops$ora10gr2@ORA10GR2> column file_name new_val f2
ops$ora10gr2@ORA10GR2> select name fullname, substr(name,instr(name,'/',-1)+1) file_name from v$tempfile;
FULLNAME
-------------------------------------------------------------------------------
FILE_NAME
------------------------------
/home/ora10gr2/oradata/ora10gr2/temp.dbf
temp.dbf
ops$ora10gr2@ORA10GR2>
ops$ora10gr2@ORA10GR2> connect / as sysdba
Connected.
sys@ORA10GR2> set echo on
sys@ORA10GR2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10GR2> !cp &f1 /tmp
sys@ORA10GR2> startup mount
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1220820 bytes
Variable Size 104861484 bytes
Database Buffers 494927872 bytes
Redo Buffers 7163904 bytes
Database mounted.
sys@ORA10GR2> alter database rename file '&f1' to '/tmp/&f2';
old 1: alter database rename file '&f1' to '/tmp/&f2'
new 1: alter database rename file '/home/ora10gr2/oradata/ora10gr2/temp.dbf' to '/tmp/temp.dbf'
Database altered.
sys@ORA10GR2> alter database open;
Database altered.
but it did not work on 9ir2 - so there may be a problem renaming temporary files in that release - which is OK, for it is far easier just to
a) create new_one
b) alter database default temporary tablespace new_one;
c) drop old_one
online, without a shutdown.
not open database - "replacing" default temp tbs
Steve Hankin, February 20, 2006 - 8:02 am UTC
You are correct, but also I think you're citing the right answer to a different problem.
You want to (1) drop the current "default" temporary tablespace, (2) create a new temporary tbs and (3) assign it as the new **default** temp tbs.
However, Oracle wants you to do (3) before you do (1). This is because although the temporary tablespace files are offline, Oracle is upset that you're about to throw away it's default temp tbs. It's a bit like monkeys swinging between trees and not wanting to let go of a branch before it's grabbed the next one.
So what is needed here is a "ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <my_new_dflt_tmp_tbs> ;"
So do what Tom said in his Followup and I think you'll be fine.
drop temp tablespace syntax
Elaine H, June 08, 2006 - 9:48 am UTC
I need to create my temp tablespace. there are some peculiar things going on (dates on some of the files are from last year while others are from today) and it is really too large anyway.
I get from reading all of this posting that instead of trying to drop the offending files, it is best to drop and recreate the temp tablespace (including the steps to create a new one and reset the default).
my question is this: i am running 9iR2; can i drop the tablespace "including datafiles and contents"? or do i drop the tablespace and then have to manually remove the datafiles from the (UNIX) O/S?
also for temp, is it best to set a uniform size or just let oracle handle the extent management completely. i have seen it done both ways, i just wondered if there were any specific advantages to one way or another.
thanks very much and keep up the awesome work!
June 08, 2006 - 10:21 am UTC
yes, you can drop the tablespace that way.
the default extent size is 1m which is appropriate for most cases.
recreating temp
Elaine H, June 08, 2006 - 4:55 pm UTC
ok, last questions on this, i promise.
when i issue the create temporary tablespace command, can i specify multiple tempfiles? the previous dba added files to temp and used size 32G autoextend off. is there any performance benefit to doing it this way or is it better to let oracle manage all of the space considerations?
lastly, it used to be a rule of thumb that the initial extent of temp (in DMT) should be the size of sort_area_size + overhead. the sort_area_size in this database is 500M. as i understand it, the temp space gets used if the sort exceeds the sort area in memory. if this is the case, shouldn't the uniform extent size for this temp be something like 500M?
June 08, 2006 - 8:09 pm UTC
using a true temporary tablespace is going to use uniform sizes, period.
A single file or multiple files is up to you - if the underlying file system is striped, I don't see the need for lots of files necessarily. Especially since you'll never backup this file or touch it really.
Why no use of pga_aggregate_target?
remember temp is used for other things (global temporary tables and such) - if you use those, ....