This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Sep 16, 2011 12:04 PM by user13653962 RSS

Temporary Tablespace

user13653962 Newbie
Currently Being Moderated
I doubt my temporary tablespace is not being used , whenever i see dbconsole for monitroing tablespace at peak time , i see ony 62 or somtime 70 mb space used for temp tableapce , though i have lot of data insertion as well reporting requests within my database , i have EBS R 12.0.1 , how can i make sure my temporary tablespace is being used or not? If it is not being used then where my sorting and other things which used temp tablepace is being used?
  • 1. Re: Temporary Tablespace
    UweHesse Expert
    Currently Being Moderated
    Look at the column TEMPORARY_TABLESPACE in DBA_USERS.
    If not all entries point to your Temporary Tablespace, another one is used also.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 2. Re: Temporary Tablespace
    user13653962 Newbie
    Currently Being Moderated
    I have checked it , all the users using default tablespace , but why at dbconsole tmep2 is always showing nominal usage of temp2 tablespace (i.e within 100 MB)
    SQL> select username,temporary_tablespace from dba_users
      2  /
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    DMSYS                                                                                      TEMP2
    XDB                                                                                        TEMP2
    ANONYMOUS                                                                                  TEMP2
    MGDSYS                                                                                     TEMP2
    SYSMAN                                                                                     TEMP2
    OLAPSYS                                                                                    TEMP2
    JTS                                                                                        TEMP2
    JTM                                                                                        TEMP2
    AHL                                                                                        TEMP2
    OKB                                                                                        TEMP2
    XNI                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    HR                                                                                         TEMP2
    IMC                                                                                        TEMP2
    APPLSYS                                                                                    TEMP2
    APPLSYSPUB                                                                                 TEMP2
    ALR                                                                                        TEMP2
    AX                                                                                         TEMP2
    AK                                                                                         TEMP2
    XLA                                                                                        TEMP2
    GL                                                                                         TEMP2
    RG                                                                                         TEMP2
    FA                                                                                         TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    FEM                                                                                        TEMP2
    SSP                                                                                        TEMP2
    BEN                                                                                        TEMP2
    HXT                                                                                        TEMP2
    OTA                                                                                        TEMP2
    RLA                                                                                        TEMP2
    VEH                                                                                        TEMP2
    QA                                                                                         TEMP2
    ICX                                                                                        TEMP2
    AZ                                                                                         TEMP2
    BIS                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    PN                                                                                         TEMP2
    HXC                                                                                        TEMP2
    RLM                                                                                        TEMP2
    VEA                                                                                        TEMP2
    POM                                                                                        TEMP2
    FRM                                                                                        TEMP2
    ABM                                                                                        TEMP2
    BSC                                                                                        TEMP2
    EAA                                                                                        TEMP2
    EVM                                                                                        TEMP2
    CS                                                                                         TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    AP                                                                                         TEMP2
    AR                                                                                         TEMP2
    OE                                                                                         TEMP2
    OSM                                                                                        TEMP2
    PA                                                                                         TEMP2
    CN                                                                                         TEMP2
    MFG                                                                                        TEMP2
    INV                                                                                        TEMP2
    PO                                                                                         TEMP2
    BOM                                                                                        TEMP2
    ENG                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    MRP                                                                                        TEMP2
    CRP                                                                                        TEMP2
    WIP                                                                                        TEMP2
    CZ                                                                                         TEMP2
    PJM                                                                                        TEMP2
    FLM                                                                                        TEMP2
    MSC                                                                                        TEMP2
    XTR                                                                                        TEMP2
    RHX                                                                                        TEMP2
    BIX                                                                                        TEMP2
    CE                                                                                         TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    EC                                                                                         TEMP2
    JG                                                                                         TEMP2
    JE                                                                                         TEMP2
    JA                                                                                         TEMP2
    JL                                                                                         TEMP2
    GMA                                                                                        TEMP2
    GMD                                                                                        TEMP2
    GME                                                                                        TEMP2
    GMF                                                                                        TEMP2
    GMI                                                                                        TEMP2
    GML                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    GMP                                                                                        TEMP2
    GR                                                                                         TEMP2
    PMI                                                                                        TEMP2
    CUS                                                                                        TEMP2
    CUI                                                                                        TEMP2
    CUN                                                                                        TEMP2
    CUP                                                                                        TEMP2
    JTF                                                                                        TEMP2
    FPT                                                                                        TEMP2
    IEO                                                                                        TEMP2
    OKC                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    OKS                                                                                        TEMP2
    CSC                                                                                        TEMP2
    BIC                                                                                        TEMP2
    CSD                                                                                        TEMP2
    ASF                                                                                        TEMP2
    CSF                                                                                        TEMP2
    ME                                                                                         TEMP2
    AMS                                                                                        TEMP2
    AMV                                                                                        TEMP2
    BIM                                                                                        TEMP2
    XNM                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    XNP                                                                                        TEMP2
    XDP                                                                                        TEMP2
    BIL                                                                                        TEMP2
    XNC                                                                                        TEMP2
    IES                                                                                        TEMP2
    XNS                                                                                        TEMP2
    CSS                                                                                        TEMP2
    CUA                                                                                        TEMP2
    AST                                                                                        TEMP2
    CCT                                                                                        TEMP2
    IBP                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    IBA                                                                                        TEMP2
    IBY                                                                                        TEMP2
    IBE                                                                                        TEMP2
    IBU                                                                                        TEMP2
    FII                                                                                        TEMP2
    HRI                                                                                        TEMP2
    ISC                                                                                        TEMP2
    OPI                                                                                        TEMP2
    POA                                                                                        TEMP2
    MSO                                                                                        TEMP2
    ONT                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    QP                                                                                         TEMP2
    WSH                                                                                        TEMP2
    MSD                                                                                        TEMP2
    WMS                                                                                        TEMP2
    WPS                                                                                        TEMP2
    CUF                                                                                        TEMP2
    IGS                                                                                        TEMP2
    IPA                                                                                        TEMP2
    ASG                                                                                        TEMP2
    IEX                                                                                        TEMP2
    OKX                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    ASO                                                                                        TEMP2
    CSP                                                                                        TEMP2
    OZF                                                                                        TEMP2
    OZP                                                                                        TEMP2
    OZS                                                                                        TEMP2
    IEU                                                                                        TEMP2
    IEM                                                                                        TEMP2
    OKE                                                                                        TEMP2
    ECX                                                                                        TEMP2
    GMS                                                                                        TEMP2
    IGW                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    PSB                                                                                        TEMP2
    PSP                                                                                        TEMP2
    CSR                                                                                        TEMP2
    IEB                                                                                        TEMP2
    IGF                                                                                        TEMP2
    WSM                                                                                        TEMP2
    MWA                                                                                        TEMP2
    FV                                                                                         TEMP2
    IGC                                                                                        TEMP2
    PSA                                                                                        TEMP2
    APPS                                                                                       TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    OKR                                                                                        TEMP2
    PV                                                                                         TEMP2
    ASL                                                                                        TEMP2
    OKO                                                                                        TEMP2
    IEC                                                                                        TEMP2
    CSE                                                                                        TEMP2
    CUE                                                                                        TEMP2
    EAM                                                                                        TEMP2
    FTE                                                                                        TEMP2
    IGI                                                                                        TEMP2
    ITG                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    MSR                                                                                        TEMP2
    OKI                                                                                        TEMP2
    IZU                                                                                        TEMP2
    IPD                                                                                        TEMP2
    ENI                                                                                        TEMP2
    XXPZ                                                                                       TEMP2
    POS                                                                                        TEMP2
    AHM                                                                                        TEMP2
    ASP                                                                                        TEMP2
    BIV                                                                                        TEMP2
    CSI                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    CSL                                                                                        TEMP2
    CUG                                                                                        TEMP2
    IMT                                                                                        TEMP2
    CLN                                                                                        TEMP2
    XNB                                                                                        TEMP2
    BNE                                                                                        TEMP2
    QRM                                                                                        TEMP2
    PON                                                                                        TEMP2
    OKL                                                                                        TEMP2
    IBC                                                                                        TEMP2
    AMF                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    QOT                                                                                        TEMP2
    CSM                                                                                        TEMP2
    DOM                                                                                        TEMP2
    EGO                                                                                        TEMP2
    DDD                                                                                        TEMP2
    PJI                                                                                        TEMP2
    ZFA                                                                                        TEMP2
    ZSA                                                                                        TEMP2
    EDR                                                                                        TEMP2
    PRP                                                                                        TEMP2
    AMW                                                                                        TEMP2
    
    USERNAME                                                                                   TEMPORARY_TABLESPACE
    ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------
    XLE                                                                                        TEMP2
    ASN                                                                                        TEMP2
    MST                                                                                        TEMP2
    FUN                                                                                        TEMP2
    GCS                                                                                        TEMP2
    XDO                                                                                        TEMP2
    ZX                                                                                         TEMP2
    LNS                                                                                        TEMP2
    IA                                                                                         TEMP2
    FPA                                                                                        TEMP2
    ZPB                                                                                        TEMP2
  • 3. Re: Temporary Tablespace
    19426 Guru
    Currently Being Moderated
    It's not necessarily a fault, when the TEMP tablespace is not or only rarely used. Check which a mount of memory is allocated to PGA and/or SORT_AREA_SIZE. It's also possible that - depending on your unknown database version - automatic memory management allocates enough memory.
  • 4. Re: Temporary Tablespace
    orawiss Oracle ACE
    Currently Being Moderated
    Could you try to execute this statement and compare the temp usage with dbconsole?
    SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
    P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    COUNT(*) statements
    FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
    WHERE T.session_addr = S.saddr
    AND S.paddr = P.addr
    AND T.tablespace = TBS.tablespace_name
    GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
    P.program, TBS.block_size, T.tablespace
    ORDER BY sid_serial;
    My doubt is the DB console is not reporting correct figures ...
    hope it helps!
  • 5. Re: Temporary Tablespace
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    please see:
    Queries to monitor Temporary Tablespace usage [ID 289894.1]

    Regard
    Helios
  • 6. Re: Temporary Tablespace
    user12000301 Newbie
    Currently Being Moderated
    Hi,

    Use following query to find out the default_tablespace of your database :
    select property_value from database_properties where property_name like'%DEFAULT_TEMP_TABLESPACE%';
    Edited by: user12000301 on Sep 16, 2011 4:44 AM
  • 7. Re: Temporary Tablespace
    839439 Pro
    Currently Being Moderated
    user13653962 wrote:
    I doubt my temporary tablespace is not being used , whenever i see dbconsole for monitroing tablespace at peak time , i see ony 62 or somtime 70 mb space used for temp tableapce , though i have lot of data insertion as well reporting requests within my database , i have EBS R 12.0.1 , how can i make sure my temporary tablespace is being used or not? If it is not being used then where my sorting and other things which used temp tablepace is being used?
    Temporary tablespaces will appear full after a while in a normally running database. Extents are not de-allocated after being used. Rather it would be managed internally and reused. This is normal and to be expected and is not an indication that you do not have any temporary space.

    If you are not encountering any issue/error related to TEMP then you don't need to worry about this.

    And the only way to estimate the required TEMP tablespace size is regressive testing.

    --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 8. Re: Temporary Tablespace
    user13653962 Newbie
    Currently Being Moderated
    SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
    P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
    COUNT(*) statements
    FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
    WHERE T.session_addr = S.saddr
    AND S.paddr = P.addr
    AND T.tablespace = TBS.tablespace_name
    GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
    P.program, TBS.block_size, T.tablespace
    ORDER BY sid_serial;
    
    SID_SERIAL    USERNAME      OSUSER   SPID      MODULE          PROGRAM                          MB_USED TABLESPACE         STATEMENTS
    ------------  ---------    --------  ------    --------         ------------------------------   ------- --------------     ----------
    119,988         APPS       appsprod  13709     OEXOEORD        oraclePROD@dbprod.parazelsus.pk        2 TEMP2              2
    122,942         APPS       appsprod  14724     INVTTMTX        oraclePROD@dbprod.parazelsus.pk        2 TEMP2            2
    150,6972         APPS       appsprod  8620      OEXOEORD        oraclePROD@dbprod.parazelsus.pk        3 TEMP2            3
    168,14999         APPS       appsprod  15355     PZWSHASCSRS      oraclePROD@dbprod.parazelsus.pk        2 TEMP2            2
    212,1840         APPS        appsprod  11472     FNDRSRUN         oraclePROD@dbprod.parazelsus.pk        2 TEMP2            2
    214,12090         APPS       appsprod  20106     ARXRWMAI         oraclePROD@dbprod.parazelsus.pk        2 TEMP2            2
    224,9818         APPS       appsprod  24862     OEXOEORD         oraclePROD@dbprod.parazelsus.pk            2 TEMP2              2
    Only 14 Mb is being used at the moment though it is peak time.
  • 9. Re: Temporary Tablespace
    UweHesse Expert
    Currently Being Moderated
    I would say that this is actually a good thing, isn't it?

    Maybe you can find something else to worry about :)

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
  • 10. Re: Temporary Tablespace
    orawiss Oracle ACE
    Currently Being Moderated
    As Uwe said, it is ok then, no?
    It means your system is well optimized so it is ok :)
  • 11. Re: Temporary Tablespace
    user13653962 Newbie
    Currently Being Moderated
    actually i am worried about my one of user tablespace which is growing with pace 100 MB within 10 minutes , i suspcect that may be temporary space is being used by this user tablespace.
  • 12. Re: Temporary Tablespace
    sb92075 Guru
    Currently Being Moderated
    user13653962 wrote:
    actually i am worried about my one of user tablespace which is growing with pace 100 MB within 10 minutes , i suspcect that may be temporary space is being used by this user tablespace.
    I suspect you misinterpret reality.

    post SQL & actual results so we can see what you do & see.
  • 13. Re: Temporary Tablespace
    John Spencer Oracle ACE
    Currently Being Moderated
    user13653962 wrote:
    actually i am worried about my one of user tablespace which is growing with pace 100 MB within 10 minutes , i suspcect that may be temporary space is being used by this user tablespace.
    SQL> select sum(bytes) from dba_free_space
      2  where tablespace_name = 'USERS';
    
    SUM(BYTES)
    ----------
     731447296
    
    SQL> create table t (id number, descr varchar2(10))
      2     tablespace users
      3     storage (initial 10M);
    
    Table created.
    
    SQL> select sum(bytes) from dba_free_space
      2  where tablespace_name = 'USERS';
    
    SUM(BYTES)
    ----------
     720961536
    
    SQL> select 731447296 - 720961536 from dual;
    
    731447296-720961536
    -------------------
               10485760
    My users tablespace just grew 10 MB within 10 seconds. Should I be worried?

    John
  • 14. Re: Temporary Tablespace
    user13653962 Newbie
    Currently Being Moderated
    I appreciate yours comments , but this 100MB space is for very after 10 mins , per hour it becomes 600 MB , it is an estimation and daily by database size is growing with 2 GB , ultimately i am running out of SAN space which is 1.4 TB now only remaining 120 GB space , i am doing some data purging but i want to also focus why the data is grwoing with such huge pace.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points