Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jay.

Asked: February 04, 2005 - 12:07 pm UTC

Last updated: November 01, 2013 - 6:22 pm UTC

Version: 9.2.0.4

Viewed 10K+ times! This question is

You Asked

I understand that temp files of temporary tablespaces when created may not acquire the disk space equal to the size specified (they acquire more disk space as needed upto the size specified).

Is the above understanding correct ?

If so is there a way to tell (by querying the database)

1. What is the size of the temp file requested in the DDL.

2. How much disk space has been currently acquired ?


and Tom said...

That is called a "sparse file". It is a unix feature.

1) select * from dba_temp_files;

2) use your OS tools for that.

Rating

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Disk space of temp files

A reader, February 07, 2005 - 11:17 am UTC

Thanks for the reply.
1. Does Oracle explicitly request from UNIX that temp files be created as sparse files ?

2. Datafiles are never created as sparse files in UNIX. Is that correct ?

3. During an operation, if you run out of space on temp files before you hit the size specified in the temp file DDL (i.e. the sparse file cannot grow to the size requested), then can the operation be resumed (after adding more space to the temp file mount point) if created as a resumable statement ?


Tom Kyte
February 07, 2005 - 11:35 am UTC

1) when supported, yes
2) we write to them, initialize them, so they are "filled out"

3) interesting question, had to try it out....  answer = no, you get an IO error and it fails:

ops$tkyte@ORA9IR2> alter session set workarea_size_policy=manual;
 
Session altered.
 
ops$tkyte@ORA9IR2> alter session set sort_area_size =64536;
 
Session altered.
 
ops$tkyte@ORA9IR2> alter session enable resumable;
 
Session altered.
 
ops$tkyte@ORA9IR2> select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11;
select * from all_objects order by 1,2,3,4,5,6,7,8,9,10,11
              *
