Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Tablespace limit exceeded on importing data pump, Tablespace limit exceeded on importing data pump
rj678
post Aug 10 2017, 10:52 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 10-August 17
Member No.: 52,113



Came across a situation I’ve never experienced importing data into Oracle.

Was given a data pump file that has its own tablespace (TS) defined. I defined the TS with this info for it I was given: size 2G autoextend on maxsize 8G uniform size 1G.
They stated the current TS size is only 4GBs so more than enough space in new TS. However on import the datafile grows and blows out 8GB size and goes into a Resumable state on extents being exceeded.
So I stopped the import and cleared TS and reset the TS adding another datafile and tried import again…this time both files bloated to limit and import again stopped to a resumable state.
I then added 2 more datafiles and repeated with same results of exceeding TS limit. I've also checked the TEMP TS and plenty of space there too.

What would be causing this issue not being able to import?

TIA!

rj...
Go to the top of the page
 
+Quote Post
burleson
post Aug 10 2017, 06:45 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,573
Joined: 26-January 04
Member No.: 13



Hi RJ, and welcome to the forum!

You have two issues here. The first is allowing Oracle to extend enough to accept your load.

The second issue is setting PCTFREE to pack the data as tightly as possible.

For the tablespace, allocate it with "maxextents unlimited" and auto extend on:

http://www.dba-oracle.com/t_alter_table_in...s_unlimited.htm

As for row packing, first see if PCTFREE is set for you table Parms:

http://www.dba-oracle.com/t_storage_parameter_exceptions.htm

Next, determine avg_row_len and set the storage parms to pack in the data without too much free space:

http://www.dba-oracle.com/art_pctfree.htm

Good luck!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th October 2017 - 04:20 PM