Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Surya.

Asked: October 19, 2000 - 4:56 pm UTC

Last updated: December 15, 2005 - 10:31 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Our application is using temporary table for processing intermediate results. I created temporary table for session.
I created index on the temporary table.

My questions are:

1) Why this temporary table is created on SYSTEM table spaces with my ID as owner?

2) Is the table not using temporary table space for storing data?

3) The index on temporary table is created in the default tablespace of my Id. Why it is not created in temporary tablespace.

4) I read in Oracle Manuals, that DML operation on temporary table will not create any redolog but the undo information (RBS) generates redolog which is required for Instance recovery.
I used logminer to analyze the archived log file.
I found the information of insert into temporary table and the username doing the insert operation as my Id.
Why DML operations are generating redolog?


I would appreciate if you can throw some light on the temporary table usage.

Thanks in advance.

Best Regards,
Surya



and Tom said...

1) the temporary tablespace will allocate extents dynamically at runtime from the USERS temporary tablespace -- there is nothing in system (unless your temporary tablespace is in fact SYSTEM)

Why do you think it is in system?

2) it is. for example:

tkyte@TKYTE816> create global temporary table foo
2 ( x int )
3 /

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816> select table_name, tablespace_name
2 from user_tables where table_name = 'FOO';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FOO

tkyte@TKYTE816>
tkyte@TKYTE816> select temporary_tablespace
2 from dba_users
3 where username = user;

TEMPORARY_TABLESPACE
------------------------------
TEMP

tkyte@TKYTE816>
tkyte@TKYTE816> insert into foo values ( 1 );

1 row created.

tkyte@TKYTE816> commit;

Commit complete.

tkyte@TKYTE816>
tkyte@TKYTE816> alter tablespace temp offline;

Tablespace altered.

tkyte@TKYTE816>
tkyte@TKYTE816> insert into foo values ( 1 );
insert into foo values ( 1 )
*
ERROR at line 1:
ORA-01542: tablespace 'TEMP' is offline, cannot allocate space in it

that shows that space is coming from my TEMP -- not system.

3) it is -- why do you think its coming from system?

tkyte@TKYTE816> create index foo_idx on foo(x);
Index created.

tkyte@TKYTE816> select index_name, tablespace_name from user_indexes where
2 index_name = 'FOO_IDX';

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FOO_IDX


temporary stuff have no tablespace -- their extents from from the users temporary tablespace dynamically at runtime.


4) they generate significantly LESS redo then real tables -- not "no redo". For example:

scott@DEV816> create table t1 ( x int );
Table created.

scott@DEV816> create global temporary table t2 ( x int );
Table created.

scott@DEV816> set autotrace on

scott@DEV816> insert into t1 select rownum from all_objects;
23080 rows created.

Statistics
----------------------------------------------------------
...
348116 redo size
...

scott@DEV816> insert into t2 select rownum from all_objects;
23080 rows created.

Statistics
----------------------------------------------------------
...
72508 redo size
...



The temporary table in this case geneate 1/5 the redo of the real table.


Rating

  (20 ratings)

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

Comments

5 sessions of the same schema - global temporary tables

A reader, October 15, 2001 - 12:47 pm UTC

Tom

Iam logged in as scott/tiger from 5 different sessions.

Now I have a global temporary table called gl_temp_tbl in the scott schema.

Each one of those sessions is doing inserts and deletes, updates and truncates on this gl_temp_tbl.

Now my question is , will each of those session have there own extents for the said table, or will this lead to a situation where each situation is deleting and truncating the data being inserted from the other session.

2.How can I determine , as to how many versions of the global temporary table are running at given point of time.
In my case above , there are 5. But how should I know that.
From which view in the data dictionary , I can get this information.

Tom Kyte
October 15, 2001 - 4:40 pm UTC

each session in fact has their own table, in their own space, exclusive of every of session.

They will not contend with eachother at all.



2) you can see temp information in v$sortusage


ops$tkyte@ORA9I.WORLD> @printtbl8 'select * from v$sort_usage'
PL/SQL procedure successfully completed.


ops$tkyte@ORA9I.WORLD> create global temporary table foo ( x int, y char(2000) );
Table created.

ops$tkyte@ORA9I.WORLD> insert into foo select user_id, username from all_users;
61 rows created.

