7 Replies Latest reply on Jul 19, 2010 7:41 PM by sb92075

    Temporary Tablespace

    728256
      Hi,

      What is the better method in maintaining temporary tablespace.

      1) to create each user a seperate tablespace apart from the default

      2) Use default temporary tablespace for all users?


      Thanks
      Rocky
        • 1. Re: Temporary Tablespace
          sb92075
          1) to create each user a seperate tablespace apart from the default
          NO
          2) Use default temporary tablespace for all users?
          YES
          • 2. Re: Temporary Tablespace
            618702
            Its up to you and your application i guess. If you do lots of sorts its better for you to create seperate temporary tablespaces for different schema. Other than that, you can use a single and one default temp tablespace.

            Ogan
            • 3. Re: Temporary Tablespace
              728256
              Hi,

              Thanks. It would be nice if you could explain why?
              • 4. Re: Temporary Tablespace
                618702
                Hi,

                In our applications, i do lots of merge into and they do create lots of undo and sort operations. If i run 10 different merge intos at the same time, what will happen to the default temporary tablespace? So it's better for you to create different temporary tablespaces for different schema. BUT if you can please share in which type of application or structure will you use that temporary tablespace so we can have an idea and a better answer for you.

                Under normal circumstances you can use the default temporary tablespace for all users.

                Ogan
                • 5. Re: Temporary Tablespace
                  sb92075
                  It would be nice if you could explain why?
                  What is gained by each user having its own TEMP TS?
                  • 6. Re: Temporary Tablespace
                    618702
                    sb92075,

                    If you do lots of merge into operations over 1 billion records of table than you have to have different temporary tablespaces otherwise you will get an error when the datafiles are full. If you have different temp tablespaces the operation will continue without an error and you can split the datafiles. In addition you can control the datafile sizes of the temp datafiles since they are located in different temp tablespaces.

                    Ogan
                    • 7. Re: Temporary Tablespace
                      sb92075
                      I contend that less disk space will be consumed with a single TEMP tablespace than when a TEMP tablespace is created for each user.
                      Each user's TEMP TS has to be sized for worst case for this user.
                      Since rarely will every user require maximum TEMP space concurrently, a single can be size smaller than SUM of all individual users.