ERROR at line 1:
ORA-01114: IO error writing block to file %s (block # %s)


 

Disk space of temp tablespace

A reader, February 07, 2005 - 11:45 am UTC

Thanks for the quick response.

Is there a way to force temp files to be created as non-sparse files in UNIX ?

Tom Kyte
February 07, 2005 - 1:15 pm UTC

Here, I'll let you tech edit a new section of "expert one on one Oracle" 2cnd edition :)

Temp Files

Temporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store the intermediate results of a large sort operation, hash operations, global temporary table data, or result set, when there is insufficient memory to hold it all in RAM. Permanent data objects, such as a table or an index, will never be stored in a temporary file, but the contents of a temporary table or index would be. So, you’ll never create your application tables in a temporary data file, but you might store data there when you use a temporary table.

Temporary files are treated in a special way by Oracle. Normally, each and every change you make to an object will be recorded in the redo logs – these transaction logs can be replayed at a later date in order to ‘redo a transaction’. We might do this during recovery from failure for example. Temporary files are excluded from this process. Temporary files never have redo generated for them, although they do have UNDO generated (and thus there will be REDO generated working with temporary tables since UNDO is always protected by REDO as we will see in detail in the chapter on Redo and Undo), when used for global temporary tables, in the event you decide to rollback some work you have done in your session. Your DBA never needs to back up a temporary data file, and in fact if they do they are only wasting their time, as you can never restore a temporary data file.

It is recommended that your database be configured with locally managed temporary tablespaces. You’ll want to make sure your DBA uses a CREATE TEMPORARY TABLESPACE command. You do not want them to just alter a permanent tablespace to a temporary one, as you do not get the benefits of temp files that way. 
One of the nuances of true temporary files is that if the operating system permits it – the temporary files will be created “sparse”.  That is, they will not actually consume disk storage until they need to.  You can see that easily using this example (on Red Hat Linux in this case)

ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999488  29008368  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/tmp/temp_huge' size 2048m
  3  /
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999616  29008240  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

NOTE: df is a Unix command to show “disk free”.  This command showed that I have 29,008,368 K free in the file system containing /tmp before I added a 2gig tempfile to the database.  After I added that file, I had 29,008,240  K free in the file system.


Apparently it only took 128 K of storage to hold that file.  But if we “ls” it:

ops$tkyte@ORA10G> !ls -l /tmp/temp_huge
-rw-rw----    1 ora10g   ora10g   2147491840 Jan  2 16:34 /tmp/temp_huge

It appears to be a normal “2 gig” file.  But it is only consuming some 128 K of storage.  The reason I point this out is because I would be able to actually create hundreds of these two gigabyte temporary files – even though I have roughly 29 GIG free.  Sounds great – free storage for all!  The problem is as we started to use these temporary files and they started expanding out – you would rapidly hit errors stating “no more space”.  Since the space is allocated as needed, you stand a definite chance of running out of room (especially if after you create the tempfiles someone else fills up the file system with other stuff).

How to solve this differs from OS to OS, on Linux some of the options are to use ‘dd’ to fill the file or use ‘cp’ to create a non-sparse file.  For example:

ops$tkyte@ORA10G> !cp --sparse=never /tmp/temp_huge /tmp/temp_huge2
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099336  26908520  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> drop tablespace temp_huge;
 
Tablespace dropped.
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/tmp/temp_huge2' reuse;
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099396  26908460  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

After copying the sparse 2 G file to “/tmp/temp_huge2” and creating the temporary tablespace using that tempfile with the REUSE option – we are assured that tempfile has allocated all of its file system space and our database actually has 2 G of temporary space to work with.

Note: In my experience Windows NTFS does not do “sparse” files, this applies to Unix/Linux variants.  On the plus side – if you have to create a 10 G temporary tablespace on Unix/Linux and have tempfile support, you’ll find it goes very fast (instantaneous) – but just make sure you have 10 G free and reserve it in your mind for 
 

tech edit

Alberto Dell'Era, February 07, 2005 - 2:05 pm UTC

Don't know about Linux, but in Solaris, /tmp is actually memory (virtual disk):

</code> http://www.serverworldmagazine.com/monthly/2003/02/solaris.shtml <code>

"The /tmp space is used as the scratch area. This space is created as a special filesystem called tmpfs, which is a RAM disk type file system and uses the available VM resources (from memory and swap disk) to allow processes and users to create temporary files."
"When huge files fill /tmp, the system behaves poorly because applications can’t get swap space."

Shouldn't it be better to avoid creating temporary files in /tmp, to avoid e.g. contending for RAM and swap space with the PGA and SGA, thus creating a vicious loop (eg we decrease pga_aggregate_target to have less swapping, that increases the temp tablespace load, which increases the load on /tmp, and thus load the swap space again ...).
----

Small "improvement" suggestion to the text:
... in the event you decide to rollback some work you have done in your session <add>or simply later query the gtt "as of" a certain point in time</add>


Tom Kyte
February 07, 2005 - 4:16 pm UTC

someone from sun would best be able to answer that -- I don't know...

but -- /tmp does not have to be nor was it by default configured that way on solaris - none of my systems are.


(thanks for the feedback)

A reader, February 07, 2005 - 3:26 pm UTC

<QUOTE>
"When huge files fill /tmp, the system behaves poorly because applications can’t
get swap space."
</QUOTE>

if /tmp gets full, the server crashes and we've noticed this every time.

Thanks.


dave, February 07, 2005 - 4:23 pm UTC

then you os needs fixing, shouldn't crash

Disk space of temp tablespace

Jay, February 07, 2005 - 4:43 pm UTC

Thanks for letting us a peek (and open for suggestions) at the new section "Temp Files" of "expert one on one Oracle" 2nd edition.

1. How about including your findings on temp files (out of space condition before it reaches the DDL size) and resumable statement in this section

2. Unrelated to temp files, but related to that mentioned in the "Temp Files" section: Why does REDO need to be generated for UNDO data of global temporary tables ? REDO is useful only for data that needs to be sustained between instance startups, where as global temporary table data is needed at max for the duration of a session (and sessions are gone once instance is down).

Tom Kyte
February 08, 2005 - 1:04 am UTC

2) because all undo for a transaction goes into the same place, it is all "one in the same", there is no ability in the forseeable future for undo to goto multiple locations

undo is a "transaction" thing, not a "segment" thing.

misunderstanding prevention

Alberto Dell'Era, February 07, 2005 - 4:49 pm UTC

> someone from sun would best be able to answer that

True or false, i think you would be on the safe side by using a directory different from /tmp in the book - a "casual reader" of the 2cnd edition, when seeing your statement

