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

ORA-30036:unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

The cause of the problem:

The imp error when importing a table in bulk is as follows:

Export file created by EXPORT:V11.02.00 via conventional path

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Import server uses WE8MSWIN1252 character set (possible charset conversion)

.

IMP-00058: ORACLE error 30036 encountered

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

IMP-00028: partial import of previous table rolled back: 814416 rows rolled back

IMP-00057: Warning: Dump file may not contain data of all partitions of this table

Import terminated successfully with warnings.

The literal translation means: cannot expand the field by 8

First, take a look at the role of UNDO:

1. What is a restore tablespace?

Restore table space, or Undo table space, is a unique concept of Oracle. Undo table space automatically allocates Undo segments to hold Undo data of DML (Insert, Update, or Delete) statements in a transaction. Prior to Oracle9i, you could only use Rollback Segment to manage Undo data. Starting with Oracle9i, managing Undo data can use not only rollback segments, but also Undo tablespaces. Due to the complexity of planning rollback segments in management, Oracle10g has abandoned the rollback segments and only uses Undo tablespaces to manage Undo data.

two。 What is Undo data?

Undo data is also called rollback data. When the DML statement is executed, the data during the transaction operation is called Undo data, which has two main functions:

1. Ensure transaction consistency: if there is an error in the transaction or the user wants to cancel the database operation, you can go back to the pre-modified value through Rollback.

2. Provide consistent reading: if table T has 100 records, user An executes statements in Table T to delete 10 records and has not yet submitted them. If user B executes the query statement, 100 records will be returned instead of 90.

Let's talk about the solution:

1. Increase the extended capacity in the original data file:

Alter database datafile'/ opt/oracle/oradata/orcl/undotbs01.dbf' resize 30g

(note: if the original data file size is 30G, this method cannot be used, because the oralce maximum file is only configured as 30G data file)

two。 New data file (generic):

Alter tablespace UNDOTBS1 add datafile'/ opt/oracle/oradata/orcl/undotbs01_01.dbf' size 30g

-- autoextend on next 1m maxsize 5000m; / / optional; the purpose is to set the automatic extension properties of the file, from a minimum of 1m to a maximum of 5000m

3. If there is a need to create multiple undotbs tablespaces, you can check the undo tablespaces regularly and switch:

Alter system set undo_tablespace = undotbs02

(note that undotbs02 needs to be created in advance, and the corresponding data file size can be set)

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.