temp tablespace problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: temp tablespace problem

  1. #1
    Join Date
    Aug 2001
    Posts
    16

    temp tablespace problem

    Hi All,

    OS : NT 4
    DB : Oracle 8.1.5

    We have temp tablespace which is occupied more space in our db.Can anyone help us to clear the temp tablespace.if we remove this, will it be a problem for our db.

    pls help.

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can drop and recreate the TEMP tablespace if there are no active transactions using TEMP tablespace.
    Or simply bounce the instance if possible.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Make sure no one has the TEMP tablespace as their default tablespace.

    1. make the tablespace offline.
    2. drop it
    3. recreate it with smaller datafiles.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    The top of your Temp-Ts allocates Blocks is the High-Water-Mark.
    This means that the place could be free for new transaction even it is allocated.
    You can monitor which SQL uses the temp-Ts actuall.
    Recreating Temp will not help you.
    You have to identify and tune the SQL's which fill the Temp-Ts

    Orca

    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(+)
    /

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by adewri
    Make sure no one has the TEMP tablespace as their default tablespace.
    It doesn't matter, they can not create any object in TEMP tablespace anyway.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Aug 2001
    Posts
    16
    Dear Mr.Sanjay,

    Thanx for ur info and i would like to know which table occupies more space on the tablespace.

  7. #7
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    select segment_name,tablespace_name,sum(bytes/(1024*1024)) "Table Size in MB"
    from user_segments
    where tablespace_name=''
    group by segment_name,tablespace_name
    order by sum(bytes/(1024*1024)) desc

    This query would help u in finding the Segment Sizes

  8. #8
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by Seenivas
    Thanx for ur info and i would like to know which table occupies more space on the tablespace.
    You can't create any permanent segment in TEMP tablespace so there is no question of having tables there. TEMP tablespace have segments used for sorting the data.
    If you want to monitor the space usage by table in permanent tablespaces then you can use the query Sridhar has provided.
    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

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