While creating a large index, we receive an Oracle error indicating that the database is unable to extend a TEMP segment. However, we have plenty of free space in the temporary tablespace.
Solution:
When we get an error such as the following, our first inclination may be to think that there’s no free space in the temporary tablespace.
ORA-01652: unable to extend temp segment by 1024 in tablespace INDX_01
we cannot fix this problem by adding space to the temporary tablespace. The error message clearly indicates the tablespace that ran out of space. In this case, the offending tablespace is INDX_01, and not the TEMP tablespace. Obviously, an index creation process failed because there was insufficient space in the INDX_01 tablespace. We can fix the problem by adding a datafile to the INDX_01 tablespace, as shown here:
SQL>alter tablespace INDX_01 add datafile '/u01/app/oracle/data/indx_01_02.dbf'
2 size 1000m;
How It Works:
When we receive the ORA-01652 error, our normal tendency is to check the temporary tablespace. We check the DBA_TEMP_FREE_SPACE view, and there’s plenty of free space in the default temporary tablespace, TEMP. Well, if we look at the error message carefully, it tells we that the database is unable to extend the temp segment in the INDX_01 tablespace. When we create an index, as in this case, we provide the name of the permanent tablespace in which the database must create the new index. Oracle starts the creation of the new index by putting the new index structure into a temporary segment in the tablespace we specify (INDX_01 in our example) for the index. The reason is that if our index creation process fails, Oracle (to be more specific, the SMON process) will remove the temporary segment from the tablespace we specified for creating the new index. Once the index is successfully created (or rebuilt), Oracle converts the temporary segment into a permanent segment within the INDX_01 tablespace. However, as long as Oracle is still creating the index, the database deems it a temporary segment and thus when an index creation fails, the database issues the ORA-01652 error, which is also the error code for an “out of space” error for a temporary tablespace. The TEMP segment the error refers to is
the segment that was holding the new index while it was being built. Once we increase the size of the INDX_01 tablespace, the error will go away.
The key to resolving the ORA-01652 error is to understand that Oracle uses temporary segments in places other than a temporary tablespace. While a temporary segment in the temporary tablespace is for activities such as sorting, a permanent tablespace can also use temporary segments when performing temporary actions necessary during the creation of a table (CTAS) or an index.
the ORA-01652 error refers to the tablespace where we are rebuilding an index. If we are creating a new index, Oracle uses the temporary tablespace for sorting the index data. When creating a large index, it may be a smart idea to create a large temporary tablespace
and assign it to the user who’s creating the index. Once the index is created, we can re-assign the user the original temporary tablespace and remove the large temporary tablespace. This strategy helps avoid enlarging the default temporary tablespace to a very large size to accommodate the creation of a large index.
If we specify autoextend for a temporary tablespace, the temp files may get very large, based on one or two large sorts in the database. When we try to reclaim space for the TEMP tablespace, we may get the following error.
SQL> alter database tempfile '/u01/app/oracle/oradata/prod1/temp01.dbf' resize 500M;
alter database tempfile '/u01/app/oracle/oradata/prod1/temp01.dbf' resize 500M
*ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
One solution is to create a new temporary tablespace, make that the default temporary tablespace, and then drop the larger temporary tablespace. In Oracle Database 11g, we can simplify matters by using the following alter tablespace command to shrink the temporary tablespace:
SQL> alter tablespace temp shrink space;
Tablespace altered.
In this example, we shrank the entire temporary tablespace, but we can shrink a specific tempfile by issuing the command alter tablespace temp shrink tempfile <file_name>. The command will shrink the tempfile to the smallest size possible.
Solution:
When we get an error such as the following, our first inclination may be to think that there’s no free space in the temporary tablespace.
ORA-01652: unable to extend temp segment by 1024 in tablespace INDX_01
we cannot fix this problem by adding space to the temporary tablespace. The error message clearly indicates the tablespace that ran out of space. In this case, the offending tablespace is INDX_01, and not the TEMP tablespace. Obviously, an index creation process failed because there was insufficient space in the INDX_01 tablespace. We can fix the problem by adding a datafile to the INDX_01 tablespace, as shown here:
SQL>alter tablespace INDX_01 add datafile '/u01/app/oracle/data/indx_01_02.dbf'
2 size 1000m;
How It Works:
When we receive the ORA-01652 error, our normal tendency is to check the temporary tablespace. We check the DBA_TEMP_FREE_SPACE view, and there’s plenty of free space in the default temporary tablespace, TEMP. Well, if we look at the error message carefully, it tells we that the database is unable to extend the temp segment in the INDX_01 tablespace. When we create an index, as in this case, we provide the name of the permanent tablespace in which the database must create the new index. Oracle starts the creation of the new index by putting the new index structure into a temporary segment in the tablespace we specify (INDX_01 in our example) for the index. The reason is that if our index creation process fails, Oracle (to be more specific, the SMON process) will remove the temporary segment from the tablespace we specified for creating the new index. Once the index is successfully created (or rebuilt), Oracle converts the temporary segment into a permanent segment within the INDX_01 tablespace. However, as long as Oracle is still creating the index, the database deems it a temporary segment and thus when an index creation fails, the database issues the ORA-01652 error, which is also the error code for an “out of space” error for a temporary tablespace. The TEMP segment the error refers to is
the segment that was holding the new index while it was being built. Once we increase the size of the INDX_01 tablespace, the error will go away.
The key to resolving the ORA-01652 error is to understand that Oracle uses temporary segments in places other than a temporary tablespace. While a temporary segment in the temporary tablespace is for activities such as sorting, a permanent tablespace can also use temporary segments when performing temporary actions necessary during the creation of a table (CTAS) or an index.
the ORA-01652 error refers to the tablespace where we are rebuilding an index. If we are creating a new index, Oracle uses the temporary tablespace for sorting the index data. When creating a large index, it may be a smart idea to create a large temporary tablespace
and assign it to the user who’s creating the index. Once the index is created, we can re-assign the user the original temporary tablespace and remove the large temporary tablespace. This strategy helps avoid enlarging the default temporary tablespace to a very large size to accommodate the creation of a large index.
If we specify autoextend for a temporary tablespace, the temp files may get very large, based on one or two large sorts in the database. When we try to reclaim space for the TEMP tablespace, we may get the following error.
SQL> alter database tempfile '/u01/app/oracle/oradata/prod1/temp01.dbf' resize 500M;
alter database tempfile '/u01/app/oracle/oradata/prod1/temp01.dbf' resize 500M
*ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
One solution is to create a new temporary tablespace, make that the default temporary tablespace, and then drop the larger temporary tablespace. In Oracle Database 11g, we can simplify matters by using the following alter tablespace command to shrink the temporary tablespace:
SQL> alter tablespace temp shrink space;
Tablespace altered.
In this example, we shrank the entire temporary tablespace, but we can shrink a specific tempfile by issuing the command alter tablespace temp shrink tempfile <file_name>. The command will shrink the tempfile to the smallest size possible.
No comments:
Post a Comment