create temporary tablespace temp_huge
2 tempfile '/tmp/temp_huge2' reuse;

may get the false impression that you are suggesting to create tempfiles in /tmp [which i know it's not the case but we are talking about a casual or fast reader - and unfortunately TEMPORARY rhimes with TMP very well]

You may have noticed that i suffer of Compulsory Perfectionism, so please be patient ;)

Tom Kyte
February 08, 2005 - 1:05 am UTC

that is a good suggestion.

thanks

/tmp - do not use /tmpfs

Gabriel Paulovic, February 07, 2005 - 4:52 pm UTC

A bit outdated but still relevant for /tmp discussion: Sun / Oracle Best Practices </code> http://www.sun.com/blueprints/0101/SunOracle.pdf <code>

tmpfs (e.g. /tmp using RAM) can hang the system if you run out of virtual memory, performance impact is also unpredictable, furthermore, when using tmpfs you would have to re-create temp tablespace after each reboot (tmpfs is not persistent across reboots).


Bottom line: don't use tmpfs.

Disk space of temp tablespace

A reader, February 08, 2005 - 10:13 am UTC

Further questions on sparse temp files in UNIX:

1. After a sparse temp file grows in size (based on usage upto the size specified in the tempfile creation DDL), does it ever shrink back
a. between instance startup and shutdown ?
b. between instance startups ?

2. At each instance startup, is the temp file reset back to be a minimum disk space sparse file (i.e. occupying disk space equal to what it did initially at creation time) ?


Tom Kyte
February 09, 2005 - 1:20 am UTC

1) no

2) no

sparse files are simply used to make the create temporary tablespace "do no work" really. after that, nothing special, as the file fills (as we write to it) it fills and stays there.

temp files

Paul, February 26, 2005 - 6:45 am UTC

excellent information on the relationship between Oracle temporary files and UNIX sparse files.......

how to solve this on solaris 9?

jx, March 28, 2005 - 5:12 pm UTC

Hi Tom, thanks a lot for pointing out this issue. We're using this kind of sparse-tempfile now on solaris 9 box and would like to know how to correct it neatly on an existing database:

1. what's the corresponding option on solaris for 'cp --sparse=never' on linux?

2. is it ok that we replace the current tempfile with a non-sparse one?

Thanks a lot in advance.



Tom Kyte
March 28, 2005 - 6:33 pm UTC

1) don't know, anyone out there know?

2) offline operation, probably best to create new. would not want to do this on a "being written to" file.

cp --sparse=never

A reader, March 28, 2005 - 7:22 pm UTC

Well, "cp" on Linux is part of the GNU suite of commands and as such is available for all *nix, including Solaris.

Native Solaris cp probably doesnt have this option, the /usr/xpg4/bin/cp might have it.

info on 'de-sparse' a sparse file

jx, April 01, 2005 - 9:36 am UTC

Thanks for your attention to my question above. Here send you something I got so far:

[ reference from metalink Note:1020110.6 ]

The only effective way to do this is file by file using a known 'safe' command, Eg: tar.
For example to 'de-sparse' a file, 'tar' it onto tape (or a tar disk file) and then fetch it back. You should check the disk free space before and after to ensure the file has expanded. The 'tar' command is safe for this on most machines but you should check the 'df' results to confirm this.
Eg: To desparse our sparse file earlier:
df -St . -> 517850 blocks free
tar cvf /dev/xxx sparse_file
rm sparse_file
tar xvf /dev/xxx sparse_file
df -St . -> 515802 blocks free
Ensure there is no other disk activity on the current disk that would confuse the 'df' readings.

Hope this helps to solve this.

cp will work on solaris w/o any option

jx, April 01, 2005 - 2:37 pm UTC

Just found out this. Thanks Tom for keeping this site available to share our information.

Tom Kyte
April 01, 2005 - 2:56 pm UTC

thanks for the followup

Help for a rookie

Didier Degryse, April 08, 2005 - 4:20 am UTC

Hi guys,

I tried to uderstand as much as I could your answers.
I am a kind of rookie at Oracle and I have a quick question for you:

What is the real use of Temp01 file in Oracle 9i?
As far as I understood it is the equivalent of disk swaping for an OS i.e what can't be loaded in RAM is loaded in TEMP file..? right?
Second question: Temp01 file has nothing to do with REDO and does not store any redo instruction... right? Consequently, is it necessary to backup temp file?

