TEMP tablespace - URGENT...
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: TEMP tablespace - URGENT...

  1. #1
    Join Date
    Jun 2002
    Posts
    88

    TEMP tablespace - URGENT...

    Dear Gurus,

    My Temp tablespace shows it has 3GB filled. How can i reduced it??
    Coalescing doesn't make any diff. Please let me know how i get back
    the space??

    My Temp tablespace is Locally Managed. PCT_INCREASE value is 0.

    Can i change change PCTINCREASE to 1 and then back to 0. It should coalesce itself?

    I use this command but i got the error

    SQL> alter tablespace temp
    2 default storage (
    3 pctincrease 1);
    alter tablespace temp
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

    How to use this command? can u please advise me,

    Thanks in advance,
    Iyyappan.M

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    there is no need to coalesce the Temp-Ts.
    What you see is the 'Highwatermark' of the Temp-Segment.
    The segment can be allocated for a transaction in the past
    and now be empty.

    Try to identify the SQL's which needs teh temp-Ts,
    and tune them or replace the logic of expensive code.

    Orca


    --
    -- KRE CSC SQL's die TEMP belegen
    --
    SELECT su.tablespace,
    se.sid,
    se.serial#,
    su.blocks,
    sa.sql_text
    FROM v$sort_usage su
    ,v$session se
    ,v$sqlarea sa
    WHERE su.session_addr = se.saddr
    AND se.sql_address = sa.address
    ORDER
    BY su.tablespace,
    se.sid,
    se.serial#,
    sa.address
    Last edited by Orca777; 11-12-2002 at 03:20 AM.

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Seems you get in this situation quite often ... http://forums.dbasupport.com/forums/...threadid=29705

    * ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
    * ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
    * ALTER DATABASE TEMPFILE 'old_temp_file' DROP;
    this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
    * go to the OS and remove the old_temp_file
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by ales
    Seems you get in this situation quite often ... http://forums.dbasupport.com/forums/...threadid=29705

    * ALTER TABLESPACE TEMP ADD TEMPFILE 'new_temp_file'
    * ALTER DATABASE TEMPFILE 'old_temp_file' OFFLINE;
    * ALTER DATABASE TEMPFILE 'old_temp_file' DROP;
    this is often not possible, as users may have space allocated in the tempfile. Be patient, kill the sessions or restart the database
    * go to the OS and remove the old_temp_file
    Hi is there a need to drop the datafile?
    And ... is it possible to drop a datafile without complication?
    I learned that to have fewer datafiles in a Tablespace you have to recreate the whole Tablespace ?


    The Actual file deallocates the storage when you alter the storage ( same value )
    alter tablespace temp
    2 default storage (
    3 pctincrease 0);

    Orca

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    I believe his TEMP tablespace is the true temporary tablespace - created with the "CREATE TEMPORARY TABLESPACE" command. It has tempfiles instead of datafiles and it's possible to offline and drop tempfile without any problems - if there aren't active transactions.

    In a "normal" tablespace with datafiles you cannot drop a datafile without dropping the tablespace but this is another issue.

    The technique I posted is the least painful I've found for "shrinking" true temporary tablespace.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    There is a simpler way to 'coalesce' a temporary type tablespace

    First switch the tablespace back to permenant
    Code:
    alter tablespace xxx permanent;
    Then coalesce the tablespace
    Code:
    alter tablespace xxx coalesce;
    Lastly switch the tablespace back to temporary
    Code:
    alter tablespace xxx temporary;
    Using this method you do not have to create any additional datafiles or drop any.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Refer to Managing Tablespaces chapter of Oracle 9i Administrator Guide for technical information about LOCALLY MANAGED TEMPORARY TABLESPACE.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Jim:
    This is OK for old fashioned temporary tablespaces with datafiles.
    Iyyappan has the true temporary tablespace and switching permanent/temporary fails with it:

    system@oracle>system@oracle> create temporary tablespace temp1
    2 tempfile 'g:\orant\database\temp1a.dbf' size 1M reuse
    3 extent management local uniform size 64k
    4 /

    Tablespace created.

    system@oracle> alter tablespace temp1
    2 default storage (pctincrease 1)
    3 /
    alter tablespace temp1
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE


    system@oracle> alter tablespace temp1 permanent
    2 /
    alter tablespace temp1 permanent
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

    system@oracle> alter tablespace temp1
    2 add tempfile 'g:\orant\database\temp1b.dbf' size 1M reuse
    3 /

    Tablespace altered.

    system@oracle> alter database tempfile 'g:\orant\database\temp1a.dbf' offline
    2 /

    Database altered.

    system@oracle> alter database tempfile 'g:\orant\database\temp1a.dbf' drop
    2 /

    Database altered.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  9. #9
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Ales,

    Your quite right, I didn't read the intermediate posts properly. In this case the method you have given is the least painful

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  10. #10
    Join Date
    Jun 2002
    Posts
    88
    Hi Ales,

    Thanks for ur reply.

    Have a good day,

    Regards,
    Iyyappan.M

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