共计 5356 个字符，预计需要花费 14 分钟才能阅读完成。
针对 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
正文完
星哥玩云-微信公众号
