-
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 11:29 PM.
-
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~
-
Do I need to shutdown and restart the database for this? The old tablespace shows 99% full.
-
No, this did not require database shutdown. you can create temporary tablespace while being database up.
~KD.DBA~
-
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 01:10 AM.
-
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~
-
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.
-
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~
-
Growth like this can indicate bad SQL ... for example a developer has missed out a join between tow tables and generated a cartesian product..
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|