5 Replies Latest reply on Oct 14, 2016 9:00 PM by Kj - Kiran Jadhav

    Temp Tablespace

    Ibrahim Malek

      Dear friends

       

      here's my ebs r12 db its 10G r2 enterprise

      I've add more data files under the temp and the temp is getting 100% it became 32GP on some of the DBF files, is that normal?

      I'm a joiner DBA

       

      please advice

       

      regards,

      ibrahim

       

        • 1. Re: Temp Tablespace
          Bashar.

          Hi,

           

          The temporary tablespace size is too big. There is something wrong in your database, probably poorly written custom code.

          When you enable AutoExtend, you should specify a reasonable limit for the file size.

          In most systems I have worked with, a total size of 8 GB for the temp tablespace is enough.

           

          What is your exact apps release?

          How many concurrent users do you have?

          What is your database size?

           

          Regards,

          Bashar

          • 2. Re: Temp Tablespace
            Ibrahim Malek

            Dear Bashar

            the DB is

            Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

            DB size is 380 GB

             

            the apps is

            ----------------------------------------

            Forms Server

            ----------------------------------------

            Oracle Forms Version : 10.1.2.2.0

            Application Object Library : 12.0.0

            Machine : SRVHQON17

            Forms User CPU (secs) : 0.750000

            Forms System CPU (secs) : 0.150000

            Forms Process ID : 13600

             

            ----------------------------------------

            • 3. Re: Temp Tablespace
              Srk600

              Hello Ibrahim,

               

              As Bashar said, their is no sense increasing the temp spaces without proper guidelines.

              Of course I come across identical scenarios.

               

              Suggest your developers to use small chunks of data to be run at certain point, Instead of doing whole big data in one single shot.

               

              Thanks,

              Srikanth

              • 4. Re: Temp Tablespace
                Ibrahim Malek

                My friends i'm looking to remove those DBF files

                 

                please advice they are n't empty!!!

                 

                i can take the system down during the weekend

                 

                regards

                ibrahim

                • 5. Re: Temp Tablespace
                  Kj - Kiran Jadhav

                  No need of downtime. You can recreate TEMP tablespace anytime. just make sure there are no sessions using current TEMP tablespace.

                   

                  High Level Steps would be:

                   

                  Step 1 Create new  Temporary Tablespace TEMP

                  Step 2 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP

                  Step 3 Make sure No sessions are using your Old Temp tablespace

                         Find Session Number from V$SORT_USAGE:

                         SELECT b.tablespace,b.segfile#,a.sid,a.serial#,

                            a.username,a.osuser, a.status

                            FROM v$session a,v$sort_usage b

                            WHERE a.saddr = b.session_addr;

                   

                  Step 4 Drop old temp tablespace

                   

                   

                  Regards,

                  Kiran

                   

                   

                  SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
                  a.username,a.osuser, a.status
                  FROM v$session a,v$sort_usage b
                  WHERE a.saddr = b.session_addr;