Temporary tablespace full to 100%. need to reduce size. 8i.

Before this I will recommend to make an cold backup and full export (if you have time and space).
Why you do not restart the instance? The temporary tablespaces should be emptied by shutdown.

I know, I tried that, but the effects were the tablespace only decreased by 1% then went back up to almost 100% when users ran some queries.

Look at this:
Dropping really messed up Temporary Tablespace

I'v managed to really mess up a Temporary tablespace. It had grown to 15Gb(!), and I needed to regain the space. Suffice to say I did not do it well. I ended up in this situation. I have a new good Temporary tablespace that my users have as their default. I have the old tablespace off-line, with files that are marked as needing media recovery. In addition, one of the files (the 15Gb one) no longer exists. When I try to drop the table space (drop tablespace including contents) the command just hangs and one CPU on my RS/6000 is pegged at 100%. After an hour or so I kill the process. Is it normal to take more than an hour to drop a tablespace, particularly when you are not cascading constraints (there are none)? What are my options to get rid of this tablespace? Do I just let the drop tablespace command run for hours? If so, why does it take so long? What is it doing? Any help would be appreciated.

More Info:I am running in Archivelog mode, all files show up in v$recover_file, the missing file has an error of "file not found' and the others have no errors.

Mark Saltzman
University of Wisconsin-Extension
mark.saltzman@uwex.edu
608.263.3084




========================
It is possible that you (and the other poster) are
suffering from the 'well-known' problem of having
a large temporary tablespace with a very small
extent size.

The effect of this combination is that you end
up with a temporary segment with a very large
number of extents. As a consequence you are now
seeing a lot of activity on fet$ and uet$ as Oracle
tries to clear up the mess before dropping the tablespace.

Try the drop command with sql_trace switched on, and
you will probably see lots of activity on these two
tables. If this is the issue, the correct solution is
to let the thing run (48 hours has been reported in
the past) until it completes.

One EMERGENCY, UNSUPPORTED, MAY CAUSE YOUR DATABASE
TO BE IRRETRIEVABLY CORRUPTED option is to delete the
entries for that tablespace from uet$ and fet$ by
hand (based on the ts# you find for the tablespace in
the table ts$) and then drop the tablespace.

that's why you NEED to SHUTDOWN the database .
You cannot use offline approach to drop the temp tablespace.

SHUTDOWN IMMEDIATE first,

then do the dropping.

awsome, thank you seazodiac, however, I have to wait for off-hours to do this.  I don't feel comfortable dropping tablespaces in the middle of the day :).  But I will let you know how this will turn out.
Also, if I stop the Oracle instance, I will then have to stop the ManagementServer first, right? so then would I have to start the database with "nomount" or anything like that in order to create the Temp2 tablespace and then drop the Temp?


-Metro.

if I stop the Oracle instance, I will then have to stop the ManagementServer first, right?
--->it's better do that. because OMS will lose connection anyway once the instance is shutdown.

 so then would I have to start the database with "nomount" or anything like that in order to create the Temp2 tablespace and then drop the Temp?
--->No, you don't need. just open database up (maybe it's good idea to use "startup restricted"), then create an alternative temp tablespace,then you are all set to drop the old one.

Thank you both, seazodiac and schwertner for your insight.
I'll award the points a bit later when I see my new Tablespace working  :)

and you know what's strange, my current Temp tablespace just dropped below 13 GB as we speak.  I am trying to understand why it took a whole day to do this (starting with yesterday afternoon).  But I still would like to have it Locally Managed.

-Metro

Also increase your SORT_AREA_SIZE. This will make sure that the number of Disk sorts are reduced and therefore the load on the temp tablespace is also reduced. Make sure that the default extent sizing is a multiple (preferably equal) to the value you set for SORT_AREA_SIZE.

hey all,

I tried to create the second Locally managed temp space on a test system that I have and which is configured identically to production, and I get the following error message when I try to make temp2 the default temporary tablespace:

SQL> alter database default temporary tablespace temp2;
alter database default temporary tablespace temp2
               *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

I tried both with dB instance up or down.  Also, when I create a new tablespace, the Instance must be up.
Am I doing something wrong with the Alter Database command?  

alter user <username> default tablespace temp2;
for all the users

the default temporary tablespace is for the user , not for database.

