Temporary tablespace size huge
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Temporary tablespace size huge

  1. #1
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49

    Temporary tablespace size huge

    Hi Guys,

    My oracle8i's temp tablepsace has been increasing. Now is at 2gb. is there any ay i can shrink it down? Is this temp the same as those datafiles where i can just issue a "resize" command to shrink to to the last data block is that used?

    thanks
    Regards
    CF

  2. #2
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    The TEMP tablespace should not have the autoextend option turned on, that is why your tablespace is growing.

    As for "Resizing" the temp tablespace, you have a couple of options:

    If you are able to have exclusive control of the DB (i.e. A database maintenance/downtime, then use the following option:

    -- Shutdown the DB
    Start the DB in restriected mode
    Drop the temp tablespace
    Recreate the temp tablespace


    If unable to have exclusive control (i.e 24x7 ) of the DB then:
    -- Create a new tablespace (i.e. TEMP1)
    -- Switch all users to use this new tablespace as their default TEMP
    -- Drop the "old" TEMP (2GB) tablespace
    -- Recreate the TEMP tablespace
    -- Switch all users to use TEMP as their default TEMP
    -- Drop the "Temporary temp" TEMP1 tablespace.
    Last edited by alapps; 01-07-2003 at 10:49 PM.
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  3. #3
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    To add to alapps comments, dont forget to add TEMPORARY clause to your statement as follows:

    CREATE TABLESPACE temp1 datafile '.../temp01.dbf' SIZE 300M
    DEFAULT STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 500)
    TEMPORARY;

    This assures that nobody can create permanent objects in this ts.
    Agasimani
    OCP(10g/9i/8i/8)

  4. #4
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    49
    I have tried to do a resize on my devp database for the temp tablespaces and it works. Can this be done on the production systems?
    Regards
    CF

  5. #5
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    A resize will work if the area at the end of the datafile is not in use. A production database will most likely have that in use, especially in this case since the TEMP tablespace keeps growing (Autoextend should not be on for datafiles within temp tablespaces).

    The drop and recreate in restricted mode was recommended since it works.
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Temporary tablespace size huge

    Originally posted by feng11
    Hi Guys,

    My oracle8i's temp tablepsace has been increasing. Now is at 2gb. is there any ay i can shrink it down? Is this temp the same as those datafiles where i can just issue a "resize" command to shrink to to the last data block is that used?

    thanks
    Use this query to find out the HWM of the file. That is up to where you can resize (downwards):

    col "File Name" for A40
    column file_name format a40;
    column highwater format 9999999999;
    SELECT Substr(df.file_name,1,40) "File Name",
    Round(df.bytes/1024/1024,2) "Size (M)",
    Round(e.used_bytes/1024/1024,2) "Used (M)",
    Round(f.free_bytes/1024/1024,2) "Free (M)",
    round((b.maximum+c.blocks-1)*d.db_block_size/(1024*1024)) "HWM (M)"
    FROM dba_data_files df,
    (SELECT file_id, Sum(Decode(bytes,NULL,0,bytes)) used_bytes FROM dba_extents GROUP by file_id) e,
    (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f,
    (SELECT file_id, max(block_id) maximum from dba_extents group by file_id) b,
    dba_extents c,
    (SELECT value db_block_size from v$parameter where name='db_block_size') d
    WHERE e.file_id (+) = df.file_id
    AND df.file_id = f.file_id (+)
    AND df.file_id = b.file_id and c.file_id = b.file_id and c.block_id = b.maximum
    ORDER BY
    df.tablespace_name, df.file_name
    /
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Also, make it locally managed, and set the extent size to be the same as your db sort_area_size
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by slimdave
    Also, make it locally managed, and set the extent size to be the same as your db sort_area_size
    Good advice!

    That is if you use SORT_AREA_SIZE and not:

    pga_aggregate_target = 512M
    workarea_size_policy = auto

    Even in this case SORT_AREA_SIZE is set by Oracle (64K) but then the extent size need not be 64K.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    identify the reason why Temp increases so much :
    Code:
    --
    -- KRE CSC SQL's die TEMP belegen
    --
    SELECT /*+ORDERED*/su.tablespace,
           se.sid,
           se.user#,
           se.serial#, 
           su.blocks,
           su.segtype,
           sa.sql_text
     FROM  v$sort_usage su
          ,v$sqlarea    sa
          ,v$session    se
    WHERE su.session_addr = se.saddr
      AND se.sql_address  = sa.address(+)
    /*ORDER
       BY su.tablespace,
          se.sid,
          se.serial#,
          sa.address
    */
    Orca









Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width