Oracle impdp导入数据临时表空间与undo表空间爆满解决实例
| [日期：2018-01-24] | 来源：Linux社区 作者：rangle | [字体： ] |
针对Oracle数据迁移，我们可能会用到expdp/impdp的方式，有时候需要大表、lob字段等可能会消耗过大的临时表空间和undo表空间，所以一般我们根据导出日志，在导入前适当调整表空间大小。否则我们可能会遇到以下问题：
1、临时表空间爆满，无法扩展
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
解决方案如下：
（1）临时表空间使用情况查看：
col file_name for a85;
set line 200;
select file_name, BYTES/1024/1024/1024 Size_GB, MAXBYTES/1024/1024/1024 Msize_GB, AUTOEXTENSIBLE from dba_temp_files;
（2）使用临时段session查看：
SELECT se.username,
se.sid,
se.serial#,
se.machine,
se.program,
su.segtype,
su.contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr ;
USERNAME SID SERIAL# MACHINE PROGRAM SEGTYPE CONTENTS
--------------- ---------- ---------- -------------------- ----------------------------------- --------- ---------
impdpuser 5 28002 test.localhost.com oracle@test.localhost.com (DW00) LOB_DATA TEMPORARY
impdpuser 5 28002 test.localhost.com oracle@test.localhost.com (DW00) SORT TEMPORARY
impdpuser 5 28002 test.localhost.com oracle@test.localhost.com (DW00) SORT TEMPORARY
impdpuser 5 28002 test.localhost.com oracle@test.localhost.com (DW00) SORT TEMPORARY
（3）增加临时表空间数据文件：
alter tablespace temp add TEMPFILE '/home/U01/app/oracle/oradata/testdb/temp01.dbf ' size 50M autoextend on next 50M;2、查看impdp进度情况
（1）登陆交互模式 impdp impdpuser/impdpuser@service_name directory=datadump_dir dumpfile=impdp.dmp logfile=impdp.log attach=SYS_IMPORT_FULL_01 使用status查看进度信息 Import> status Job: SYS_IMPORT_FULL_01 Operation: IMPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /home/oracle/data.dmp Worker 1 Status: Instance ID: 1 Instance name: testdb Host name: test.localhost.com Process Name: DW00 State: EXECUTING Object Schema: USERNAME01 Object Name: TABLE_T1 Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Total Objects: 34 Completed Rows: 816,282,438 Completed Bytes: 64,412,720,616 Percent Done: 99 Worker Parallelism: 1 (2)数据字典查看 select * from dba_datapump_jobs ;
3、undo表空间爆满
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
解决方案：
（1）查看undo表空间使用情况 col file_name for a85; set line 200; select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%'; SELECT UPPER(F.TABLESPACE_NAME) AS "TABLESPACE_NAME", ROUND(D.MAX_BYTES,2) AS "TBS_TOTAL_SIZE" , ROUND(D.AVAILB_BYTES ,2) AS "TABLESPACE_SIZE", ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2) AS "TBS_AVABLE_SIZE", ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "TBS_USED_SIZE", TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,2),'999.99') AS "USED_RATE(%)", ROUND(F.USED_BYTES, 6) AS "FREE_SIZE(G)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES, ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTES FROM DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME AND D.TABLESPACE_NAME=&UNDO_TABLESPACE_NAME ORDER BY 5 DESC; （2）查看undo段使用情况 select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize; （3）查看归档日志切换情况 SELECT to_char(trunc(first_time),'DD-Mon-YY') "Date", to_char(first_time, 'Dy') "Day", '|' separator, count(1) Total, '|' separator, SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0", SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1", SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2", SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3", SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4", SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5", SUM(decode(to_char(first_time, 'hh24'),'06',