 |
|
|
Oracle table Unlimited Extents When an object in Oracle (table, index, table partition, etc.) needs to expand, Oracle is kind enough to automatically allocate another extent of space to accommodate the incoming data. Many DBAs, however, have horror stories about how a critical database suddenly froze in its tracks because a hub table or index had reached its maximum extent limit, which is the maximum number of extents that Oracle will allow an object to possess. If that limit was reached, a DBA could increase the maximum extent limit to a higher number, providing the object had not reached the ceiling of allowable extents for the Oracle version/operating system combination being used. But if the ceiling had indeed been reached, the DBA then had no choice but to reorganize the object into fewer extents. Such a situation can be quite time consuming, but it can be completely avoided if you create or alter your objects to have unlimited extents, which is allowed in the most recent Oracle versions (at least back to Oracle 7.3). For example, to alter an object to have unlimited extents, you can simply issue a DDL command like the following:
alter table eradmin.patient storage(maxextents unlimited);
Unlimited extents are the rule in locally-managed tablespaces, so if you choose to use these storage structures in your database, you will not have to worry about an object reaching a maximum extent limit. Still, some DBAs have expressed concerns over whether an object having hundreds or thousands of extents will experience performance problems when full table scans or similar operations are performed against them. The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher. It's only $19.95 and you can order it and get instant access to the Oracle scripts here: http://www.rampant-books.com/book_2003_1_perf.htm
|
|
|
