Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    15

    Unanswered: Cannot resize Temporary Tablespace

    Hello to All,
    i have got e courios Problem:
    In 9.2.0.5-Oracle database i cannot decrease the Temp-Tablespace.
    It seem it is empty (the select * from V$SORT_SEGMENT; "said it"). The OEM describe the Temp-Ts as full and Toad from Quest get complet strange Pic: instead white (= empty) i get grey (= i dont know).
    This Problem is at on Database, the Application-Programm is based on COBOL.
    My Hardware is AIX-Regatta-Server (AIX5.1) and the Storage-System is EMC2.
    I restarted sometime the database, no jobs are running but nothing happen!
    Has anyone a ideo for decreasing the temp-TS?

    Thanks! Karl
    nobody is perfect

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What you do is to create a new temp tablespace, drop the old one, recreate the temp tablespace and then make it the default. So if your current temp tablespace is called TEMP then do the following (replacing path and size parameters).

    Alan

    CREATE TEMPORARY TABLESPACE temp2
    TEMPFILE 'K:\ORADATA\PROD1\TEMP01.DBF' SIZE 5M REUSE
    AUTOEXTEND ON NEXT 1M MAXSIZE 500M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

    alter tablespace temp offline

    DROP TABLESPACE temp INCLUDING CONTENTS
    AND DATAFILES;

    CREATE TEMPORARY TABLESPACE temp
    TEMPFILE 'J:\ORADATA\PROD1\TEMP01.DBF' SIZE 500M REUSE
    AUTOEXTEND ON NEXT 100M MAXSIZE 5500M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

    DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

  3. #3
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    15
    Hello Alan,
    i forgot to say: I recreated sometimes the Temp -TS as your suggest.
    after running and finishing Application-Program the TS seems full. Dropping and recreating didnot help.
    Thanks! Kurt
    nobody is perfect

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget temp is not cleared down it reuses the extents as required. So over time temp will fill up and stay filled up but that doesnt mean it cant fulfil sort requests.

    Alan

  5. #5
    Join Date
    Dec 2004
    Location
    Frankfurt
    Posts
    15
    Hello,
    you are right! the Temp-Ts is full. We got no Problems with our Applications.
    And the same behavior is at our SAP-Business-Applications.
    Thnaks for your Help!
    Kurt
    nobody is perfect

Posting Permissions

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