4 Replies Latest reply: Feb 28, 2014 3:09 PM by DMU2011 RSS

    Is it normal, TEMP TABLESPACE grow beyond 200gb ?

    DMU2011

      Hello Experts,

       

      I have database for Data warehouse where Developer build some report with the help of Micro-strategy tool. Where this process create Temporary Tables  ( these are not Oracle Temporary tables) and perform some aggregation,  once report generated the tables had been dropped. The query is hitting to some huge table ( i.e. 60 GB , 25 GB in size ).

      When this report generation process chewing lots of space on TEMP TBS. I end up with aporox 200GB and my report created successfully.

       

      Do you think it is normal ?

      Would you share some thoughts on this?

       

      Thanks,

        • 1. Re: Is it normal, TEMP TABLESPACE grow beyond 200gb ?
          Paul M.

          Do you think it is normal ?

          Yes, it may be perfectly normal.

          Would you share some thoughts on this?

          If you worry about those 200G, then you can get back that space :

           

          - create a new much smaller temporary tablespace

          - assign it to users as default temporary tablespace (this can be done in one shot)

          - drop the 200G TS (you may have to wait a bit for the drop to succeed, if it's in use).

           

           

          P.S. probably your new TS will grow again....

          • 2. Re: Is it normal, TEMP TABLESPACE grow beyond 200gb ?
            Emad Al-Mousa

            it could be normal as it depends of the amount of sorting and aggregation as you have mentioned. you can view and monitor the sessions while the process is running for your reference.

             

            there is no need to re size your tablespace as you will be running this process frequently. one thing you need to analyze is your storage capacity of the database.

            • 3. Re: Is it normal, TEMP TABLESPACE grow beyond 200gb ?
              Baris Yildirim

              Hi,

               

              Global temporary tables are created on Temporary tablespaces, and also temporary tablespaces are used by Sorting,Aggregation and Hash joins.

               

              you should keep using Global temporary tables. it also useful in terms of reducing redo generation.

               

              Regards

              • 4. Re: Is it normal, TEMP TABLESPACE grow beyond 200gb ?
                DMU2011

                Hello Everyone,

                Thanks you for your response.

                I think you didn't get the whole picture. One Answer I got that it is normal. Thank you for that.

                I am aware about how to resize the Tablespace. But the question here is, does  250gb size of database required the 200 GB of TEMP Tablespace. Once my report completed these space is automatically release from temp TS.

                 

                Now let me flash on real glitch.

                The query we are running here is generated by MicroStrategy query builder tool and it has numerous joins on temporarily created tables ( Heap tables )  which are very huge in size.

                Can I rely on MicroStrategy Query Tool or I have to re-build all those query manually?

                 

                Is there any thing which can improve in this process that do not generate huge temp/Sort data and increase performance?