Last question, once the data stored into Temp01 has been written to the database, is the temp file updated so it get reduced in term of size or does it keep growing until you decide to clean it up?
By the way can we clean up a temp file once we check the DB is OK and then recreate a new one ?

Any information on this matter will be highly appreciated, as well if you have some URL I can browse ...

TIA for your prompt answer

Didier




Tom Kyte
April 08, 2005 - 7:25 am UTC

temp01 file? I assume you mean a file named temp01 assigned to a temporary tablespace. These are called TEMPFILES.

It is similar to swap, yes. when sorting or hashing or using a global temporary table for example and it doesn't fit into the memory you are allowed to use, we swap it out.

temp01 as assumed is for temporary data and not redo. it is not necessary or even suggested to backup a TEMPFILE.

the space in a tempfile is managed by the system and as one session finishes using a chunk of it, it becomes available for some other session (or that same session in fact) to use it.

You need not "clean up" a tempfile. If you want to "shrink it", it is easiest to create a new small temporary tablespace, alter the database and/or users to point to it and drop the old (but it'll just grow again, you need that space).

V$sort_segment
v$sort_usage

contain information as to the utilization of temporary space.

Regarding Solaris (another suggestion on fixing sparse file)

Allaine, June 21, 2005 - 1:39 pm UTC

Got the following article on SAP Support site on how to manage the Oracle sparse files (for Unix OS):

If you want to avoid that files do allocate less space on disk than they are defined

o shutdown your database
o rename the file or move it to another disk
o copy the file back to its former name resp. location. The copied file has not the 'sparse' property anymore. A 'du' or 'df' will now show significant changes.
o Open the database
o delete the renamed or to another disk copied version of the file. Do not delete the copied version that belongs to the database now.

Basically, the idea is to "move" the file to another disk when Oracle is shutdown ...

Regards

Tom Kyte
June 21, 2005 - 5:22 pm UTC

that is not true on all platforms. the method to desparse a file is platform specfic.


moving and copying sparse files can and will result in -- well -- another sparse file.


Moving a tempfile

VA, September 07, 2005 - 2:53 pm UTC

I have

create temporary tablespace temp
tempfile '/disk1/temp1.dbf' size ...,
'/disk2/temp2.dbf' size ....;

I would like to move the file on /disk1 to /disk2.

What is the best way to do this without incurring any downtime?

Thanks

Tom Kyte
September 07, 2005 - 3:30 pm UTC

create new temp tablespace

make it the default tablespace (anyone assigned to default will be switched automagically).

alter anyone else that needs be

drop old temp when all active sorts are done.


that is, don't move the files, just create new, alter to use it, drop old

Multiple temp tablespaces

a reader, September 07, 2005 - 7:12 pm UTC

If i were to create multiple temporary tablespaces each on it own disk array and one of the arrays fails, how will oracle handle this?
Will connections not using the bad tablespace be affected?
Will the instance crash?
Will it restart without the mount point for temp tablespace?
Will users specifying the bad temp tablespace as default need to be migrated to another temp tablespace?



Tom Kyte
September 08, 2005 - 8:22 am UTC

only the users using it would be affected, the instance should not crash, others not using it would not know it happened.

You would fix it by

a) drop and create new temp tablespace by same name elsewhere
b) altering users that were assigned to it to a new tablespace.

A reader, September 07, 2005 - 7:41 pm UTC

"make it the default tablespace (anyone assigned to default will be switched automagically)"

Not sure I understand this, can you please elaborate?

create temporary tablespace newtemp ....
alter database set default temporary ....

Now what? Are you saying that users currently assigned to oldtemp will automagically be switched to newtemp?!

Thanks

Tom Kyte
September 08, 2005 - 8:34 am UTC

yes, that is what I'm saying

ops$tkyte@ORA10G> select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;
 
TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
TEMP                                   39
 
ops$tkyte@ORA10G> alter database default temporary tablespace temp2;
 
Database altered.
 
ops$tkyte@ORA10G> select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;
 
TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
TEMP2                                  39
 
ops$tkyte@ORA10G> alter database default temporary tablespace temp;
 
