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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
switch to it before dropping the old one.
-- Create a New UNDO Tablespace
CREATE UNDO TABLESPACE undotbs02
DATAFILE '/define_the_path/and_thed
-- 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;