- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: extent issue
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 04:24 AM
тАО09-30-2003 04:24 AM
I have a Oracle Database which has reached it's max extents of a 121. i have set this to unlimited but still get the same error message.
sql
alter tablespace
when i query it with the following i get:
SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,
EXTENTS, MAX_EXTENTS
FROM DBA_SEGMENTS
WHERE MAX_EXTENTS - EXTENTS < 4
ORDER BY EXTENTS;
can anyone advise how to trouble shoot this
it appeared when i looked at the orignal tablespace it appeared with it changed ok.
select t.initial_extent,t.next_extent,t.min_extents,t.max_extents,t.pct_increase
from dba_tablespaces t
where t.tablespace_name = 'PMDB_NDX1';
regards
grace
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 04:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:00 AM
тАО09-30-2003 05:00 AM
Re: extent issue
PLease find enclosed if you look around
Tue Sep 30 16:54:03 2003.
It is there
Thanks
Grace
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:09 AM
тАО09-30-2003 05:09 AM
Re: extent issue
But same idea :
ALTER INDEX
>>>>>>ADMUSER.NDX_TASKACTV_TASK
ALTER INDEX ADMUSER.NDX_TASKACTV_TASK
STORAGE ( MAXEXTENTS 400);
You can also change the extent size
Rgds,
Jean-luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:11 AM
тАО09-30-2003 05:11 AM
Re: extent issue
Can u please give the version info,Which tablespace(temp, rollback e.t.c) of your database.
Is that a table of Frequent delete/inserts e.t.c (i mean to come by steps)is that for a table ?
If it is 7.2, it depends on block size which restricts max extents (with 2K its 121,with 4K its upto 249 and if 8K upto 505).
One thing we can do is Export the related data, reorganise with (tablespace/table)with proper values and NEXT taken care and now import your data.
Thanks,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:12 AM
тАО09-30-2003 05:12 AM
Re: extent issue
ALTER INDEX ADMUSER.NDX_TASKACTV_TASK
STORAGE ( NEXT 4096K );
This is an example to chnage the next extent size to 4Mb.
Rgds,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:14 AM
тАО09-30-2003 05:14 AM
Re: extent issue
Regards
Grace
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2003 05:20 AM
тАО09-30-2003 05:20 AM
Re: extent issue
great !! how fast u r responding, i am just typing Click!! Jean with Good solution !! again typing Click!!! Grace had Done it !!
Thanks friends!! Go support Cheers!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:26 PM
тАО10-01-2003 04:26 PM
Re: extent issue
*
Once again, LMT will be the way to go! Locally managed tablespaces will be the solution and END of all the problems you are having and will continue to have in the future!
*
read:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#9633
*
below another useful quote from Thomas Kyte:
A dictionary managed tablespace, the only type of tablespace prior to Oracle8i, manages extents in a set of database tables. A locally managed tablespace does this extent managed in a bitmap in the header of a datafile. A zero bit means that space is free, a one bit means it is allocated.
-
Using the dictionary approach, we can see a lot of contention in the database when you do lots of extends. Dictionary updates are done serially (so one user at a time) and the SQL to get the free space can be somewhat time consuming. We need to query a free space table to find the BEST free extent that is as big or bigger then the extent you want. If we cannot find that, we have to go back and try to coalesce free space (rows in the free space table that are "adjacent" to eachother) and try again. When we finally find one, we have to delete it from free space and add it into allocated space.
-
Dictionary managed tablespaces allow for extents of any size which can (and frequently does) lead to free space fragmentation. That is -- you might have 500meg of free space in a tablespace but you find that your largest CONTIGOUS
free chunk is 1m and lots are less. This is a real problem when your next extents for objects in this tablespace are all greater than 1m. You might have 500meg free but no object will be able to extend.
-
Contrast this to a locally managed tablespace. Free space is managed in a bitmap at the head of the file. Instead of serializing ALL space requests for a database -- we serialize for a shorter period of time at the file. If you have
more then 1 file, you can have more then 1 space request being processed -- hence it removes contention. The process of finding free space is faster as well -- space is typically managed in a UNIFORM fashion in a locally managed tablespace -- each extent is exactly the same size as every other extent. No long search for the "best" fit -- the first fit is the best fit. So, its faster at finding space. Additionally -- free space coalescing happens automagically. Free up an extent and its bit goes to zero. If the bit in front of it and behind it were zero -- we now have 3 zero bits to indicate three free chunks.
-
Most importantly -- it is IMPOSSIBLE to have free space fragmentation in a locally managed tablespace with uniform extents. Since every extent is the size of every other extent -- ANY extent is the correct size for ANY objects next extent. If you have 500m free in a locally managed uniform tablespace -- you really do have 500meg free. In a dictionary managed tablespace, that same tablespace might be "full".
-
In Oracle8i, use locally managed tablespaces with uniform extents. Oh yeah, if someone says "but that'll cause our objects to have many extents" just say "so what, who cares". It is perfectly OK to have objects with many 100's of extents.
*
Hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-01-2003 04:37 PM
тАО10-01-2003 04:37 PM
Re: extent issue
Very great info!
Learning alot from forums.............