Database altered.
 
ops$tkyte@ORA10G> select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;
 
TEMPORARY_TABLESPACE             COUNT(*)
------------------------------ ----------
TEMP                                   39
 
 

multiple temp tablespaces

a reader, September 08, 2005 - 1:30 pm UTC

non failure tolerant raid-0 disk arrays or single disks are often recommended for temp tablespaces for performance reasons.
if you configure multiple temp tablespaces how would you implement failover with the least dba intervention and pain?
could it be done automatically?
If the default temp tablespace is unavailable will oracle use another temp tablespace automatically?

Tom Kyte
September 08, 2005 - 4:38 pm UTC

if you are utterly worried about it - then protect it by all means. raid 10 it.

Else, there will be the need to issue a pair of commands (create new temp, alter users to use it)

You might be able to automate it - but if you need that kind of availability, mirror temp.

alter database default temporary tablespace temp2

VA, September 09, 2005 - 10:22 am UTC

Are there any circumstances in which the above command will NOT switch all the dba_users.temporary_tablespace to the newly specified tablespace?

I carried out your testcase above on a idle database (9iR2) and it behaved exactly like you showed.

But when I did the ALTER DATABASE command on a active database with sessions currently using the old temp tablespace, that GROUP BY SQL still showed all the users still assigned to the old tablespace?

Is this normal or is this a bug?

Thanks

Tom Kyte
September 09, 2005 - 11:13 am UTC

it will not MOVE their already sorted data, it will complete over there.

No bug, it is like changing the default on anything - the default change doesn't in general affect things "already there"

Like changing the default LOGGING/NOLOGGING mode on a tablespace only affects NEWLY created objects, not existing ones.

Or changing the users default tablespace affects new objects, not existing ones

alter database default temporary tablespace temp2

VA, September 09, 2005 - 11:20 am UTC

You misundertand...

You showed that...
select temporary_tablespace, count(*) from dba_users group by
temporary_tablespace;
showed all N users assigned to OLD_TEMP

You did the ALTER DATABASE .... and the query above now showed all users switched to NEW_TEMP

I am saying that I did see this behaviour on a completely idle database.

But when I did the same thing on a active database, it did NOT change the output of the query above AT ALL i.e. it did NOT switch the temporary_tablespace in dba_users.

Why is this? Why is the behaviour different?

Thanks


Tom Kyte
September 09, 2005 - 11:33 am UTC

I cannot reproduce, it should not work like that.

Perhaps - the tablespace these users were assigned to was NOT the default temporary tablespace??? It only changes those whose temporary tablespace was the default one in the first place.

alter database default temporary tablespace temp2

VA, September 09, 2005 - 11:42 am UTC

I had ONE temporary tablespace TEMP. All users are assigned to this tablespace when they are created using the appropriate clause of the CREATE USER statement.

"was NOT the default temporary tablespace?"

Well, I had never issued this command (ALTER DATABASE DEFAULT TEMPORARY TABLESPACE) before, ever. What does that mean? Does that mean I did not *have* a "default temporary tablespace" and thats why it didnt switch anything?

But that still doesnt explain why it worked on the idle clone database which also never had a "default temporary tablespace".

Thanks

Tom Kyte
September 09, 2005 - 11:52 am UTC

that means you didn't have one before, so no one was in the default temporary tablespace and this only moves users that were assigned to the default temporary tablespace.

Is it possible that in playing on the clone, you set the default to the current tablespace and then did the switch. That would work.

which tablespace is the default temp

a reader, September 09, 2005 - 1:03 pm UTC

How would you query the database to tell what the (database)default temp tablespace is set to?



Tom Kyte
September 09, 2005 - 1:10 pm UTC

alter database default temporary tablespace ......

create database .... default temporary tablespace ...

A reader, September 09, 2005 - 1:19 pm UTC

I think what he means is

SELECT * FROM database_properties
WHERE property_name='DEFAULT_TEMP_TABLESPACE'

Tom Kyte
September 09, 2005 - 1:37 pm UTC

indeed, I misread, thanks!

which tablespace is the default temp

a reader, September 09, 2005 - 1:22 pm UTC

i mean how do i
query the database for the default temp ts without setting it?

Tom Kyte
September 09, 2005 - 1:38 pm UTC