ops$tkyte@ORA9I.WORLD> @printtbl8 'select * from v$sort_usage'
USERNAME                      : OPS$TKYTE
USER                          : OPS$TKYTE
SESSION_ADDR                  : 8834F6C0
SESSION_NUM                   : 1544
SQLADDR                       : 87513890
SQLHASH                       : 2907312366
TABLESPACE                    : TEMP
CONTENTS                      : TEMPORARY
SEGTYPE                       : DATA
SEGFILE#                      : 201
SEGBLK#                       : 585
EXTENTS                       : 1
BLOCKS                        : 64
SEGRFNO#                      : 1
-----------------

PL/SQL procedure successfully completed.


 

Sameer, November 09, 2001 - 12:04 pm UTC

What is the difference between

'create tablespace temp ... temporary'
and
create temporary tablespace temp ...'

The former uses datafiles, the latter uses tempfile. In either case, permanent objects cannot be stored.

I read all the relevant docs, but in terms of practical usage, whats the diff? How to choose between the two ?

Tom Kyte
November 09, 2001 - 1:27 pm UTC

The true temporary tablespace (create temporary tablespace) can use locally managed tablespaces (very nice, fast, ensures uniform extents), the other cannot.

The true temporary tablespace uses files that CANNOT be backed up (well you can but you are totally wasting your time). The dictionary managed ones don't have to be backed up but your recovery procedures are complicated by the fact they are datafiles (you have to go through extra steps to do a full recovery with missing datafiles -- no so with tempfiles).

The true temporary tablespae uses "sparse" files. Create a 2 gig temp tablespace with tempfiles, then create a 2gig temp with datafiles - time them. Big difference.

Do you mean 'create tablespace temp......temporary' is outdated ?

Sameer, November 09, 2001 - 2:53 pm UTC

You mentioned all the advantages of true temporary tablespace ('create temporary tablespace temp'). So do you mean to say that's the only sytax that makes sense now and the other temp ts ('create tablespace temp ... temporary') is outdated syntax and shouldn't really be used anymore ?

Tom Kyte
November 09, 2001 - 4:25 pm UTC

Yes, i use true temporary tablespaces exclusively now myself. I would not recommend using tablespaces that could be permanent via an alter -- only true temporary tablespaces with tempfiles.

Could you tell me why is this ?

Gururaj Kulkarni, November 12, 2001 - 12:50 pm UTC

SQL> create global temporary table tool (x int);

Table created.

SQL> select table_name,tablespace_name from user_tables
where table_name='TOOL';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ -----------------------------
TOOL

SQL> select temporary_tablespace from dba_users where user='SCOTT';

TEMPORARY_TABLESPACE
------------------------------
SYSTEM
TEMP
SYSTEM
SYSTEM
SYSTEM
SYSTEM
TEMP
SYSTEM
SYSTEM
SYSTEM
SYSTEM
TEMP
SYSTEM
SYSTEM

14 rows selected.


Why do I have both as System and Temp temporary table space ? and Why 14 rows have been selected ? 

Tom Kyte
November 12, 2001 - 1:30 pm UTC

Well, the reason for the NULL tablespace name is because a temporary tablespace simply doesn't HAVE a tablespace! When you use the temp table, it will allocate space in the tablespace mandated by the TEMPORARY_TABLESPACE for the current schema. So, if you put data into this temp table, it will use either TEMP or SYSTEM in your system depending on who you are (best to ALTER those users and have their TEMP tablespace set to something other then SYSTEM!!!)


As for the query -- the reason it returns 14 rows -- you must have 14 users in your database. You obviously ran this query when logged in as SCOTT. In this case, the query was the same as:

select temporary_tablespace from dba_users where 1 = 1;

You see -- you used the USER pseudo column -- it returns the name of the CURRENTLY logged in user. If you logged in as any other user, that would return 0 rows.

The query returns both system and temp as temporary tablespaces because some of your users have TEMP and others as SYSTEM as their temporary tablespace.


Helena Markova, November 13, 2001 - 4:28 am UTC

Thank you for explaining the usage of the temporary tables.

temp tablespace datafiles

cosmin ioan, August 03, 2003 - 8:49 am UTC

hi Tom,
on 9.0.2.3, I created multiple datafiles for the temp tablespace so that I can spread the I/O but to my surprise, when I create large temporary tables, Ora starts filling the first datafile, then next, etc, basically, not using the round-robin fashion as all other tablespaces do. any explanation for that?

thank you v. much,
Cosmin

Tom Kyte
August 03, 2003 - 9:23 am UTC

test case?  here is mine:

