Problem solve Get help with specific problems with your technologies, process and projects.


We are badly stuck with an import problem in st_txn_trlr. The import aborts saying 'maximum number of extents reached', and then rolling back some 2900000 rows out of a total of 3500000 of rows. We have all user tablespaces offline except the default tablespace which has about 400 MB space. The 'oerr' shows that it is necessary to increase a combination of initial extent, nextxtent, pctincrease etc. We have already tried different combinations - starting from initial = 256 K, next = 256 k, pctincrease = 0 upto initial = 4m, next = 4m and pctincrease = 50%. But the same error message is coming. We had been trying this for the last 5hrs., but in vain. Please extend guidance, if it is not absolutely impossible. The question is what is the right combination? And how do you determine that?

The st_txn_trlr table structure is as follows:

storeid varchar2(2) not null,
txn_typ varchar2(3) not null,
txn_yy varchar2(2) not null,
txn# number(6,0) not null,
line# number(3,0),
item_cd varchar2(7),
conv_factr_txn number(7,3),
qty_txn number(7,2),
measr_unit_txn varchar2(4),
flg_batch#_req varchar2(1),
tot_qty_ret number(7,2),
rate number(10,2),
value number(10,2))

The Oracle error messages we came across are ORA 1631 followed by ORA1542.

You said you have been changing your initial, next and pctincrease storage parameters but what about the MAXEXTENTS parameter. This is what your error message is telling you. It's saying that the limit you placed on MAXEXTENTS has been reached. So, no matter how much you change those other settings, it still can't extend. Try setting the MAXEXTENTS parameter up in proportion.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.