see above

How do I find out which session is consuming most TEMP in 8i

Shalini, February 10, 2006 - 4:54 am UTC

Tom,

We have some nightly application jobs - which are failing consistently over lack of TEMP tablespace.

First guess - some query - pretty bad/in-efficient and consuming almost all of the 32G of temp tablespace.

However in 8i we dont have anything like v$tempseg_usage like we have in 9i. And the v$sort_usage will give only usage for "sort" operations and not for "other" operations - which normally use the TEMP - like hash join,global temp. tables, intermediate result storage.

I'm planning to write a job which runs every hour in night and just checks on which session is consuming TEMP, but I fail to get a query for which session is consuming TEMP.

Anything other than v$sort_usage/v$temp_space_heade ?

With Regards,
Shalini.

Tom Kyte
February 10, 2006 - 12:56 pm UTC

where did  you read that about v$sort_usage?  It shows "usage of temp"



ops$tkyte@ORA817DEV> /*
DOC>drop table t;
DOC>
DOC>create table t as select * from all_objects;
DOC>
DOC>variable x refcursor;
DOC>*/
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> alter session set hash_area_size = 65537;
Session altered.
 
ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV> select /*+ all_rows use_hash(t1 t2) */ t1.* from t t1, t t2 where t1.object_id = t2.object_id;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=3310 Card=8313996 Bytes=1172273436)
   1    0   HASH JOIN (Cost=3310 Card=8313996 Bytes=1172273436)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=28834 Bytes=374842)
   3    1     TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=28834 Bytes=3690752)
 
 
 
ops$tkyte@ORA817DEV> set autotrace off
ops$tkyte@ORA817DEV> select * from v$sort_usage;
 
no rows selected
 
ops$tkyte@ORA817DEV> variable x refcursor
ops$tkyte@ORA817DEV> declare
  2          l_rec t%rowtype;
  3  begin
  4  open :x for
  5  select /*+ all_rows use_hash(t1 t2) */ t1.* from t t1, t t2 where t1.object_id = t2.object_id;
  6  fetch :x into l_rec;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> @printtbl8 'select * from v$sort_usage';
USER                          : "OPS$TKYTE"
SESSION_ADDR                  : "8013A470"
SESSION_NUM                   : "24518"
SQLADDR                       : "81B71FB0"
SQLHASH                       : "2907312366"
TABLESPACE                    : "TEMPORARY"
CONTENTS                      : "TEMPORARY"<b>
SEGTYPE                       : "HASH"</b>
SEGFILE#                      : "202"
SEGBLK#                       : "47945"
EXTENTS                       : "7"
BLOCKS                        : "448"
SEGRFNO#                      : "1"
-----------------
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA817DEV> set verify on



<b>or....</b>


ops$tkyte@ORA817DEV> create global temporary table gtt ( x char(2000) );
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from v$sort_usage;
 
no rows selected
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into gtt select 'x' from all_users;
 
62 rows created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set echo off
USER                          : "OPS$TKYTE"
SESSION_ADDR                  : "8013A470"
SESSION_NUM                   : "24522"
SQLADDR                       : "81B71FB0"
SQLHASH                       : "2907312366"
TABLESPACE                    : "TEMPORARY"
CONTENTS                      : "TEMPORARY"
SEGTYPE                       : "DATA"
SEGFILE#                      : "202"
SEGBLK#                       : "48329"
EXTENTS                       : "1"
BLOCKS                        : "64"
SEGRFNO#                      : "1"
-----------------
 
PL/SQL procedure successfully completed.

 

what is the use of temporary tablespace ?

meme, May 01, 2006 - 3:55 pm UTC


Tom Kyte
May 02, 2006 - 3:33 am UTC

that is where we put temp stuff - from sorts that didn't fit into memory, for hash join information that didn't fit into memory.

It is like our "swap space"

sawp should be 2 times RAM

mal, August 07, 2006 - 3:51 pm UTC

Is it true ?

Tom Kyte
August 07, 2006 - 9:46 pm UTC

yes
no
maybe

it depends - on the OS, how swap is handled. that was definitely the rule of thumb in the early days.

it is red hat AS 3.0

mal, August 07, 2006 - 10:59 pm UTC

10.2.0.2 RAC