ps$tkyte@ORA920> create temporary tablespace temp2
  2  tempfile size 100m, size 100m, size 100m, size 100m, size 100m
  3  /
Tablespace created.

<b>created a 500m temp tablespace...</b>

ops$tkyte@ORA920> alter user ops$tkyte temporary tablespace temp2;
User altered.

<b>set myself to use it...
</b>

ops$tkyte@ORA920> create global temporary table t
  2  on commit delete rows
  3  as
  4  select *
  5    from big_table.big_table
  6   where 1=0;
Table created.

<b>when I put stuff into T now, it'll go into TEMP2...</b>

ops$tkyte@ORA920> select file_id, extents_cached, extents_used
  2    from v$temp_extent_pool
  3   where tablespace_name = 'TEMP2';

no rows selected

<b>nothing there yet...</b>

ops$tkyte@ORA920> insert into t
  2  select *
  3    from big_table.big_table
  4   where rownum <= 100000;

100000 rows created.

ops$tkyte@ORA920> select file_id, extents_cached, extents_used
  2    from v$temp_extent_pool
  3   where tablespace_name = 'TEMP2';

   FILE_ID EXTENTS_CACHED EXTENTS_USED
---------- -------------- ------------
         2              2            2
         3              2            2
         4              2            2
         5              2            2
         6              2            2

<b>Now, spread out over the 5 files -- just to show that it isn't the case that file id 2 could only HOLD 2 extents -- that it went round and round -- we'll add some more data:</b>

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t
  2  select *
  3    from big_table.big_table
  4   where rownum <= 100000;

100000 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select file_id, extents_cached, extents_used
  2    from v$temp_extent_pool
  3   where tablespace_name = 'TEMP2';

   FILE_ID EXTENTS_CACHED EXTENTS_USED
---------- -------------- ------------
         2              4            4
         3              4            4
         4              4            4
         5              4            4
         6              4            4

<b>two more in each -- so, file 2 didn't "fill up" and then move on... it went round and round</b>

ops$tkyte@ORA920> commit;
Commit complete.

ops$tkyte@ORA920> drop table t;
Table dropped.

ops$tkyte@ORA920> alter user ops$tkyte temporary tablespace temp;
User altered.

ops$tkyte@ORA920> drop tablespace temp2;
Tablespace dropped.

 

great example; I'm still missing something

cosmin, August 03, 2003 - 1:02 pm UTC

