Xử lý như sau
sqlplus sys/sysdba as sysdba
1. SQL> startup mount;
2. SQL>show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
3. SQL> alter system set undo_management='MANUAL' scope=spfile;
4. SQL>shutdown immediate;
5. SQL> conn / as sysdba
6. startup;
Execute following query to find undo tablespace name.
SELECT VALUE, name
FROM gv$parameter
WHERE name LIKE '%undo_tablespace%';
execute following query and verify undo tablespace path and name and note the information path can be used to create new file in coming steps.
select tablespace_name,file_name
from dba_data_files
where tablespace_name like 'UNDO%'
if the undo tablespace name is different than the name returned by query at step 5 then execute following query and drop the tablespace returned by above query.
SQL> DROP TABLESPACE Tablespace name INCLUDING CONTENTS AND DATAFILES;
Example
-- SQL> DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;
9. SQL>create undo tablespace UNDOTBS1 datafile '/location/filename' size 50M autoextend on;
10. SQL> alter system set undo_management='AUTO' scope=spfile;
11. SQL>shutdown immediate;
12 SQL>conn / as sysdba
13. SQL> startup mount;
14. SQL> show parameter undo;
14 SQL> alter database open;
16. SQL> alter system set undo_tablespace='UNDOTBS1' scope=BOTH;
--------------------------------------------
If database is running and undo_management='AUTO'.
sqlplus sys/sysdba as sysdba
create undo tablespace UNDOTBS datafile 'location/filename' size 50M autoextend on;
alter system set undo_tablespace='UNDOTBS' scope=BOTH;