Link to home
Start Free TrialLog in
Avatar of hiramindiaz
hiramindiaz

asked on

How i candelete o reduce UNDOTBS01.DBF

I have a server Dell PowerEdge 6400, I have installed Oracle 9i and I need to delete the file UNDOTBS01 because the size of this file is 16Gb and the free space in the server is only 2 Gb, This server have an aplication that run all day and i do not reboot the server.
Avatar of prast1007
prast1007

You must have an active Undo tablespace, so you need to create a new one and
switch to it before dropping the old one.
-- Create a New UNDO Tablespace
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/define_the_path/and_thedatafile.dbf' SIZE 400M REUSE AUTOEXTEND ON;

-- Alter the system, so it is using the new UNDO Tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs02;

-- You have to wait before the old undo can be drop (Must be don't have any online segments).
declare
   nmbCount number := 1;
begin
   while nmbCount > 0 loop    
            select count(segment_name)
               into nmbCount
             from dba_rollback_segs
          where status='ONLINE'
              and tablespace_name='UNDOTBS01';
   end loop;
end;
/

-- shutdown the old UNDOTBS01
alter tablespace UNDOTBS01 offline;

-- You can drop the datafile
drop tablespace UNDOTBS01 including contents and datafiles;

Avatar of hiramindiaz

ASKER

Hi prast1007:
    The file undotbs02 was created, and i run the script and the apliccation send me the next message:
"PL/SQL procedure successfully completed." but when i run the next sentence, it send me an error:
"alter tablespace UNDOTBS01 offline
*
ERROR at line 1:
ORA-00959: tablespace 'UNDOTBS01' does not exist"

or

"alter tablespace UNDOTBS01.dbf offline
                          *
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option"

What is wrong? you say that "i need to wait before the old undo can be drop", how many time? or i need to do other thing.
ASKER CERTIFIED SOLUTION
Avatar of prast1007
prast1007

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial