Re: extents exxceeded ?? ( need help )
Date: Tue, 17 Aug 1993 08:24:07 +0000
Message-ID: <745575847snz_at_fenton.demon.co.uk>
In article <1993Aug16.041546.20590_at_amoco.com> zdxc0d_at_amoco.com writes:
>In article 10837_at_njitgw.njit.edu, yxc1612_at_hertz.njit.edu (yung-feng chang me
> stnt) writes:
>-->Dear netters:
>-->
>--> One user get following message while he is running a query.
>-->
>-->ORA-01556 maximum number of 99 extents exceeded.
>-->
>--> I check the free space for the tablespace first; It still have
>-->1.2G available!!
>-->
>--> Then, I try to allocate more extents for tables and tablespaces that
>-->he used. I increase max extents for the tablespace from 99 to 150, then
>-->I run his query again. I find the error message become:
>-->
>-->ORA-01556 maximum number of 121 extents exceeded.
>-->
>--> No matter what max extent or extent size I increase, it seem that
>-->121 is "ceil"??
>-->
>--> I find lots fragnments on that tablespace. Is this cause problem?
>-->If so, how to remove those fragnments. If not, what cause this problem?
>-->
>-->Thanks, in advance.
>-->
>-->Sincerely
>--> D. L.
>You will have to recreate the tablespace with more sensible extent
>sizes.(but see note at bottom of message)
>I take it you're running unix becuase of the '121 extents' message.
>You may only have a maximum of 121 extents in Unix. Therefore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^The maximum number of extents in Oracle is a function of the database block size. Under Unix, the default database block size is 2K , therefore Unix sites typically see the message 'maximum number of 121 extents exceeded'. I list below the relationship of block size to max no. of extents. Block Size Max. No. Extents
512 Bytes 25 1 K 57 2 K 121 4 K 249 8 K 505
On our site (we run Pyramid hardware), I created the database with a 4 K block size because some developers got the sizing wrong, and tables no longer fell over at 121 extents. VMS defaults to a 4K block size, so it's easy to assume they are operating system differences rather than diffrent default settings within the init.ora.
To adjust the database block size, you will have to export and re-create the database setting the init.ora parameter db_block_size = 4096 Hope this helps!!!
>you should set the initial ,next extents in the tablespace create
>stament to something aproxximating to tablespace_size/121.
>e.g for a TS of 121Mb
>create tablespace FRD
>datafile .........
>defualt storage (initial 1M
> next 1M
> minextents 1
> maxextents 121
> pctincrease 0)
>
>NOTE : But you also did not specify what the tablespace name is
>that you're having problems with . It could be the fact that
>your user is still using SYSTEM instead of a 'user work area'
>tablespace.You could have been be more specific but the above should
>get on the road to fixing your problem.
>
>Good Luck %^)
>---
>-----------------------------------------------------------------------------
>David Crowson |
>DBA (ORACLE V.4,5,6,7) | "Rude alert! Rude alert! An electrical fire
>Amoco Exploration | has knocked out my voice-recognition unicycle! Many
>Ealing, London | Wurlitzers are missing from my database! Abandon shop!
>
>dcrowson_at_amoco.com | This is not a daffodil! Repeat: This is not a daffodil!>"
>"My views not Amoex's" | - Holly (Ship's computer on Red Dwarf)
>
>
>
>
-- ------------------------------------------------------------------------- David Fenton Tel : (+44) (0)51-327-4467 North West England, UK. davef_at_fenton.demon.co.ukReceived on Tue Aug 17 1993 - 10:24:07 CEST