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

Confirm the UNDO tablespace name

Select name from v$tablespace

Check the database UNDO table space occupancy and data file storage location

Select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1'

UNDO table space is not enough, there are two ways to deal with it: 1, expand the size of the table space; 2, create a new UNDO table space and delete the original

1. Expand the UNDO table space

Alter database UNDOTBS1 datafile'/ opt/oracle/oradata/inms/undotbs02.dbf' resize 4000m

Create a new UNDO tablespace and delete the original

1. Create a new UNDO tablespace and set automatic extension parameters

Create undo tablespace undotbs2 datafile'/ oradata/oradata/ddptest/UNDOTBS1.dbf' size 2 1000m reuse autoextend on next 800m maxsize unlimited

2. Dynamically change the spfile configuration file

Alter system set undo_tablespace=undotbs2 scope=both

3. Delete the original UNDO tablespace

Drop tablespace undotbs1 including contents

4. Confirm whether the deletion is successful

Select name from v$tablespace

5. Determine whether the content of $ORACLE_HOME/dbs/spfileoinms.ora has changed:

$more spfileoinms.ora

* .undo_management='AUTO'

* .undo_retention=10800

* .undo_tablespace='UNDOTBS2'

If no changes have occurred, execute the following statement:

SQL > create pfile from spfile

File created.

6. 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

How to deal with the large data file corresponding to the UNDO tablespace of Oracle

[date: 2011-04-18]

Source: Linux Community author: Linux

Google_protectAndRun ("render_ads.js::google_render_ad", google_handleError, google_render_ad); 1 View the tablespace size and maximum value of undo

Select t.file_name,t.tablespace_name

T.bytes/1024/1024/1024 "GB", t.maxbytes/1024/1024/1024 "Max GB"

From dba_data_files t where t.tablespaceful nameplate UNDOTBS1'

The data file is: / oracle/oradata/undo/undotbs01.dbf

2 create a new undo tablespace to replace the original undo tablespace

Create undo tablespace UNDOTBS2

Datafile'/ oracle/oradata/log/undotbs02.dbf'

Size 10M autoextend on maxsize unlimited

3 set the new undo table space to the undo table space of the database

Alter system set undo_tablespace=UNDOTBS2 scope=both

4 verify the undo tablespace of the database again

Show parameter undo_tablespace

5 wait for the original UNDO tablespace 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

5.1 see what is currently using this rollback segment

SELECT r.NAME,s.sid,s.serial# Serial

S.username, s.machine

T.start_time,t.status

T.used_ublk

Substr (s.program, 1,15) "operate"

FROM v$session s, v$transaction t, v$rollname rjime vault rollstat g

WHERE t.addr = s.taddr

AND t.xidusn = r.usn

AND r.usn = g.usn

ORDER BY t.used_ublk desc

For example, the object is: sid 474 camera serial 6794

5.2 find out the specific sql according to sid

Select sql_text from v$session a minute vandalism 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.

5.3 kill session

Alter system kill session '474pr 6794'

5.4 deleting the data of the original undo tablespace and its system

Drop tablespace UNDOTBS1 including contents and datafiles

(in an AIX system, although the data file for the corresponding undo tablespace of the system has been deleted, the system space cannot be freed when viewed with df-g.

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. )

6 newly established UNDOTBS1 tablespace

Create undo tablespace UNDOTBS1

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

Size 10M autoextend on maxsize 12G

7 switch back to UNTOTBS1

Alter system set undo_tablespace=UNDOTBS1 scope=both

8 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 nameplate UNDOTBS2'

ORDER BY 5 DESC

9 Delete

Drop tablespace UNDOTBS2 including contents and datafiles

FROM: http://blog.chinaunix.net/uid-57485-id-3171219.html

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.