手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

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',1,0)) "h6",
        SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7",
        SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8",
        SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9",
        SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10",
        SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11",
        SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12",
        SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13",
        SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14",
        SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15",
        SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16",
        SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17",
        SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18",
        SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19",
        SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20",
        SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21",
        SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22",
        SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23"
 from v$log_history
-- where standby_dest = 'NO'
-- and   CREATOR IN ('ARCH' , 'FGRD')
group by trunc(first_time), to_char(first_time, 'Dy')
order by trunc(first_time);
--logswitchfreq.sql 
4)查看相关SQL
set line 200;
set pagesize 20000;
col sql_text for a45;
col   elapsed_rank for 999;

select *
from (select v.sql_id,
 v.sql_text,
v.elapsed_time/1000000 elapsed_time ,
v.cpu_time/1000000 cpu_time,
v.disk_reads,
v.executions,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sqlarea v) a
where elapsed_rank <= 10;

(4)增加表空间数据文件
alter  undo tablespace undotbs1 add datafile '/home/U01/oracle/oradata/test/UNDOTBS1a.dbf' size 100m reuse autoextend on next 100M;
(5)改变默认undo表空间
create undo tablespace undotbs2 datafile '/home/U01/oracle/oradata/test/UNDOTBS2.dbf' size 100m reuse autoextend on next 100m;
alter system set undo_tablespace=undotbs2 scope=both;  

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2018-01/150535.htm

linux
相关资讯       undo表空间  impdp导入数据 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款