11 Replies Latest reply: Feb 18, 2013 10:58 AM by Nicolas.Gasparotto RSS

    Shrinking of Temp Tablespace doesnot shrink tempfile at OS level

    Step_Into_Oracle_DBA
      I want to test the new 11g feature of shrinking tempfile. My tempfile size is 32GB and i have tried shrinking it to 5gb but it doesnt resize the tempfile at OS level. Still file size remains same.

      Please suggest here.
      select tablespace_name,round(bytes/1048576/1024,2) "size GB",file_id,file_name,round(maxbytes/1048576/1024,2) "Max in GB",autoextensible from   dba_temp_files where  tablespace_name like 'TEMP';
      SQL>
      TABLESPACE_NAME                   size GB    FILE_ID FILE_NAME                                                               Max in GB AUT
      ------------------------------ ---------- ---------- ---------------------------------------------------------------------- ---------- ---
      TEMP                                31.99          1 +mydb_DATA/mydb/tempfile/temp.264.798205781                                32 YES
      
      SQL> alter tablespace temp shrink space;
      
      Tablespace altered.
      
      SQL> select tablespace_name,round(bytes/1048576/1024,2) "size GB",file_id,file_name,round(maxbytes/1048576/1024,2) "Max in GB",autoextensible from   dba_temp_files where  tablespace_name like 'TEMP';
      
      TABLESPACE_NAME                   size GB    FILE_ID FILE_NAME                                                               Max in GB AUT
      ------------------------------ ---------- ---------- ---------------------------------------------------------------------- ---------- ---
      TEMP                                31.99          1 +mydb_DATA/mydb/tempfile/temp.264.798205781                                32 YES
      
      SQL> select TABLESPACE_NAME,round((Tablespace_size)/1048576/1024,2) "Total in GB",(allocated_space)/1048476/1024 "Used in GB",round((FREE_Space)/1048576/1024,2) "Free in GB"
      from dba_temp_free_space;  2
      
      TABLESPACE_NAME                Total in GB Used in GB Free in GB
      ------------------------------ ----------- ---------- ----------
      TEMP                                 31.99 .049801808      31.96
      Still File size remains 32GB at OS level.

      Edited by: Step Into Oracle DBA on Feb 11, 2013 6:25 AM
        • 1. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
          Girish Sharma
          Just type this at the command like:

          sync ; sync ; sync

          Don't ask why you need to run it three times. I have never gotten a good explanation to that.

          sync-Linux Command. sync command- Synchronize data on disk with memory. So, I think it will sync

          http://www.experts-exchange.com/Database/Oracle/9.x/Q_22610343.html

          Edited by: Girish Sharma on Feb 11, 2013 8:33 PM

          If the sync does not work, then I would suspect that bouncing Oracle should work.
          • 2. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
            jgarry
            Girish Sharma wrote:
            Just type this at the command like:

            sync ; sync ; sync

            Don't ask why you need to run it three times. I have never gotten a good explanation to that.
            Why, it's a myth, of course. http://utcc.utoronto.ca/~cks/space/blog/unix/TheLegendOfSync
            • 3. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
              TSharma-Oracle
              You have to provide the "KEEP" clause too. With the "KEEP" clause omitted(in your case) , the database will shrink the tablespace to the smallest possible size. So in your case , there could be a chance that somebody is holding something at the end of the datafile.
              Use this command,

              ALTER TABLESPACE temp SHRINK SPACE KEEP 5120M;
              • 4. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                Step_Into_Oracle_DBA
                TSharma wrote:
                You have to provide the "KEEP" clause too. With the "KEEP" clause omitted(in your case) , the database will shrink the tablespace to the smallest possible size. So in your case , there could be a chance that somebody is holding something at the end of the datafile.
                Use this command,

                ALTER TABLESPACE temp SHRINK SPACE KEEP 5120M;
                Tsharma,

                I have tried this option before posting but still no luck :(

                Thanks & Regards,
                SID

                (StepIntoOracleDBA)

                Email : stepintooracledba@gmail.com

                http://stepintooracledba.blogspot.in/

                www.stepintooracledba.com/
                • 5. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                  Niket Kumar
                  you need to shrink datafile size...

                  check how much size you can reduce from temp file from this command....
                  column tablespace_name format a10
                  column file_name format a32
                  column file_mb format 9999990
                  column hwm_mb format 9999990
                  column used_mb format 9999990
                  column shrnk_mb format 9999990
                  
                  break on report
                  compute sum of file_mb on report
                  compute sum of hwm_mb on report
                  compute sum of used_mb on report
                  compute sum of shrnk_mb on report
                  
                  select a.*
                  , file_mb-hwm_mb shrnk_mb
                  from (
                  select /*+ rule */
                  a.tablespace_name,
                  a.file_name,
                  a.bytes/1024/1024 file_mb,
                  b.hwm*d.block_size/1024/1024 hwm_mb,
                  b.used*d.block_size/1024/1024 used_mb
                  from
                  dba_data_files a,
                  (select file_id,max(block_id+blocks-1) hwm,sum(blocks) used
                  from dba_extents
                  group by file_id) b,
                    dba_tablespaces d
                  where a.file_id  = b.file_id
                  and a.tablespace_name = d.tablespace_name
                  ) a
                  order by a.tablespace_name,a.file_name;
                  alter deatabase tempfile 'Temp_data_file' resize 5G; or whatever size you want to reduce.....

                  Edited by: Niket Kumar on Feb 12, 2013 9:52 AM
                  • 6. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                    Step_Into_Oracle_DBA
                    Hi Girish,

                    sync command doesnt seem to help. We are using ASM and the tempfile is stored in Diskgroup.

                    Thanks & Regards,
                    SID

                    (StepIntoOracleDBA)

                    Email : stepintooracledba@gmail.com

                    http://stepintooracledba.blogspot.in/

                    http://www.stepintooracledba.com/

                    Edited by: Step Into Oracle DBA on Feb 11, 2013 9:05 PM
                    • 7. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                      Step_Into_Oracle_DBA
                      Hi Niket,

                      I have tried this already which didnt work. When i tried to resize i got ORA-03297 error.

                      From Your query i changed dba_data_files to dba_temp_files as this is a temporary tablespace.


                      Thanks & Regards,
                      SID

                      (StepIntoOracleDBA)

                      Email : stepintooracledba@gmail.com

                      http://stepintooracledba.blogspot.in/

                      http://www.stepintooracledba.com/
                      • 8. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                        TSharma-Oracle
                        There could somethign holding at the end of the datafiles. Run this query to find if there are any sessions using your temp tablespace.

                        SELECT se.username username,

                        se.SID sid, se.serial# serial#,
                        se.status status, se.sql_hash_value,
                        se.prev_hash_value,se.machine machine,
                        su.TABLESPACE tablespace,su.segtype,
                        su.CONTENTS CONTENTS
                        FROM v$session se,
                        v$sort_usage su
                        WHERE se.saddr=su.session_addr;

                        Check if you can drop those sessions and then try shrink. OR you can create a new tablespace move every user to that temp tablespace and than drop this old one.
                        • 9. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                          Step_Into_Oracle_DBA
                          Hi TSharma,

                          There will be some sessions always using the temporary segments, i'm wondering why new features of shrinking temporary tablespaces is not working :(

                          Thanks & Regards,
                          SID

                          (StepIntoOracleDBA)

                          Email : stepintooracledba@gmail.com

                          http://stepintooracledba.blogspot.in/

                          http://www.stepintooracledba.com/
                          • 10. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                            Step_Into_Oracle_DBA
                            Hi All,

                            I have tried Shrinking after a couple of days and it worked like a charm. May be because of active segments in temp it didnt get resized Before.

                            Thanks to all.


                            Thanks & Regards,
                            SID

                            (StepIntoOracleDBA)

                            Email : stepintooracledba@gmail.com

                            http://stepintooracledba.blogspot.in/

                            http://www.stepintooracledba.com/

                            Edited by: Step Into Oracle DBA on Feb 18, 2013 2:01 AM
                            • 11. Re: Shrinking of Temp Tablespace doesnot shrink tempfile at OS level
                              Nicolas.Gasparotto
                              I find a bit strange that you ask question over the forum, should the answer not being obvious to you ?
                              From your training center promoted in every of your post :
                              "Becoming a professional ORACLE DBA is not a BIG DEAL any more.
                              SID makes your dreams true to achieve your target (ORACLE DBA) by just crossing 7 Easy steps.
                              So what are you waiting for? Enroll and Enjoy."
                              And if you don't know something, what refrain you to take training over the week-end :
                              "Attend ORACLE DBA trainings in Weekends and become an professional ORACLE DBA"

                              +-- promoting its own commercial website is considered as spam over here, moderation actions can be taken --+

                              Nicolas.