Temp tablespace file grew to 32GB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Temp tablespace file grew to 32GB

  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Temp tablespace file grew to 32GB

    Hi!
    I have Oracle 9i Standard server on Windows 2000 Server. I had Temp tablespace file grow to its limit of 32GB. Since the database had no other files in the tablespace I created another file for temporary solution.

    The data in the database use about 8.6GB, and I am wondering what could cause such growth. If this is a query, is there a way to trace it? I am also looking for the best way to decrease the space of the database. My colleague proposes that I just drop the file and recreate it. Is that safe? Is there a way to just free up some space in the file and then shrink it?

    Thanks.
    Last edited by Fish; 11-21-2005 at 10:29 PM.

  2. #2
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    Quote Originally Posted by Fish
    Hi!
    I have Oracle 9i Standard server on Windows 2000 Server. I had Temp tablespace file grow to its limit of 32GB. Since the database had no other files in the tablespace I created another file for temporary solution.

    The data in the database use about 8.6GB, and I am wondering what could cause such growth. If this is a query, is there a way to trace it? I am also looking for the best way to decrease the space of the database. My colleague proposes that I just drop the file and recreate it. Is that safe? Is there a way to just free up some space in the file and then shrink it?

    Thanks.
    create a new temporary tablespace and make it default.
    delete old one.
    ~KD.DBA~

  3. #3
    Join Date
    Nov 2005
    Posts
    4
    Do I need to shutdown and restart the database for this? The old tablespace shows 99% full.

  4. #4
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    No, this did not require database shutdown. you can create temporary tablespace while being database up.
    ~KD.DBA~

  5. #5
    Join Date
    Nov 2005
    Posts
    4
    I am new to Oracle. Could you, please, provide a sample of command to create a new temp tablespace, make it default and drop the old one. Could there be any user interuprions, like failed query using the old, deleted, temp tablespace?
    Last edited by Fish; 11-22-2005 at 12:10 AM.

  6. #6
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    Are you getting any error in alert file like (unable to extend temp segment)
    if yes, then there could be need to create new temporary tablespace,

    The data in the database use about 8.6GB, and I am wondering what could cause such growth.

    it is good that temporary tablespace is showing full, no need to worry unless it is not showing any error in alert file. because the system will allocate an extent in TEMP and then keep it and manage it itself. This is normal and to be expected and is not an indication that you do not have any temporary space.

    V$SORT_USAGE will tell you who's using what.
    ~KD.DBA~

  7. #7
    Join Date
    Nov 2005
    Posts
    4
    Yes, I am getting an error: unable to extend temp segment. After such alert, I created additional file in the same tablespace and looking for painless way to free up disk space.

  8. #8
    Join Date
    Sep 2005
    Location
    Delhi
    Posts
    78
    yes then we need to create a tablespace with larger size.
    for this use: (if ur old temporay tablespace was locally managed tablespace) then follow

    1. create temporary tablespace temp01
    tempfile 'c:\temo1.tmp' size 100m

    The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size

    2. alter database default temporary tablespace temp01

    3. Make old temporary tablespace offline or u can delete it.
    ~KD.DBA~

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Growth like this can indicate bad SQL ... for example a developer has missed out a join between tow tables and generated a cartesian product..
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by Fish
    Hi!
    I have Oracle 9i Standard server on Windows 2000 Server. I had Temp tablespace file grow to its limit of 32GB. Since the database had no other files in the tablespace I created another file for temporary solution.

    The data in the database use about 8.6GB, and I am wondering what could cause such growth. If this is a query, is there a way to trace it? I am also looking for the best way to decrease the space of the database. My colleague proposes that I just drop the file and recreate it. Is that safe? Is there a way to just free up some space in the file and then shrink it?

    Thanks.
    Just create temporary tablespace with 4 GB or 8 GB. Remove all "auto" options.

    Tamil

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