hey Tom, here's my setup:
(initially I had the TEMP tablespace with only one datafile, then I added three more, one for each remainder of my disks but I'm still seeing used space in only one datafile; I even restarted the server several times in the interim):
[sorry for the wide/messy formatting  -- is there any easy way to display column widths not on a case by case basis?)

thx much,
Cosmin


SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users
  2  where username='MS';

USERNAME                       DEFAULT_TABLESPACE                               
------------------------------ ------------------------------                   
TEMPORARY_TABLESPACE                                                            
------------------------------                                                  
MS                             MS                                               
TEMP                                                                            
                                                                                

SQL> select file_id, extents_cached, extents_used
  2     from v$temp_extent_pool
  3     where tablespace_name = 'TEMP';

   FILE_ID EXTENTS_CACHED EXTENTS_USED                                          
---------- -------------- ------------                                          
         4           5949         3884                                          

SQL>    select * from dba_tablespaces
  2     where tablespace_name='TEMP';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_                                                   
--------- --- ------ --------                                                   
TEMP                                 8192        1048576     1048576           1
                       0    1048576 ONLINE    TEMPORARY NOLOGGING NO  LOCAL     
UNIFORM   NO  MANUAL DISABLED                                                   
                                                                                

SQL> SELECT * FROM DBA_TEMP_FILES;

FILE_NAME                                                                       
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS          
---------- ------------------------------ ---------- ---------- ---------       
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS      
------------ --- ---------- ---------- ------------ ---------- -----------      
D:\ORACLE\ORADATA\MS\TEMP01.DBF                                                 
         1 TEMP                           8388608000    1024000 AVAILABLE       
           1 NO           0          0            0 8387559424     1023872      
                                                                                
E:\ORACLE\ORADATA\MS\TEMP01.DBF                                                 
         2 TEMP                           6291456000     768000 AVAILABLE       
           3 YES 6291456000     768000        12800 6290407424      767872      

FILE_NAME                                                                       
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS          
---------- ------------------------------ ---------- ---------- ---------       
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS      
------------ --- ---------- ---------- ------------ ---------- -----------      
                                                                                
F:\ORACLE\ORADATA\MS\TEMP01.DBF                                                 
         3 TEMP                           1.0486E+10    1280000 AVAILABLE       
           2 YES 1.0486E+10    1280000        12800 1.0485E+10     1279872      
                                                                                
C:\ORACLE\ORADATA\MS\TEMP01.DBF                                                 
         4 TEMP                           1.0486E+10    1280000 AVAILABLE       

FILE_NAME                                                                       
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS          
---------- ------------------------------ ---------- ---------- ---------       
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS      
------------ --- ---------- ---------- ------------ ---------- -----------      
           4 YES 3.4360E+10    4194302        12800 1.0485E+10     1279872      
                                                                                
 

Tom Kyte
August 03, 2003 - 2:30 pm UTC

temp was already allocated and didn't need to get anymore space.

This would be like asking "hey, why isn't my table spread across the 4 files. I started with one file, filled the table up and then add three more".

For the same reason -- temp is in a single file, we've already allocated it.

sounds like you want to drop it and recreate it.

What about the indexes ?

Tony, October 01, 2003 - 6:55 am UTC

We have a large temporary table used as an intermediate step in a batch process. The process drops indexes, issues and insert /*+ append */ and then re-creates. The table is partitioned on Organization to allow the batch process to issue a truncate at the beginning (rather than deleting).

I am looking at amending this to be a global temporary table with indexes (so we no longer need the expensive truncate) - marking the indexes unusable initially, inserting and then rebuilding. I was intrigued about your comment that each user effectively had their own copy of a table in their own schema. Is this true for indexes too - how would marking them unusable affect the situation ??

Is this a viable method ?

Tom Kyte
October 01, 2003 - 8:48 am UTC

it would not be necessary to mark the indexes unusable as you would be using a direct path insert (/*+ append */) into the gtt

that will cause the index to be built off to the side and merged into the index after the insert finished.  since the index does not "exist" (it would be empty) there is nothing to merge.

I would propose that you need not set unusable the indexes on the gtt since there is no structure to merge into at the end.

in any case:


ops$tkyte@ORA920> alter index gtt_idx unusable;
alter index gtt_idx unusable
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
 

Thanks

Tony, October 01, 2003 - 9:05 am UTC

Is there any advantage of keeping the /*+ Append */ given that there will be little redo generated due to it being a gtt ?

Tom Kyte
October 01, 2003 - 9:41 am UTC

yes, it makes a difference - try it in sqlplus with set autotrace on statistics and see.

Temp Tablesapce size in 9.2.0

A reader, October 01, 2003 - 4:11 pm UTC

Hello Tom,

I got a really weird probelm using Oracle 9.2.0 on windows 2000.

The Temp tablespace couldn't extend more than 4G and it failed & I had to add another datafile to Temp tablespace. I remember we had this problem with 8.1.7. Do we still have this problem with 9.2 ? Please let me know.

Thanks in advance


Tom Kyte
October 01, 2003 - 6:11 pm UTC

no error messages for me to look at?

A reader, October 02, 2003 - 9:37 am UTC

Hi Tom,

I got this error : ORA-01114 : IO error writting block to file

HTH,
Thanks

Tom Kyte
October 02, 2003 - 10:05 am UTC



bummer?

not sure what else I can say?

if the rest of the error stack is not obvious, you might consider contacting support.

Any Backup/Restore Issues when using /*+ APPEND */ on a GTT ?

pasko, October 02, 2003 - 10:08 am UTC

Hi Tom,

Thanks for a greate Follow-up above..

I am planning to be using a GTT with insert /*+ APPEND */
on it on a production Database.

This will be used for filling some summary Statistics Tables.

Will there be any issues with Backup/Restore procedures, with or without rman.


And also, why can't we specify nologging on a GTT to even take down further the amount of REDO generated ?


thanks in advance.





Tom Kyte
October 02, 2003 - 10:09 am UTC

nope, no issues -- gtt's live in TEMP and you don't even backup true temporary tablespaces.



Errors

atul, November 03, 2003 - 1:54 pm UTC

Hi Tom

while running one batch job we are getting following errors

ORA-01114: IO error writing block to file 76 (block # 1471)
ORA-27063: skgfospo: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device


But when i check in v$datafile we have only 33 datafiles then why error shows file# as 76.
When i check db_file parameter it's 75,so temp_file in temporary tablespace is 76th one??

I check that i found no space left in that,
How to go about it?
any solutions?

Can i drop temp tablespace and recreate it with auto extent on?

Why temp file is feeling too much?

Thanks.
atul


Tom Kyte
November 03, 2003 - 6:12 pm UTC



temp -- yes.

you can drop and recreate, yes.

Dynamic temporary tables

A reader, November 04, 2003 - 9:16 am UTC

Hi Tom,

What do you think if I create the temporary table "dynamically" each time to store the temporary results of the queries? The temporary table does not exist before the query, only create when the query is submitted. And the temporary table does not drop after the query. These temporary tables only be dropped by a job which is schedule to run at night (e.g. 1:00a.m.). And where Oracle stores the temporary table definition (in SYSTEM tablespace)?

Thanks,
David

Tom Kyte
November 04, 2003 - 10:16 am UTC

DDL in an application is a very very very bad idea.

create global temporary table is ddl. it is in system like everything else.

i would encourage you to reconsider your overall design.

Creating tablespace and temporary tablespace

Jamil, May 10, 2004 - 2:47 am UTC

Dear Tom
 I would like to create a tablespace of size of 3GB , I have created the tablespace as follow :

SQL> CREATE TABLESPACE APP DATAFILE
  2  'C:\ORACLE\ORADATA\APP01.DBF' 
  3  SIZE 3072000M;
CREATE TABLESPACE APP DATAFILE
*
ERROR at line 1:
ORA-01144: File size (124780544 blocks) exceeds maximum of 4194303 blocks
And I got the above ERROR HOW CAN I CREATE the TABLESPACE AND TEMPORARY  tablespace OF THE SIZE 3GB and create a user using the above tablespace and temporary tablespace  can you give  me an  example  how to CREATE TABLESPACE  AND TEMPORARY TABLESPACE OF ZISE 3 GB EACH 
Waiting for your valuable answer .

Best Regards
Jamil Shaibani
 

Tom Kyte
May 10, 2004 - 8:01 am UTC

3 gig would be slightly less than the 3 terabytes you attempted.

3*1024 meg = 3gig

3*1024*1024 meg = 3 terabytes.

About Temporary tablespace

YenYang, May 25, 2004 - 4:44 am UTC

apart from sorting what are the events in which temporary tablespaces is used ? does the global temporary tables also use temp tablespace ?

Tom Kyte
May 25, 2004 - 7:32 am UTC

hash joins

global temporary tables

sorts

"view" steps in query plans (when a view needs to be materialized as part of query processing)

are some

Performence impact of temporary tables

Sanjaya Balasuriya, July 13, 2005 - 4:13 am UTC

Hi Tom,

What's the performance impact of temporary tables rather than using regular tables ?

Some of our developers user temporary tables to store results of a bit complex query and then they process the data in the temporary table. They say things will be faster because there are a little data in the temporary table.

Is there any considerable performance gain using this method instead of using the base tables?

Thanks.

Tom Kyte
July 13, 2005 - 11:08 am UTC

if you do not want the data to persist beyond a transaction or session, using a "real" table would not be "smart", as you would be responsible for cleaning up the temporary data.

Temporary table

mAg, August 04, 2005 - 11:58 am UTC

Hi Tom

Related to the follow up, the user is asking about the performance storing the data in the temp file rather than in the datafile, not about the data persistant... As per my understanding?

Here my question is the same?

Sorry!!! If i mistaken the question?

Thanks
mAg

Tom Kyte
August 04, 2005 - 12:33 pm UTC

sorry, I did not understand this?

Temporary table

Mani, August 05, 2005 - 5:17 am UTC

Hi Tom

My question is whether the performance will be good or bad when we use temporary table rather than the regular table?

Thanks
mAg

Tom Kyte
August 05, 2005 - 11:06 am UTC

the answer is

a) things might go slower
b) things might go faster
c) things might not change at all


In general if you can AVOID the need for either (inline views, with subqueries -- temp tables are hardly ever necessary or desirable), do so.

In general, if you can USE a global temporary table at the transaction or session level instead of a real table, do so.

In general, if you need the data to persist across sessions, a permanent table is the only game in town.

can i CREATE INDEX on a global temporary table?

A reader, December 15, 2005 - 2:26 am UTC

because the temporary table is insert and delete high frequency,can i create index on the temporary table?

Tom Kyte
December 15, 2005 - 10:31 am UTC

many tables are high insert/delete frequency and they have indexes...


yes, you may create an index on a temporary table.