Data Pump Import job got stuck for space quota..

The import job got stuck and continuously alerting the below two error. 

ORA-39171: Job is experiencing a resumable wait.

ORA-01536: space quota exceeded for tablespace 'SPDEFAULT'

As per oracle the above errors information is like below.

----------------------------------------------------------------------------------------------------------------------------------------

ORA- 39171:  Job is experiencing a resumable wait.

Cause: The Data Pump job is stalled with one or more of its sessions having a resumable wait. Resumable waits are typically caused by a non-expandable tablespace running out of space. The follow-on message describes the nature of the wait.

Action: Correct the condition causing the wait. This will typically involve adding datafiles to the tablespace that is full

ORA-01536: space quota exceeded for tablespace "string"

Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.

Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner.

----------------------------------------------------------------------------------------------------------------------------------------

However the both alerts were not directing me the proper fix to my problem because though i wanted to grant some quota for a owner/schema which is unknown in this here and there are a lot of users getting imported. 

Then i thought to dig it further, so i looked into database to know what exactly is happening at this moment. Actually a user called  'SP'  is waiting to create an object in SPDEFAULT tablespace and I found this user does not have any quota on SPDEFAULT tablespace. 

set pages 50000 lines 32767

col "Last SQL" format a1000

SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL",t.module

FROM gv$session s, gv$sqlarea t

WHERE s.sql_address =t.address AND

s.sql_hash_value =t.hash_value;

   INST_ID USERNAME                              SID    SERIAL# SQL_ID        Last SQL

---------- ------------------------------ ---------- ---------- ------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

         1 EXP$ORACLE                             44        741 bjnvhuy0bayab CREATE TABLE "SP"."SPDBOWNER" ("DBNAME" VARCHAR2(8 BYTE) NOT NULL ENABLE, "OWNERID" VARCHAR2(8 BYTE) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SPDEFAULT"

Finally i got the schema to whome the quota is needed, after granting quota here we go, the import progressed.

ORA-39171: Job is experiencing a resumable wait.

ORA-01536: space quota exceeded for tablespace 'SPDEFAULT'

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SYSDMPS"."SP_GLSI_ADEX_DTL"                25.89 GB 102216301 rows

NFS locking issue while data pump export - Linux-x86_64 Error: 37: No locks available

Yesterday, as usual the cron job triggered a datapump export job against a database on a Linux Server.
Immediately post running the export job it got failed. When i look into the dump logfile i found below sort of errors.

Export: Release 11.2.0.3.0 - Production on Sat Mar 8 05:53:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/oraexp/NTLSNDB/Data_Pump_Export_NTLSNDB_FULL_030814_0553_01.dmp"
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10

I verified at database level whether the dump directory, its path and the proper read & write privileges are granted on the directory. Yes everything was fine at database end.

I believed this could be an issue of nfs mount option at OS level. We are using an NFS shared mount point for all of the servers it needs to get mounted with proper options on each server to get it used by the database. I could see this mount point is mounted properly with recommended options by Oracle.

Then i checked the logs at OS level, then i found the issue is related to nfslock services. The nfslock service is not running on this database.  this service helps the client to lock a file in the related NFS mount point on the server to create a file and make write operations.

>cat messages | grep lockd
Mar  8 04:03:31 demoserver kernel: lockd: cannot monitor 10.207.80.179
Mar  8 04:03:31 demoserver kernel: lockd: failed to monitor 10.207.80.179
Mar  8 04:20:27 demoserver kernel: lockd: cannot monitor 10.207.80.179
Mar  8 04:20:27 demoserver kernel: lockd: failed to monitor 10.207.80.179

Further i came to know that t the server got rebooted couple of days ago for a reason, after reboot the nfslock services did not startup automatically. So manually we started the services. Note that  If the nfslock services need to get auto start after a reboot then we need to use chkconfig nfslock on. Later the same has been taken care. hence onwards whenever the server gets rebooted the nfslock services will automatically startup.

cat messages | grep rpc
Mar  8 07:01:43 demoserver rpc.statd[12667]: Version 1.0.9 Starting
Mar  8 07:01:49 demoserver rpc.statd[12667]: Caught signal 15, un-registering and exiting.
Mar  8 07:01:49 demoserver rpc.statd[12745]: Version 1.0.9 Starting

You can manage the nfslock services by below commands.

service nfslock status
service nfslock start
service nfslock stop

After making sure that the services got started and the client could able to lock the file on the NFS file system on the server. we re-triggered the export job. It executed successfully.