Shulou(Shulou.com)06/01 Report--

Reduce the size of UNDOTBS01.DBF files in the Oracle directory

Log in to Oracle using the sys user

Method 1: reset the tablespace size

Execute under linux

ALTER DATABASE DATAFILE'/ opt/oracle/oradata/res/undotbs01.dbf' RESIZE 100m

Execute ALTER DATABASE DATAFILE'E:\ ORACLE\ ORADATA\ UNDOTBS01.DBF' RESIZE 100m under windows

Where'/ opt/oracle/oradata/res/undotbs01.dbf' is the storage path of the UNDOTBS01.DBF file on your system; 100m is the reset size, which can be modified as needed.

If method one cannot be executed, use method two

Method 2: create a new UNDO table space and replace the original UNDO table space

1. Create a new small undo tablespace

Execute under linux:

Create undo tablespace undotbs2 datafile'/ opt/oracle/oradata/res/undotbs02.dbf' size 100m reuse autoextend on;//'/opt/oracle/oradata/res/undotbs02.dbf' is the location where the tablespace file wants to be stored, and 100m represents the initial size of the new table space and automatically grows. Or create undo tablespace undotbs2 datafile'/ u01Accord size undotbs02.dbf' 100M reuse autoextend on next 5m maxsize 1024M is the location where tablespace files want to be stored. 100m represents the initial size of the new table space, increasing the maximum to 1G by 5m.

Execute under windows:

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE'e:\ oradata\ UNDOTBS02.DBF' SIZE 100m REUSE AUTOEXTEND ON

two。 Set the new table space to the system's Undo table space

-- (1) dynamically change the spfile configuration file and set the new table space to the system undo_tablespace

Alter system set undo_tablespace=undotbs2

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2

-- (2) verify the undo table space of the database again (determine the UNDO table space being used by the current routine)

Show parameter undo_tablespace

-- (3) wait for the original UNDO table space UNDOTBS1 is OFFLINE

SELECT r.status "Status", r.segment_name "Name", r.tablespace_name "Tablespace", s.extents "Extents", TO_CHAR ((s.bytes/1024/1024), '99999990.000') "Size" FROM sys.dba_rollback_segs r, sys.dba_segments 's WHERE r.segment_name = s.segment_name AND s.segment_type IN (' ROLLBACK' 'TYPE2 UNDO') and r. Tablespaceful nameplate UNDOTBS1' and status='ONLINE' If there is an object with status online on it, you can query the sid,serial# of the specific object

-- (4) see what is currently using this rollback segment

SELECT r.NAME force s.sidjimol s.serial# Serial,s.username, s.machine, t.startbooktimestampt.status, t.used_ublk, substr (s.program, 1,15) "operate" FROM v$session s, v$transaction t, v$rollname rMagneVince rollstat g WHERE t.addr = r.usn AND r.usn = g.usn ORDER BY t.used_ublk desc;-- for example: the object is: sid 474 camera serial 6794

-- (5) find out the specific sql according to sid

Select sql_text from v$session a journal sqltextbook withdrawing newlines b where DECODE (a.sql_hash_value, 0, prev_hash_value, sql_hash_value) = b.hash_value and a.sid=&sid order by piece

If the sql is not important, you can kill the session directly.

-(6) kill session

Alter system kill session '474pr 6794'

-- (7) Delete the old Undo tablespace

Still log in using the sys user, execute

Drop tablespace undotbs1 including contents and datafiles; (drop tablespace undotbs1 including contents;, which only deletes the table space name, does not delete the data file)

-- (8) confirm whether the deletion is successful

Select name from v$tablespace

-- (9) determine whether the content of $ORACLE_HOME/dbs/spfileoinms.ora has changed:

$more spfileoinms.ora

* .undo_management='AUTO'

* .undo_retention=10800

* .undo_tablespace='UNDOTBS2'

-- (10) if there is no change, execute the following statement:

SQL > create pfile from spfile

File created.

-- (11) Delete the data file of the original UNDO tablespace, whose file name is the result executed in the step.

# rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf

Although the data file for the undo tablespace corresponding to the system has been deleted, the system space cannot be freed when viewed with df-h.

This is mainly due to the fact that a process in Oracle is accessing the file. The process can be accessed by kill Oracle, or the system space can be freed after restarting the database. )

3. Restart the database

You can use the sys user to log in from sqlplus, execute the startup force command, and force a restart

If you feel it is not safe to force a restart, you can start the database startup by shutdown immediate first.

At this point, the UNDOTBS01.DBF file has shrunk, and if you want to restore the original tablespace name, re-execute method 2.

4. Switch back to UNTOTBS1 tablespace

-- (1) newly created UNDOTBS1 tablespace

Create undo tablespace UNDOTBS1

Datafile'/ oracle/oradata/undo/undotbs01.dbf'

Size 100M autoextend on

-- (2) switch back to UNTOTBS1 and dynamically change the spfile configuration file

Alter system set undo_tablespace=UNDOTBS1 scope=both

-- (3) verify the undo tablespace of the database again

Show parameter undo_tablespace

-- (4) wait for UNDO tablespace UNDOTBS2 is OFFLINE

SELECT r.status "Status", r.segment_name "Name", r.tablespace_name "Tablespace", s.extents "Extents", TO_CHAR ((s.bytes/1024/1024), '99999990.000') "Size" FROM sys.dba_rollback_segs r, sys.dba_segments 's WHERE r.segment_name = s.segment_name AND s.segment_type IN (' ROLLBACK', 'TYPE2 UNDO') and r.tablespaceful nameplates UNDOTBS2' ORDER BY 5 DESC

-- (5) delete

Drop tablespace UNDOTBS2 including contents and datafiles

-- (6) confirm whether the deletion is successful

Select name from v$tablespace

Method 3: prohibit automatic growth of undo tablespace

Alter database datafile'/ u01 autoextend off;alter database datafile autoextend off;alter database datafile:\ app\ Administrator\ oradata\ undotbs01.dbf' autoextend off

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.