当日有个变更需求,需要提取数据到压测环境下,在impdp导入过程中发现. . . . imported "RCS"."T_RCS_MER_DAY":"PMAX" 0 KB 0 rows Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 一直卡着不动,根据经验应该是该表有索引表空间,应该是索引表空间,或者临时表空间不足导致hang住不动。去alert日志检查一下; Mon Jun 04 15:47:31 2018 statement in resumable session 'SYS.SYS_IMPORT_FULL_01.1' was suspended due to ORA-01652: unable to extend temp segment by 128 in tablespace RCS_IDX 果不其然,确实有警告,该进程应该是停止了。我们先将索引表空间resize一下 Mon Jun 04 16:06:14 2018 ALTER DATABASE DATAFILE '/u01/yace/datafile/yace/rcs_idx_01.dbf' RESIZE 30G Mon Jun 04 16:06:26 2018 Completed: ALTER DATABASE DATAFILE '/u01/yace/datafile/yace/rcs_idx_01.dbf' RESIZE 30G Mon Jun 04 16:06:26 2018 statement in resumable session 'SYS.SYS_IMPORT_FULL_01.1' was resumed 随后就有任务恢复的提示,接下来导入工作完成 Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Jun 4 16:09:10 2018 elapsed 0 00:23:05

有点搞笑最近和temp表空间杠上了哈,我们看看都有什么利用了temp表空间。 SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,su.segtype, su.CONTENTS from v$session se, v$sort_usage su WHERE se.saddr = su.session_addr; 是ogg用户占用,复制进程难道有延迟? [oracle@yace ggs12]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO Linux, x64, 64bit (optimized), Oracle 11g on Jul 21 2017 20:31:38 Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (yace) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING EXTZW 00:00:00 00:00:05
EXTRACT RUNNING PMPZW 00:00:00 00:00:05
REPLICAT RUNNING REPZW 00:00:00 00:00:04

GGSCI (yace) 2> info repzw

REPLICAT REPZW Last Started 2018-05-10 18:58 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:00 ago) Process ID 98477 Log Read Checkpoint File /home/oracle/ogg/ggs12/dirdat/t1000000040 2018-06-04 16:37:32.000442 RBA 84950052

用下面语句进行查看是什么语句占用了temp表空间 SQL> set lines 1000 pages 200 SQL> set long 999999999 SQL> Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid; UPDATE ogg.checkpointtable SET last_update_ts = sysdate, seqno = :seqno, rba = :rba, audit_ts = :audit_ts, log_bsn = :log_bsn, log_csn = :log_csn, log_xid = :log_xid, log_cmplt_csn = :log_cmplt_csn, log_cmplt_xids = :log_cmplt_xids, version = :version WHERE group_name = :group_name AND group_key = :key

至于怎么解决不知道了,总不能kill进程吧,Mark一下。