Online expansion of database UNDO tablespace

Online expansion of database UNDO tablespace

Expand the database UNDO tablespace online and log on to the Oracle database server with an Oracle account

Method 1:

View the tablespace name and file location:

Select tablespace_name, file_id, file_name, round (bytes/(1024*1024), 0) total_space

From dba_data_files order by tablespace_name;

Modify the data file size of the database to the new size.

Alter database datafile '\ oracle \ oradata \ undotab1.dbf' resize 4000 m;

Method 2:

Start SQL * Plus session and execute the following command:

Oracle % sqlplus/nolog
SQL> connect/as sysdba;
SQL> spool $ ORACLE_BASE/admin/oss/scripts/recreate_undo.log;
SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/db/data/undotbs02.dbf' SIZE 30 M REUSE AUTOEXTEND ON NEXT 5120 K MAXSIZE 32767 M;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = "UNDOTBS2 ";
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE BIGFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/db/data/undotbs01.dbf' SIZE 35 M REUSE AUTOEXTEND ON NEXT 5120 K MAXSIZE 128G;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = "UNDOTBS1 ";
SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
SQL> exit
Oracle %

NOTE!

If you see this error: "ORA-30013: undo tablespace 'undotas1' is currently in use" takes 10-30 seconds, sometimes longer, and then re-execute the above command. You can also run "UNDOTBS1/UNDOTBS2" to check whether the DROP command can execute SQL> SELECT SEGMENT_NAME, XACTS, V. status from v $ rollstat v, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'undotbs1' AND SEGMENT_ID = USN; returned results:

"No rows selected"

If UNDOTBS1 is still in use, restart the ORACLE service and then perform the preceding steps.

--------------------------------------------------------------------------------

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------