2 Replies Latest reply: Jan 3, 2013 9:21 AM by redologger RSS

    shrink / resize to a smaller value in production.

    redologger
      hi, just wonder

      will there be any outrage if i do a shirnk/ resize of my temp tablespace on my production database.

      by doing,
      reate a new temporary tablespace, set it as the default temporary tablespace then drop your old default temporary tablespace and recreate it. Afterwords drop the second temporary table created.
      SQL> CREATE TEMPORARY TABLESPACE temp2
      2  TEMPFILE '/the/full/path/to/temp2_01.dbf' SIZE 5M REUSE
      3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
      4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
      
      Tablespace created.
      
      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
      
      Database altered.
      
      SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
      
      Tablespace dropped.
      
      
      SQL> CREATE TEMPORARY TABLESPACE temp
      2  TEMPFILE '/the/full/path/to/temp01.dbf' SIZE 256M REUSE
      3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
      4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
      
      Tablespace created.
      
      SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
      
      Database altered.
      
      SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
      
      Tablespace dropped.
        • 1. Re: shrink / resize to a smaller value in production.
          JohnWatson
          You might want to check whether any schemas have their temporary tablespace explicitly set to temp before dropping it: I think they will not have been changed to temp2 after you make it default.
          In general, this is a nice example of why temporary tablespace groups are useful: assign users to the group, and you can do what you like with the tablespace(s) within the group without worrying about the users.
          • 2. Re: shrink / resize to a smaller value in production.
            redologger
            JohnWatson wrote:
            You might want to check whether any schemas have their temporary tablespace explicitly set to temp before dropping it: I think they will not have been changed to temp2 after you make it default.
            In general, this is a nice example of why temporary tablespace groups are useful: assign users to the group, and you can do what you like with the tablespace(s) within the group without worrying about the users.
            So is the timeframe when the making the changes. Temp2 as default while creating the new temp before assigning as the default temp. So do it when there is no or low activities?

            Yes for pointing out for using groups.