Solved

Oracle 11g  Import job impdp

Posted on 2014-03-21
4
1,090 Views
Last Modified: 2014-03-24
Hi,

I got the below error while importing the data for few tables using impdp in Oracle 11g.

"ORA-39171: Job is experiencing a resumable wait."

then I added data files with autoextend on and maxsize as unlimited and re-started the import job.

Can you please let me know if my job impot the tables for which I got the error like above?

If not, should I manully import those tables after the complete import and my import job is completed?

Also, before the error I added the datafile with autoextend on but did not give "maxsize unlimited". Is that the reason for the error?
For my case it extend upto 31 gb but still lot of space is available in the drive. But did not get extended.

If I do not give maxsize for autoextend on, upto how much size it will extend(default size) ?

Even after I gave maxsize unlimited(second time when i add datafile) I got the same error after reaching 31.9 GB. Still there is lot of space in the hard disk.

Can you please let me know why I am getting this error.

Anyway I added more datafiles and continuing the job.


Thanks.
0
Comment
Question by:GouthamAnand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39944887
Maybe this link will help you, whereas I suppose you're already doing what they propose ;-)

http://dbtricks.com/?p=62
0
 

Author Comment

by:GouthamAnand
ID: 39944902
Yes. Thank you. But my question is not how to resolve the error. As it was explained in the website. But Want to know the root cause of the error ie even after giving the autoextend on (with or without maxsize) got the error?

Also. another question is what about for the tables for which already got the error "ORA-39171: Job is experiencing a resumable wait."

does it retry for those tables also?

Thanks.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39948875
"Autoextend" in Oracle still has limits.  An Oracle datafile can only "autoextend' up to the maximum files size that Oracle supports.  This varies by the block size that you choose, and it is usally *MUCH* smaller than the size of physical disks these days.  For example, if you use the default Oracle block size of 8k, the maximum size for a single datafile is 32Gb.  For data warehouses, with a blocksize of 16k or 32k, your maximum datafile sizes will be 64Gb or 128Gb respectively.  And, if you use a block size of 2k or 4k, your maximum file size will be smaller than 32Gb (probably 8Gb and 16Gb).

You may create multiple datafiles per tablespace.  Each of them can then expand up to the largest size that Oracle supports for the block size of that tablespace.
0
 

Author Closing Comment

by:GouthamAnand
ID: 39949879
Thank you.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question