9.5 How do I…Allocate unlimited extents for a database object?Problem
I administer an Oracle8 instance that frequently reports an inability to allocate new extents. This happens for database tables, indexes, and rollback segments. I have set the MAXEXTENTS storage parameter to the maximum value for the database block size. How can I avoid these “failure to extend” errors?
Technique
With the advent of Oracle version 7.3, DBAs and developers can set the value of the MAXEXTENTS storage parameter to UNLIMITED. The UNLIMITED specification is valid at object creation time, and Oracle8 users can easily convert existing objects, such as rollback segments, to unlimited format as well. This How-To focuses on converting a rollback segment to unlimited extent format.
Steps
1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the script CHP9_7.SQL, as shown in Figure 9.11.
A SQL*Plus substitution variable enables the user to pass the name of any rollback segment to the CHP9_7.SQL script. The first SQL statement takes the rollback segment offline, a required prerequisite to changing a rollback segment’s MAXEXTENTS storage parameter. The next SQL statement modifies the rollback segment’s storage clause. The third and final statement puts the rollback segment back online, making it available to the Oracle8 instance.
2. Use the START command to load and execute the script CHP9_8.SQL, which queries the DBA_ROLLBACK_SEGS data dictionary table to confirm the successful completion of the operation in Step 1. This query and its result appear in Figure 9.12.
How It Works
The script takes the rollback segment offline and changes the value of the MAXEXTENTS storage parameter. Step 2 confirms the change in the Oracle8 data dictionary.
Comments
To create a table in unlimited extent format, specify the MAXEXTENTS UNLIMITED option in the storage clause of the CREATE_TABLE statement. Figure 9.13 illustrates this operation.
Note that the queries in Figures 9.12 and 9.13 report the value of the MAXEXTENTS parameter as 2147483645. Apparently, the UNLIMITED value does not mean that the number of allowable extents is literally unlimited, but 2 billion extents should be adequate for most of us!
The initialization parameter COMPATIBLE must be at least 7.3.0.0 to enable you to take advantage of unlimited extents capability.