Home » RDBMS Server » Server Administration » Only one temp file is used (12.1.0.2.0)
Only one temp file is used [message #665138] Wed, 23 August 2017 00:34 Go to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
Hello,

I have four files assigend to the TEMP table space.
Unfortunately only one file seems to be used.
This file does show a usage of over 100%.
The other files are not even touched.

If run SELECT * FROM dba_temp_files I can also only see that one file.
If I look in EM Express, I can see all four files in the TEMP table space.
Re: Only one temp file is used [message #665139 is a reply to message #665138] Wed, 23 August 2017 02:39 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
matthiaswolf1984 wrote on Wed, 23 August 2017 06:34
I have four files assigend to the TEMP table space.
Unfortunately only one file seems to be used.
This file does show a usage of over 100%.
The other files are not even touched.
So what?

matthiaswolf1984 wrote on Wed, 23 August 2017 06:34
If run SELECT * FROM dba_temp_files I can also only see that one file.
If I look in EM Express, I can see all four files in the TEMP table space.
Show us. Copy and paste the output to here.
Re: Only one temp file is used [message #665141 is a reply to message #665139] Wed, 23 August 2017 04:54 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
I cannot believe this either. The "dba_temp_files" view will show ALL your tempfiles. If not, you are on the wrong database.
Re: Only one temp file is used [message #665145 is a reply to message #665138] Wed, 23 August 2017 06:38 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
matthiaswolf1984 wrote on Wed, 23 August 2017 00:34

This file does show a usage of over 100%.
How can a file - any file - have a usage of over 100%?
I'd say you are looking at the wrong thing, and misinterpreting it. But since you only make assertions without any evidence, we are in the dark.
Re: Only one temp file is used [message #665147 is a reply to message #665145] Wed, 23 August 2017 07:27 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
Ok, here is the output I have in EM Express:
http://imgur.com/iic75l2

Here is the output of select * from dba_temp_files:
http://imgur.com/aRjjNHC

[Updated on: Wed, 23 August 2017 07:29]

Report message to a moderator

Re: Only one temp file is used [message #665148 is a reply to message #665147] Wed, 23 August 2017 07:34 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
How do we know that these are from the same database? Also, please post your entire session using CODE tags. Something like this:
C:\> set ORACLE_SID=<your_sid>
C:\> sqlplus / as sysdba
SQL> select name from v$database;
SQL> select * from dba_tenp_files;

You need to show us what you are doing. Not simply two pictures which may or may not be related Smile
Re: Only one temp file is used [message #665149 is a reply to message #665148] Wed, 23 August 2017 07:50 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
C:\Users\mwadmin>sqlplus sys/password@PLM61 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 14:45:58 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
PLM61

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
------------ --- ---------- ---------- ------------ ---------- -----------
W:\PLM61\TEMP01.DBF
         1 TEMP                           5194645504     634112 AVAILABLE
           1 YES 3.4360E+10    4194302       131072 5193596928      633984


SQL>

Here is the EM-Express Screenshot with DB-Name and Username.

http://imgur.com/uah9wqr
Re: Only one temp file is used [message #665150 is a reply to message #665149] Wed, 23 August 2017 07:56 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
Sorry guys,

sometimes it seems that you are blind.
I see now, that I really connected to PLM61 instead of PLM62.
This explains the difference.

But still, does anyone know why it seems that one file is fille over 100% and the others are not touched?
Re: Only one temp file is used [message #665151 is a reply to message #665149] Wed, 23 August 2017 07:57 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
The SQL*Plus session shows database name as PLM61. But the screenshot appears to give the database name as PLM62?
Re: Only one temp file is used [message #665152 is a reply to message #665151] Wed, 23 August 2017 08:06 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
You are right, sorry.
Please see post above.
Re: Only one temp file is used [message #665154 is a reply to message #665150] Wed, 23 August 2017 08:15 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
From Temporary tablespaces:
Quote:

A temporary tablespace contains transient data that persists only for the duration of a session. No permanent schema objects can reside in a temporary tablespace. A temp file stores temporary tablespace data.
Re: Only one temp file is used [message #665164 is a reply to message #665154] Wed, 23 August 2017 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
>But still, does anyone know why it seems that one file is fille over 100% and the others are not touched?

please post actual COPY & PASTE of SQL& results that show above is true.
Those who live by the GUI die by the GUI. Long live sqlplus!
Re: Only one temp file is used [message #665167 is a reply to message #665164] Thu, 24 August 2017 01:13 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
Do you know which SQL statment I have to run to show the usage of the file?
Re: Only one temp file is used [message #665171 is a reply to message #665167] Thu, 24 August 2017 03:29 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
Funnily enough there is an article on this website here.
Re: Only one temp file is used [message #665297 is a reply to message #665171] Tue, 29 August 2017 06:02 Go to previous messageGo to next message
matthiaswolf1984
Messages: 15
Registered: November 2016
Junior Member
Ok, thank you. I will check the usage.
Re: Only one temp file is used [message #665333 is a reply to message #665297] Wed, 30 August 2017 21:23 Go to previous message
trantuananh24hg
Messages: 705
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Correct me if I am wrong to understand your question:

1- You made a query against to dba_temp_files, EM Express, and you realized two different result from both of, didn't you?

If that, there is only case: Your query and your EM was on different databases

2- You wonder why your database tempfile was always going on 100% fully, so, you would like to find what was the job, query, ...etc did a fire the tempfile?

If yes, using the below SQL to find them.

-- +----------------------------------------------------------------------------+                                                                            |
-- | DATABASE : Oracle        													|
-- | Author	  : Tuan Anh Tran - trantanh@vnpt.vn								|
-- | FILE     : tempusage.sql                                        			|
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Finding temporary tablespace and what're session, user schema   |
-- |            who has been being used. This script work with Oracle8i .     	|
-- |            or higher                          								|
-- +----------------------------------------------------------------------------+
select b.Total_MB,
       b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,
       round(used_blocks*8/1024)                Current_Used_MB,
       round(max_used_blocks*8/1024)             Max_used_MB
from v$sort_segment a,
 (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b
/

Prompt -- Press any key to continue
Pause
Prompt -- List sessions are using temporary tablespace --

col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

Prompt -- Press anykey to continue --
Pause

Prompt -- List sql statements --
var schema varchar2(200);
var sid number;
col hash_value for 999999999999
select sql_text, hash_value, sorts, rows_processed/executions
 from v$sql
 where hash_value in (select hash_value from v$open_cursor where sid=&&sid)
 and sorts > 0
 and PARSING_SCHEMA_NAME='&&schema'
 order by rows_processed/executions;
undefine schema
undefine sid

Example:
teleadm@TELESMS> @tempusage

  TOTAL_MB CURRENT_FREE_MB CURRENT_USED_MB MAX_USED_MB
---------- --------------- --------------- -----------
      6505            6501               4        5128

-- Press any key to continue

-- List sessions are using temporary tablespace -

       SID USERNAME        TABLESPACE HASH_VALUE                               SEGTYPE   CONTENTS      BLOCKS
---------- --------------- ---------- ---------------------------------------- --------- --------- ----------
      2623 TELESMPP             TEMP       0/3724126796                             LOB_DATA  TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              DATA      TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              LOB_DATA  TEMPORARY     128
      4068 TELESMPP             TEMP       0/377997205                              INDEX     TEMPORARY     128

-- Press anykey to continue - Pause
-- List sql statements - var schema varchar2(200)
Enter value for sid: 4068
old   3:  where hash_value in (select hash_value from v$open_cursor where sid=&&sid)
new   3:  where hash_value in (select hash_value from v$open_cursor where sid=4068)
Enter value for schema: TELESMPP
old   5:  and PARSING_SCHEMA_NAME='&&schema'
new   5:  and PARSING_SCHEMA_NAME='TELESMPP'

SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
   HASH_VALUE      SORTS ROWS_PROCESSED/EXECUTIONS
------------- ---------- -------------------------
SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205         35                1.02857143

SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205          5                       3.6

SELECT PARTITION_NAME       FROM SYS.Dba_TAB_PARTITIONS       WHERE TABLE_OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME
ORDER BY PARTITION_NAME
    377997205         31                4.74193548

SELECT COLUMN_NAME       FROM SYS.Dba_TAB_COLUMNS       WHERE OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME       ORDER BY COL
UMN_ID
   2828175926          6                11.3333333

SELECT COLUMN_NAME       FROM SYS.Dba_TAB_COLUMNS       WHERE OWNER = :SCHEMA       AND TABLE_NAME = :PARENT_NAME       ORDER BY COL
UMN_ID
   2828175926         35                11.8857143

select c.column_name,  case when data_type = :"SYS_B_00"     then      data_type||:"SYS_B_01"||c.char_length||decode(char_used,:"SYS
_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",null)||:"SYS_B_06"                when data_type = :"SYS_B_07"  then      data_type||:"SY
S_B_08"||c.char_length||decode(char_used,:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",null)||:"SYS_B_13"                when data
_type = :"SYS_B_14" then      data_type||:"SYS_B_15"||c.char_length||decode(char_used,:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19
",null)||:"SYS_B_20"                when data_type = :"SYS_B_21"    then      data_type||:"SYS_B_22"||c.char_length||decode(char_use
d,:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",null)||:"SYS_B_27"                when data_type = :"SYS_B_28" then
     case when c.data_precision is null and c.data_scale is null then          :"SYS_B_29"            when c.data_precision is nu
ll and c.data_scale is not null then          :"SYS_B_30"||c.data_scale||:"S
   3986790923         47                12.3617021

select c.column_name,  case when data_type = :"SYS_B_00"     then      data_type||:"SYS_B_01"||c.char_length||decode(char_used,:"SYS
_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",null)||:"SYS_B_06"                when data_type = :"SYS_B_07"  then      data_type||:"SY
S_B_08"||c.char_length||decode(char_used,:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",null)||:"SYS_B_13"                when data
_type = :"SYS_B_14" then      data_type||:"SYS_B_15"||c.char_length||decode(char_used,:"SYS_B_16",:"SYS_B_17",:"SYS_B_18",:"SYS_B_19
",null)||:"SYS_B_20"                when data_type = :"SYS_B_21"    then      data_type||:"SYS_B_22"||c.char_length||decode(char_use
d,:"SYS_B_23",:"SYS_B_24",:"SYS_B_25",:"SYS_B_26",null)||:"SYS_B_27"                when data_type = :"SYS_B_28" then
     case when c.data_precision is null and c.data_scale is null then          :"SYS_B_29"            when c.data_precision is nu
ll and c.data_scale is not null then          :"SYS_B_30"||c.data_scale||:"S
   3986790923         20                     12.95


7 rows selected.

teleadm@TELESMS>

[Updated on: Wed, 30 August 2017 21:24]

Report message to a moderator

Previous Topic: connection of client pcs with server oracle database
Next Topic: Table Reorg / DBMS_REDEFINITION
Goto Forum:
  


Current Time: Fri Dec 15 21:19:40 CST 2017

Total time taken to generate the page: 0.02594 seconds