ORA-01631, and table is not full, is this an index problem? 
Author Message
 ORA-01631, and table is not full, is this an index problem?

I've got a newly installde data collection instnace (7.3.4.5) that's
usdenly returning ORA-01631 when data is atempted to be loged into some
tables.

But, the following query, which I picked up from the O'riley "Oracle
Preformance Tuning":

SELECT BLOCKS
    ALLOCATED_BLKS,
                COUNT(DISTINCT SUBSTR(T.ROWID,1,8) ||
                SUBSTR(T.ROWID,15,4)) USED, (COUNT(DISTINCT
                SUBSTR(T.ROWID,1,8) || SUBSTR(T.ROWID,15,4)) /
                BLOCKS) * 100 PCT_USED
        FROM
                SYS.DBA_SEGMENTS E,
                &&USER .&&TAB_NAME T
                WHERE
        E.SEGMENT_NAME = UPPER ('&&TAB_NAME')
                AND E.SEGMENT_TYPE = 'TABLE' GROUP BY
                E.BLOCKS ;

Says:

Enter value for user: flink
Enter value for tab_name: B410

ALLOCATED_BLKS       USED   PCT_USED
-------------- ---------- ----------
         17410      17219 98.9029294

And since the data being inserted is tiny, relative to the allocated table
(many of them hav 1 million + rows), I'm certain that the table really does
have space for the row.

I'm not quite so certain of space in the indexs, though. However I would
have expected a more specific error message if it were index size related.

Anyone have words of wisdom on this?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin



Sun, 23 May 2004 22:47:11 GMT
 ORA-01631, and table is not full, is this an index problem?

Quote:

> I've got a newly installde data collection instnace (7.3.4.5) that's
> usdenly returning ORA-01631 when data is atempted to be loged into some
> tables.

> But, the following query, which I picked up from the O'riley "Oracle
> Preformance Tuning":

> SELECT BLOCKS
>     ALLOCATED_BLKS,
>                 COUNT(DISTINCT SUBSTR(T.ROWID,1,8) ||
>                 SUBSTR(T.ROWID,15,4)) USED, (COUNT(DISTINCT
>                 SUBSTR(T.ROWID,1,8) || SUBSTR(T.ROWID,15,4)) /
>                 BLOCKS) * 100 PCT_USED
>         FROM
>                 SYS.DBA_SEGMENTS E,
>                 &&USER .&&TAB_NAME T
>                 WHERE
>         E.SEGMENT_NAME = UPPER ('&&TAB_NAME')
>                 AND E.SEGMENT_TYPE = 'TABLE' GROUP BY
>                 E.BLOCKS ;

> Says:

> Enter value for user: flink
> Enter value for tab_name: B410

> ALLOCATED_BLKS       USED   PCT_USED
> -------------- ---------- ----------
>          17410      17219 98.9029294

> And since the data being inserted is tiny, relative to the allocated table
> (many of them hav 1 million + rows), I'm certain that the table really does
> have space for the row.

> I'm not quite so certain of space in the indexs, though. However I would
> have expected a more specific error message if it were index size related.

> Anyone have words of wisdom on this?
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>                                                 -- Benjamin Franklin

Did you read the text of the error message?  Use the 'oerr' command at
the UNIX prompt.  This will give you the cause of the error and the
solution.
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.


Sun, 23 May 2004 22:53:16 GMT
 ORA-01631, and table is not full, is this an index problem?

Quote:
>>                                                 -- Benjamin Franklin
>Did you read the text of the error message?  Use the 'oerr' command at
>the UNIX prompt.  This will give you the cause of the error and the
>solution.

$ oerr ORA 01631
01631, 00000, "max # extents (%s) reached in table %s.%s"
// *Cause:  A table tried to extend past maxextents
// *Action: If maxextents is less than the system maximum, raise it.
Otherwise,
//          you must recreate with larger initial, next or pctincrease
params

If you _read_ the original post, you will see that I clearly know what the
message is saying, and it's clearly not correct. That's why I asked for
some help.

As you can see from the data in the original post, there is free space in
the existing extents for this table, therfore it should not need to get
more extnts.

Or am I missing somehting here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin



Mon, 24 May 2004 01:27:57 GMT
 ORA-01631, and table is not full, is this an index problem?

The free blocks and the maxextents are not related ( at least not in your situation)

It is not clear from your initial posting,or your followup, what the MAXEXTENTS for that  table is...
The error is  specific to the # of your extents not their size....As it states, if your maxextents is at the max allowed for
your system then the only solution is to rebuild the table with new parameters...

Quote:

>>>                                                 -- Benjamin Franklin
>>Did you read the text of the error message?  Use the 'oerr' command at
>>the UNIX prompt.  This will give you the cause of the error and the
>>solution.

>$ oerr ORA 01631
>01631, 00000, "max # extents (%s) reached in table %s.%s"
>// *Cause:  A table tried to extend past maxextents
>// *Action: If maxextents is less than the system maximum, raise it.
>Otherwise,
>//          you must recreate with larger initial, next or pctincrease
>params

>If you _read_ the original post, you will see that I clearly know what the
>message is saying, and it's clearly not correct. That's why I asked for
>some help.

>As you can see from the data in the original post, there is free space in
>the existing extents for this table, therfore it should not need to get
>more extnts.

>Or am I missing somehting here?

-----=  Posted via Newsfeeds.Com, Uncensored Usenet News  =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
 Check out our new Unlimited Server. No Download or Time Limits!
-----==  Over 80,000 Newsgroups - 19 Different Servers!  ==-----


Mon, 24 May 2004 03:30:54 GMT
 ORA-01631, and table is not full, is this an index problem?

Quote:
>The free blocks and the maxextents are not related ( at least not in your situation)
>It is not clear from your initial posting,or your followup, what the MAXEXTENTS for that  table is...
>The error is  specific to the # of your extents not their size....As it states, if your maxextents is at the max allowed for
>your system then the only solution is to rebuild the table with new parameters...

OK, I see that.

All the tables for this instance (system tables excepted) are created with
a fixed initial sze, and no provision for growing, thus there should be no
reason for it to attempt to allocate more extents untill it fills the
iniital one(s), correct?

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                                                -- Benjamin Franklin



Mon, 24 May 2004 08:21:40 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. error ORA-01855: AM/A.M. or PM/P.M. required

2. ORA-01631 problem

3. ora-01631

4. Please help-- Error ORA-01631

5. I am getting this message when i am tring to export or import anything using

6. Busy Day = Slowdown from 12 AM - 5 AM

7. Use of @am, Am I dumb?

8. Patches (current CVS) for changes if index AM tables

9. HELP: 0RA-01631: max # extents (505) reached in table PLNDAD (7.1.6/SCO5)

10. where EXISTS / NOT EXISTS do not work (I am really pannic)

11. Problem: I am trying to insert a query into a table


 
Powered by phpBB® Forum Software