Pages

Sunday, January 26, 2014

impdp. SYSTEM Tablespace running out of space after several impdb runs

After running several times impdp, the next subsequent execution fails with error: 

"unable to extend temp segment by 128 in tablespace SYSTEM"

WTF???!!!...

It is important to note that the the impdp process was run under SYSTEM Oracle account.
Since it was run with REMAP_SCHEMA option.
And all of the executions have failed, due to discrepancy in the schema definition between "from user" and "to user" . 

The actual cause of the failure is not important. 
It seems that Oracle was keeping data from impdp failed runs, and now the SYSTEM tablespace is full...

SELECT tablespace_name, ROUND(SUM(bytes/1024/1024)) AS Mb
FROM DBA_FREE_SPACE 
WHERE TABLESPACE_NAME = 'SYSTEM'
GROUP BY tablespace_name


TABLESPACE_NAME MB
SYSTEM         0


SELECT bytes/1024/1024 AS Mb, SEGMENT_NAME, SEGMENT_TYPE 
FROM USER_SEGMENTS
WHERE tablespace_name = 'SYSTEM'
  AND bytes/1024/1024 > 10
  AND segment_name LIKE 'SYS_LOB%'
ORDER BY 1 DESC;

There are many SYS_LOB99999999$$ Objects, each occupying ~32 Mb, 
and many SYS_IMPORT_FULL_99 Tables, each occupying 2 Mb

MB SEGMENT_NAME                 SEGMENT_TYPE
29 SYS_LOB0000207647C00044$$ LOBSEGMENT
29 SYS_LOB0000203413C00044$$ LOBSEGMENT
29 SYS_LOB0000199272C00044$$ LOBSEGMENT
29 SYS_LOB0000195131C00044$$ LOBSEGMENT
and
4 SYS_IMPORT_FULL_15         TABLE
4 SYS_IMPORT_FULL_16         TABLE
4 SYS_IMPORT_FULL_17         TABLE
4 SYS_IMPORT_FULL_10         TABLE

In total, there are 18 SYS_LOB9999999999$$ Lobsegments, and 18 SYS_IMPORT_FULL_99 Tables

Thus 18 runs * 34 Mb ~ 650 Mb.
This indeed would explain why the SYSTEM Tablespace has run out of space.

Now need to check, to which Object these CLOBs are related.

SELECT * 
FROM USER_LOBS
WHERE tablespace_name = 'SYSTEM' 
  AND TABLE_NAME LIKE 'SYS_IMPORT_FULL%'

As expected, these are the very same Segment that clog up the USER_SEGAMNTS Table

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME
SYS_IMPORT_FULL_01 XML_CLOB SYS_LOB0000143499C00044$$ SYSTEM
SYS_IMPORT_FULL_02 XML_CLOB SYS_LOB0000147737C00044$$ SYSTEM
SYS_IMPORT_FULL_03 XML_CLOB SYS_LOB0000153721C00044$$ SYSTEM
SYS_IMPORT_FULL_04 XML_CLOB SYS_LOB0000157862C00044$$ SYSTEM
SYS_IMPORT_FULL_05 XML_CLOB SYS_LOB0000162003C00044$$ SYSTEM

In total 18 entries.

To make sure, query again the USER_SEGMENTS

SELECT bytes/1024/1024 AS Mb, USER_SEGMENTS.* 
FROM USER_SEGMENTS 
WHERE tablespace_name = 'SYSTEM'
  AND segment_name IN
   (SELECT SEGMENT_NAME 
      FROM USER_LOBS 
     WHERE tablespace_name = 'SYSTEM' 
       AND TABLE_NAME like 'SYS_IMPORT_FULL%')
ORDER BY 1 DESC

Indeed, the CLOB objects are the ones related to SYS_IMPORT TABLES

Now, all need to do, is to drop the SYS_IMPORT% tables.

SELECT 'DROP TABLE ' ||SEGMENT_NAME ||';' 
FROM USER_SEGMENTS 
WHERE tablespace_name = 'SYSTEM'
  AND SEGMENT_NAME like 'SYS_IMPORT_FULL%'
  AND SEGMENT_type = 'TABLE'
ORDER BY 1 ASC;


DROP TABLE SYS_IMPORT_FULL_01;
DROP TABLE SYS_IMPORT_FULL_02;
DROP TABLE SYS_IMPORT_FULL_03;
DROP TABLE SYS_IMPORT_FULL_04;
DROP TABLE SYS_IMPORT_FULL_05;
DROP TABLE SYS_IMPORT_FULL_06;
DROP TABLE SYS_IMPORT_FULL_07;
DROP TABLE SYS_IMPORT_FULL_08;
DROP TABLE SYS_IMPORT_FULL_09;
DROP TABLE SYS_IMPORT_FULL_10;
DROP TABLE SYS_IMPORT_FULL_11;
DROP TABLE SYS_IMPORT_FULL_12;
DROP TABLE SYS_IMPORT_FULL_13;
DROP TABLE SYS_IMPORT_FULL_14;
DROP TABLE SYS_IMPORT_FULL_15;
DROP TABLE SYS_IMPORT_FULL_16;
DROP TABLE SYS_IMPORT_FULL_17;
DROP TABLE SYS_IMPORT_FULL_18;

Execute the generated statements.
Check again for space under SYSTEM Tablespace.

SELECT tablespace_name, ROUND(SUM(bytes/1024/1024)) AS Mb 
FROM DBA_FREE_SPACE 
WHERE TABLESPACE_NAME = 'SYSTEM'
GROUP BY tablespace_name


TABLESPACE_NAME MB
SYSTEM         644


Aurika!!!

No comments:

Post a Comment