1 2 Previous Next 16 Replies Latest reply: Sep 16, 2011 2:04 PM by user13653962 RSS

    Temporary Tablespace

    user13653962
      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-Oracle
          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
            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
              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
                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
                  Hi;

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

                  Regard
                  Helios
                  • 6. Re: Temporary Tablespace
                    user12000301
                    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
                      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
                        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-Oracle
                          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
                            As Uwe said, it is ok then, no?
                            It means your system is well optimized so it is ok :)
                            • 11. Re: Temporary Tablespace
                              user13653962
                              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
                                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
                                  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
                                    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