当日有个变更需求，需要提取数据到压测环境下，在impdp导入过程中发现. .

. . imported "RCS"."T_RCS_MER_DAY":"PMAX" 0 KB 0 rowsProcessing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEX一直卡着不动，根据经验应该是该表有索引表空间，应该是索引表空间，或者临时表空间不足导致hang住不动。去alert日志检查一下；Mon Jun 04 15:47:31 2018statement in resumable session 'SYS.SYS_IMPORT_FULL_01.1' was suspended due toORA-01652: unable to extend temp segment by 128 in tablespace RCS_IDX果不其然，确实有警告，该进程应该是停止了。我们先将索引表空间resize一下Mon Jun 04 16:06:14 2018ALTER DATABASE DATAFILE '/u01/yace/datafile/yace/rcs_idx_01.dbf' RESIZE 30GMon Jun 04 16:06:26 2018Completed: ALTER DATABASE DATAFILE '/u01/yace/datafile/yace/rcs_idx_01.dbf' RESIZE 30GMon Jun 04 16:06:26 2018statement in resumable session 'SYS.SYS_IMPORT_FULL_01.1' was resumed随后就有任务恢复的提示，接下来导入工作完成Processing object type TABLE_EXPORT/TABLE/COMMENTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "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_FBOLinux, x64, 64bit (optimized), Oracle 11g on Jul 21 2017 20:31:38Operating 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 98477Log Read Checkpoint File /home/oracle/ogg/ggs12/dirdat/t10000000402018-06-04 16:37:32.000442 RBA 84950052

用下面语句进行查看是什么语句占用了temp表空间

SQL> set lines 1000 pages 200SQL> set long 999999999SQL> 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一下。