Tom Kyte
August 08, 2006 - 7:37 am UTC

it depends - on the OS, how swap is handled.
^^^^^^^^^^


ask your OS vendor of choice.

ORA-01114: IO error writing block to file %s (block # %s)

cPiyush, June 20, 2007 - 8:29 am UTC

Hi Tom,

I am running an application which query a table (say sampleTab) consisting more than 20 million records. We create around 13-15 dump of sampleTab in that single query.
The format is as follows: -

sampleTab t1, sampleTab t2...sampleTab t7...sampleTab t15 where...<some conditions>....

when I execute this query after some time the size of my tempfile TEMP01 reaches to 4GB (which is maximum I think) & I get error : -

ORA-01115: IO error reading block from file %s (block # %s)

& when i restsrt the application it gives me this error: -

ORA-01114: IO error writing block to file %s (block # %s)

Can you please help me out wht to do with this??

Do I have to increase the size of the tempfile...if yes then how to do this???

Thanks.
cPiyush.
Tom Kyte
June 20, 2007 - 11:10 am UTC

a simple query like that would not use temp, but anyway.....

4gb is not the max, unless you set it to be the max, and you would ask your DBA to help you out/take a look at this. setting up temp is a pretty basic thing, they would be able to do it in their sleep.

Tempfiles

cPiyush, June 21, 2007 - 3:04 am UTC

->a simple query like that would not use temp, but anyway.....

->4gb is not the max, unless you set it to be the max, and you would ask your DBA to help you out/take a look at this. setting up temp is a pretty basic thing, they would be able to do it in their sleep.

Can u plz explain the use of temp files??? (as u said that "a simple query like that would not use temp"..I couldn't understand this...becoz what i am seeing here is that my this query is modifying the temp01 file...)

Ok I agree that 4GB is not the max...but what to do with the above mentioned error...
Tom Kyte
June 21, 2007 - 10:27 am UTC

"u" is not available, when they are, I'll let them know you have this question and something about German postal codes (PLZ is the abbreviation for that)

see your DBA.

Well I regret for using those words.

cPiyush, June 27, 2007 - 2:33 am UTC


SIze of Temporary tablespace

PC, July 30, 2007 - 5:20 am UTC

Hello Tom,

I am facing some problem while I am trying to fire a query on a table containing 20 million records.

First I have fired a complex query which increased the size of my temporary tablespace to 4GB. & an error encountered that was "Ora-01114" & "OSD-04026".

Then I changed some indexes over the table's few fields & tried the same query again, but now I am getting "ORA-00603: ORACLE server session terminated by fatal error".

All other small queries are running fine But If I am trying any complex query then its giving me the latter type of error.

DO I need to re-create the temporary tablespace??

Kindly guide me to remove this problem.

Regards,
PC.
Tom Kyte
July 30, 2007 - 5:30 pm UTC

please utilize support if you are getting a fatal error.

Temporary tablespace on RAC with ASM

Max, May 26, 2009 - 10:17 pm UTC

Tom,
Let say i have 10g RAC with ASM installed on Windows nodes) and ASM manages files (data and temp) on partitions presented from SAN, so there is no underlining file system.
Are created tempfiles either sparse or regular ones?
How to measure real space taken if sparse files got created (asmcmd, V$ASM views)?
Tom Kyte
May 27, 2009 - 8:07 am UTC

The temp files would be "real ones" - the space is allocated to temp right then and there.

"sparse files" are entirely an OS trick with their cooked file system - a sort of delayed allocation mechanism.

With ASM, the extents are allocated to the segment right then, right there.

empty creation time of tempfile

sagar, October 14, 2013 - 7:09 am UTC

I wanted to see when temp file was created. I queried creation_time from v$tempfile view. But I am getting empty value for creation time. How we can find out creation time of a tempfile.
Tom Kyte
November 01, 2013 - 6:22 pm UTC

ops$tkyte%ORA11GR2> create temporary tablespace foo tempfile '/tmp/foo.dbf' size 5m;

Tablespace created.

ops$tkyte%ORA11GR2> select file#, creation_time from v$tempfile;

     FILE# CREATION_
---------- ---------
         1 29-JUN-13
         2 01-NOV-13



i cannot reproduce (i ran this on 1-nov)

please utilize support for this one.