impdp时不报错地hang住
import: release 11.2.0.2.0 - production on wed jan 22 12:27:27 2014copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.connected to: oracle database 11g enterprise edition release 11.2.0.2.0 - 64bit productionwith the partitioning, real application clusters, automatic storage management, olap,data mining and real application testing optionsmaster table sys.impdp_fwy successfully loaded/unloadedstarting sys.impdp_fwy: /******** as sysdba parfile=impdp.parprocessing object type table_export/table/tableprocessing object type table_export/table/table_data. . imported gbsmis.gm_account_main 20.37 gb 114580038 rows. . imported gbsmis.gm_claim_settled_backup 7.775 gb 24464956 rows. . imported gbsmis.gm_claim_liab_cal 7.287 gb 27262351 rows. . imported gbsmis.gm_claim_docu 7.030 gb 24851389 rows. . imported gbsmis.gm_pol_main_backup 3.896 gb 7287304 rows. . imported gbsmis.gm_pol_fee_table 2.791 gb 16147645 rows. . imported gbsmis.gm_annuity_achieve_intf_new 1.289 gb 16874511 rows. . imported gbsmis.gm_claim_scene_cal 881.9 mb 2457482 rows. . imported gbsmis.gm_manage_fee_sum 14.71 mb 291045 rows. . imported gbsmis.payment_company_card_table 13.74 mb 113421 rows. . imported gbsmis.gm_undwrttimes 5.596 mb 59175 rows. . imported gbsmis.gm_sync_icss_iprs_common 1012. kb 2352 rows. . imported gbsmis.gm_department_table 391.7 kb 4464 rows. . imported gbsmis.gm_department_table_new 391.7 kb 4464 rows. . imported gbsmis.gm_deptflag 10.13 kb 36 rows. . imported gbsmis.gm_three_department 318.2 kb 4424 rows. . imported gbsmis.gm_sync_icss_iprs_question 612.5 kb 9529 rows. . imported gbsmis.existing_transfer_client_prem 471.0 kb 4327 rows. . imported gbsmis.gm_sync_icss_iprs_business 194.8 kb 2352 rows. . imported gbsmis.existing_transfer_client 63.66 kb 1204 rows. . imported gbsmis.gm_liab_item_tbl 12.93 kb 86 rowsprocessing object type table_export/table/grant/owner_grant/object_grantprocessing object type table_export/table/index/index到processing object type table_export/table/index/index时hang住了，最大的表是20g，为其建索引虽慢，也用不着一晚都没完呀。网上搜索了一下，说impdp时开并行可能遇到了bug，好吧我绕过它，我把parallel=4删掉。重新发起，等了好几个小时后仍然在这里hang住了，看来不是parallel的问题。登陆数据库看情况。 从datapump的视图看来，导入是正常运行的。 sql> @datapump -- dba_datapump_sessions owner_name job_name inst_id saddr session_type------------------------------ ------------------------------ ---------- ---------------- --------------sys impdp_fwy 1 0000000728748330 dbms_datapumpsys impdp_fwy 1 000000072c7b0918 mastersys impdp_fwy 1 000000073c7fefc8 worker-- dba_datapump_jobs owner_name job_name operation job_mode state degree attached_sessions datapump_sessions------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ---------- ----------------- ----------------- sys impdp_fwy import full executing 1 1 3看下我导入的用户，sys用户，在等什么。 sql> @waitbyusr sys --v$session_wait displays the current or last wait for each session.--@sid 19,25,232 sid event 等待时间 state status wait_class----- ------------------------------ ------------------------------ ------------------- -------- ---------------------------------------------------------------- 721 wait for unread message on bro 已等1秒 waiting active idle adcast channel 993 wait for unread message on bro 已等1秒 waiting active idle adcast channel 网上搜了下这个等待，结合我的impdp，好像也没啥线索。 从后台attach这个job登陆 worker 1 status: process name: dw00 state: executing object schema: gbsmis object name: idx_gm_account_main_lcd object type: table_export/table/index/index completed objects: 1 worker parallelism: 1 显示的确是在建索引idx_gm_account_main_lcd，因为是ddl语句，没建完我也不能从数据库层面看他是否存在以及是否在增长。此时一切看来正常，难道我还得在等一晚上？ 看到process name是dw00，再登陆到数据库中。 sql> select * from v$process where program like '%dw00%'; addr pid spid pname username serial# terminal program traceid tracefile background latchwait latchspin pga_used_mem pga_alloc_mem pga_freeable_mem pga_max_mem---------------- ---------- ------------------------ ----- -------------------- ---------- ------------------------------ ------------------------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ---------------- ---------------- ------------ ------------- ---------------- -----------000000075c5aa808 63 16224 dw00 otzj11g 47 unknown oracle@cnsh281003 (dw00) /paic/stg/oracle/11g/app/oracle/diag/rdbms/molapstg/molapstg/trace/molapstg_dw00 1 65156820 1187400836 1120534528 1187400836 这个dw00并不是dbwr进程。 dbw0 是dbwr进程,写datafile用的. dw0是datapump worker进程,给 impdp/expdp用的.通过spid16224可以得到会话sid是1009 sql> @sidbyspid 16224 sid ----- 1009 sql> 看看1009会话在等什么。 sql> @waitbysid 1009 --v$session_wait displays the current or last wait for each session.--@sid 19,25,232 sid event 等待时间 state status wait_class----- ------------------------------ ------------------------------ ------------------- -------- ---------------------------------------------------------------- 1009 statement suspended, wait erro 已等0秒 waiting active configuration r to be cleared sql> statement suspended, wait erro to be cleared等待，此时百度一搜，惜分飞的文章映入眼帘，顿时春暖花开。是因为表空间不足，所以hang住了。此时看了下表空间，是足够的，是表空间所在的asm dg已经满了。所以申请存储扩diskgroup，搞定。