Online expansion of database UNDO tablespaces and expansion of database undo tables

Online expansion of database UNDO tablespaces and expansion of database undo tables

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_spacefrom 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 4000m;

Method 2:

Start SQL * Plus session and execute the following command:

oracle% sqlplus /nologsql> 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 30M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M;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 35M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 128G;sql> ALTER SYSTEM SET UNDO_TABLESPACE="UNDOTBS1";sql> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;sql> exitoracle%

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 = 'undomains1' 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.

How Does oracle expand the undo tablespace?

There are two solutions,
First, add the undo tablespace data file,
Second, switch undo tablespace. In this case, it is usually used in the case that the undo tablespace is already very large.
1. Add data files
SQL> alter tablespace undo add datafile 'd: \ undo02.dbf 'size 100 m reuse;
The tablespace has been changed.

2 switch undo tablespace

1. Create a New tablespace undotbs2
SQL> create undo tablespace undotbs2 datafile 'd: \ undo03.dbf' size 100 m reuse;
The tablespace has been created.

2. Switch to the newly created undo tablespace. The operation is as follows:
SQL> alter system set undo_tablespace = undotbs2 scope = both;
The system has been changed.

3. Set the original undo tablespace to offline:
SQL> alter tablespace undo offline;
The tablespace has been changed.

4. Delete the original undo tablespace:
SQL> drop tablespace undo including contents and datafiles cascade constraints
The tablespace has been deleted.

If you only drop tablespace undo, only records in the control file will be deleted and files will not be physically deleted.
The drop undo tablespace can be performed only when it is not used.
If the undo tablespace is being used (for example, the transaction fails, but the restoration is not successful), the drop tablespace command will fail. You can use including contents to drop the tablespace.

How to release the undo tablespace in Oracle

In daily database maintenance and database programming, we often hesitate to perform DML operations on large data volumes, so that ORACLE's undo tablespace can be extended to dozens of GB or dozens of GB. the physical space occupied by the disk will not be released by oracle, if you are using a PC, you may encounter insufficient disk space. After completing the following operations, you can reconstruct the undo tablespace, similarly, the temp tablespace may be infinitely expanded when you query big data or create an index, resulting in insufficient disk space. You can also solve this problem using the following methods: -- view the names of tablespaces select name from v $ tablespace -- view the information of a tablespace select file_name, bytes/1024/1024 from dba_data_files where tablespace_name like 'undotbs1'; -- view the use of rollback segments, which user is using resources of the rollback segment? If there is a user, it is best to change the time (especially in the production environment ). Select s. username, u. name from v $ transaction t, v $ rollstat r, v $ rollname u, v $ session s where s. taddr = t. addr and t. xidusn = r. usn and r. usn = u. usn order by s. username; -- check the UNDO Segment status select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize; -- create a new UNDO tablespace, and set automatic extension parameters; create undo tablespace undotbs2 datafile 'd: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ UNDOTBS02.DBF 'size 10 m reuse autoextend on next 100 m maxsize unlimited; -- dynamically change the spfile configuration file; alter system set undo_tablespace = undotbs2 scope = both; -- wait for all undo segment offline in the original UNDO tablespace; select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize; -- run the following command to check all undo segment online in the UNDO tablespace: select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize; -- delete the original UNDO tablespace; drop tablespace undotbs1 including contents; -- confirm whether the deletion is successful;