Oracle UNDO tablespace Reconstruction Method

Oracle is still quite common, So I studied the Oracle UNDO tablespace and shared it with you here. I hope it will be useful to you.

1. In order to open the database, the first thought is to clear bad logs,:

 
 

  
  
SQL> alter database clear unarchived logfile group 3 ;  
  
  
SQL> alter database clear unarchived logfile group 3 unrecoverable datafile;  
  
  
alter database clear unarchived logfile group 3 unrecoverable datafile 
 
 

Ii. Reconstruction of the system Oracle UNDO tablespace

1. Create a New Oracle UNDO tablespace

 
 

  
  
CREATE UNDO TABLESPACE "UNDOTEMP"  
  
  
DATAFILE 'G:\ORACLE\ORADATA\HOME\UNDOTEMP.ora' SIZE 5M  
  
  
CREATE UNDO TABLESPACE "UNDOTEMP"  DATAFILE 
  
  
'/home/oracle/oradata/oracle9/UNDOTEMP.ora' SIZE 50M 
 
 

2. On the OEM console, modify the default Oracle UNDO tablespace name from UNDOTBS to UNDOTEMP, or use the following command to modify the identifier of the default Oracle UNDO tablespace.

 
 

  
  
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTEMP 
 
 

3. Delete the default Oracle UNDO tablespace and recreate the UNDOTBS.

 
 

  
  
DROP TABLESPACE "UNDOTBS1" INCLUDING CONTENTS AND DATAFILES 
 
 

4. On the OEM console, modify the default Oracle UNDO tablespace name from UNDOTEMP to UNDOTBS and change it back to the original Oracle UNDO tablespace ID.

 
 

  
  
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS 
 
 

5. Delete the temporary Oracle UNDO tablespace UNDOTEMP

 
 

  
  
DROP TABLESPACE "UNDOTEMP" INCLUDING CONTENTS AND DATAFILES 
 
 

Iii. ORACLE 9I rebuilding TEMP tablespace

1. startup: Start the database.

2. create temporary tablespace TEMP_TEMP TEMPFILE '/home/oracle/oradata/oracle9/temp_temp.dbf' SIZE 128 M REUSE AUTOEXTEND
On next 640 k maxsize unlimited: create temporary tablespace for transit

3. alter database default temporary tablespace TEMP_TEMP: Change the default temporary tablespace to the newly created temporary tablespace temp2.

4. drop tablespace temp including contents and datafiles: Delete the original temporary tablespace

5. create temporary tablespace temp tempfile '/home/oracle/oradata/oracle9/temp01.dbf' SIZE 512 m reuse autoextend on next 640 k maxsize unlimited: recreate a temporary tablespace

6. alter database default temporary tablespace temp: reset the default temporary tablespace

1. Five-minute master of Oracle tablespace
2. Explain how to query user tablespaces in Oracle
3. An analysis of the specific methods for assigning Oracle user permissions
4. Step 4: Change the Oracle user name
5. Simple Oracle user table search example