When importing a large number of data containing sub-partitions at 11 GB, The tablespace is insufficient.

Problem description:

Import using impdp Data Pump at ORACLE11g:

ORA-01691: Lob segment ISCS. SYS_LOB0000100750C00045 $ unable to scale through 128 (in tablespace RT_DATA)

There may also be insufficient TEMP and UNDO tablespaces:

ORA-30036: unable to expand segments by 8 (in restoring tablespace 'undotbs1)

ORA-01652: unable to expand segments by 8 (in restoring tablespace 'temp)

Solution:

View the section usage in the RT_DATA tablespace:

Select *
From (select owner,
Segment_name,
Tablespace_name,
Segment_type,
Bytes/1024/1024 AS "SIZE (M )"
From dba_segments
Order by 5 desc)
Where TABLESPACE_NAME = 'rt _ data'
AND rownum <20;

 

The LOBSEGMENT system naming rules are as follows:

SYS_LOB (10 digit object_id) C (5 digit col #) $ table segment
SYS_IL (10 digit object_id) C (5 digit col #) $ Index

Users who want to view the segment space:

Select * from dba_objects where object_id = '201312 ';

 

The impdp command generated in the default tablespace RT_DATA in the sys_import_table_01 table with the field xml_clob as a variable field and occupies a large amount of space. The following are 11 records.

Select object_name,
Object_schema,
Partition_name,
Subpartition_name,
Object_tablespace,
Xml_clob,
Round (dbms_lob.getlength (xml_clob)/1024) as "XML_CLOB SIZE (K )"
From SYS_IMPORT_TABLE_01
Where rownum <12;

 

Compared with sys_import_table_01 of 10 Gb, 11g adds the sub-partition description. From this table, the description is changed from 82 columns to 95 columns, and the records of sub-partitions in this table are 11 GB, as a result, the xml_clob column occupies a large amount of space. As a result, each piece of information in the subpartition occupies a full table space by default.

The reason is unknown, and there is very little information on the Internet. Please give your advice. However, you can manually expand or automatically expand the default tablespace, and then contract the default tablespace after the import.

 

Method 1 (manual extension ):

Alter database datafile '/data1/oradata/i1000/rt_data01.dbf' resize 10G;

After impdp:

Alter database datafile '/data1/oradata/i1000/rt_data01.dbf' resize 2G;

 

Method 2 (Auto scaling ):

Alter database datafile '/data1/oradata/i1000/rt_data01.dbf' autoextend on;

After impdp:

Alter database datafile '/data1/oradata/i1000/rt_data01.dbf' resize 2G autoextend off;

If the temp and undo tablespaces are insufficient, they are also extended.