max extents reached

I am receiving the following message ORA-01631 . I have increased the max extents to unlimited and performed percentage increases on the tablespace. I am trying to import data into a particular table not from a Oracle dump file but from another database. Is there anything I can check or modify further in Oracle as I am still receiving the max extents reached message when data is imported.

ORA-01631 max # extents (string) reached in table string.string

Cause: A table tried to extend past MAXEXTENTS.

Action: If MAXEXTENTS is less than the system maximum, raise it. Otherwise, you must re-create with larger initial, next or PCTINCREASE parameters.

Are you sure this is not for the table index?
I have seen that before.
I would not touch the pct_increase for the table, IMO, you are asking for trouble.

MH

You might also check to see if there is an insert trigger that is also inserting data into another table.

Quote:

---

Originally posted by gandolf989
You might also check to see if there is an insert trigger that is also inserting data into another table.

---

Very good call, I had that happen once!!
I hate when that happens!

MH

How about setting maxextents to unlimited?

Quote:

---

Originally posted by marist89
How about setting maxextents to unlimited?

---

How about reading sentence one, paragraph one.
Two steps back my arse!

MH

Appearantly it's not obvious, I meant set maxextents to unlimited for ALL OBJECTS.

How do you set max extents unlimited for all user tables and or objects

Quote:

---

Originally posted by bal
I have increased the max extents to unlimited and performed percentage increases on the tablespace.

---

Setting MAXEXTENTS to unlimited on the tablespace level has absolutely no effect on the existing segments (as well on the future segments that will have MAXEXTENTS specified sxplicitely). You'll have to set MAXEXTENTS on the table you are inserting to.

Quote:

---

Originally posted by bal
How do you set max extents unlimited for all user tables and or objects

---

SQL> SET HEADING OFF
SQL> SET PAGESIZE 1000
SQL> SPOOL MAX_EXTENTS_UNLIMITED.SQL

SQL> select 'alter table '||owner||'.'||table_name||' storage(maxextents unlimited);'
2 from dba_tables
3 where owner='TABLE_OWNER_YOU_WANT_TO_ALTER;

SQL> SPOOL OFF


Who do you love?

MH

Maxextents are not valid in the case of locally managed tablespaces and hence on the objects in the tablespace. So if you try it on objects belonging to LMT you will get


alter table rd_towns storage (maxextents unlimited)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

Quote:

---

Originally posted by adewri


alter table rd_towns storage (maxextents unlimited)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

---

Actaully, on LMT's the DEFAULT is ulimited, and you can set a segment's maxextents residing in a LMT, but it is ignored. I think I proved this a couple of weeks ago in another thread.

Quote:

---

Originally posted by grjohnson
Actaully, on LMT's the DEFAULT is ulimited, and you can set a segment's maxextents residing in a LMT, but it is ignored.I think I proved this a couple of weeks ago in another thread.

---

and now you got the reason also, why not to try to change the maxextents for LMT segments.

Quote:

---

Originally posted by adewri
and now you got the reason also, why not to try to change the maxextents for LMT segments.

---

Because it's ignored.