did you read my comments in step 6,7?

if you have sevral users, just do like anand_2000v said.

if you have too many users, do like my comments in step 6,7:

SQL>spool change_TS.sql;
SQL>select 'alter user '||username || ' temporary tablespace temp1;' from dba_users ;
SQL>spool off;
SQL>@change_TS.sql;

great, now I get an error saying that it's in EXCLUSIVE Mode.  what does this mean?
I was trying to shutdown and restart so I can create my new TEMP space, but I get the following error message

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict
ORACLE instance started.

Total System Global Area 1126959132 bytes
Fixed Size                    75804 bytes
Variable Size             258338816 bytes
Database Buffers          868466688 bytes
Redo Buffers                  77824 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

just reboot the machine, bring the database up , that exclusive message will disappear.

I tried that.  I still get the same message.

I never thought this will ramify into this....

try if this note will fix your problem

You are trying to startup the database and you receive the following error:  
 
   ORA-01102:  cannot mount database in EXCLUSIVE mode  
       Cause:  Some other instance has the database mounted exclusive  
               or shared.  
      Action: Shutdown other instance or mount in a compatible mode.  
 
 
Problem Explanation:  
====================  
 
A database is started in EXCLUSIVE mode by default.  Therefore, the  
ORA-01102 error is misleading and may have occurred due to one of the  
following reasons:  
 
  - there is still an "sgadef<sid>.dbf" file in the "ORACLE_HOME/dbs"  
    directory  
  - the processes for Oracle (pmon, smon, lgwr and dbwr) still exist  
  - shared memory segments and semaphores still exist even though the  
    database has been shutdown  
  - there is a "ORACLE_HOME/dbs/lk<sid>" file  
   
 
Search Words:  
=============  
 
ORA-1102, crash, immediate, abort, fail, fails, migration
 
Solution Description:  
=====================  
 
Verify that the database was shutdown cleanly by doing the following:  
 
1. Verify that there is not a "sgadef<sid>.dbf" file in the directory  
   "ORACLE_HOME/dbs".    
 
        % ls $ORACLE_HOME/dbs/sgadef<sid>.dbf  
 
   If this file does exist, remove it.  
 
        % rm $ORACLE_HOME/dbs/sgadef<sid>.dbf  
 
2. Verify that there are no background processes owned by "oracle"  
 
        % ps -ef | grep ora_ | grep $ORACLE_SID  
 
   If background processes exist, remove them by using the Unix  
   command "kill".  For example:  
 
        % kill -9 <Process_ID_Number>  
 
3. Verify that no shared memory segments and semaphores that are owned  
   by "oracle" still exist  
 
        % ipcs -b  
 
   If there are shared memory segments and semaphores owned by "oracle",  
   remove the shared memory segments  
 
      % ipcrm -m <Shared_Memory_ID_Number>  
 
   and remove the semaphores  
 
      % ipcrm -s <Semaphore_ID_Number>  
 
   NOTE:  The example shown above assumes that you only have one  
          database on this machine.  If you have more than one  
          database, you will need to shutdown all other databases  
          before proceeding with Step 4.  
 
4. Verify that the "$ORACLE_HOME/dbs/lk<sid>" file does not exist  
 
5. Startup the instance  
 
 
Solution Explanation:  
=====================  
 
The "lk<sid>" and "sgadef<sid>.dbf" files are used for locking shared memory.  
It seems that even though no memory is allocated, Oracle thinks memory is  
still locked.  By removing the "sgadef" and "lk" files you remove any knowledge
oracle has of shared memory that is in use. Now the database

Yes, Finally...Everything worked.  Thank you soooo much everyone for bearing with me throughout this whole ordeal.  One additional thing, seazodiac, the last document to kill the processes is for UNIX systems, but you gave me the idea and I found one for NT. here's the link to it:

https://www.experts-exchange.com/questions/20339340/ORA-01102-ERROR-WHEN-STARTING-DATABASE.html

Althought it wasn't necessary because I found out what I was doing wrong.  for some reason this dB doesn't have the spfile create, therefore, I had to  use the following command with the pfile.

SQL> startup restrict pfile=C:\oracle\admin\<SID>\pfile\ini<SID>.ora

worked like a charm after that.

Danielzt, thank you for your idea on how to create the script to modify all my users at the same time.

-Metro