Oracle error : ORA-01632 : Max extents reached in index

Error: ORA-01632
Text: max # extents (%s) reached in index %s.%s
Cause: An index tried to extend past maxextents
Action: If maxextents is less than the system max, raise it.
Otherwise, you must recreate with larger initial, next
or pctincrease params. Continue reading

Segments in Oracle

A segment is a container for objects (such as tables, indexes….).
A segment consists of extents.
Segment types
There are 11 types of segments in Oracle 10g:

  • table
  • table partition
  • index
  • index partition
  • cluster
  • rollback
  • deferred rollback
  • temporary
  • cache
  • lobsegment
  • lobindex

Continue reading

Manually Resizing a Datafile

You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.

Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

Continue reading

Enabling and Disabling Automatic Extension for a Datafile

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

  • Reduces the need for immediate intervention when a tablespace runs out of space
  • Ensures applications will not halt or be suspended because of failures to allocate extents

Continue reading