"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
Now, all need to do, is to drop the SYS_IMPORT% tables.
ORDER BY 1 DESC;
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'
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
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = 'SYSTEM'
GROUP BY tablespace_name
TABLESPACE_NAME MB
SYSTEM 644
Aurika!!!
No comments:
Post a Comment