Shulou(Shulou.com)06/01 Report--

Error message: [HY000] (1652) [Oracle] [ODBC] [Ora] ORA-01652: temp segment cannot be extended through 128in tablespace TEMP

Cause analysis: oracle temporary table space is insufficient, transaction execution will generally report an error that ora-01652 cannot extend the temporary period. Because oracle always tries to allocate contiguous space, the above phenomenon will occur when there is not enough allocable space or discontiguous allocation.

Recall the role of temporary tablespaces:

Temporary tablespaces are mainly used to provide temporary computing space for sorting operations in the database (such as creating indexes, order by and group by, distinct, union/intersect/minus/, sort-merge and join, analyze commands), managing indexes (such as creating indexes, IMP for data import), accessing views, and so on. When the operation is completed, the system will automatically clean up.

When the temporary table space is insufficient, the operation speed is abnormally slow, and the temporary table space grows rapidly to the maximum space (the limit of expansion), and generally does not clean up automatically.

Solution: we know that because ORACLE takes tablespaces as logical structures-units, and the physical structure of tablespaces is data files, data files are physically created on disk, and all objects in tablespaces also exist on disk, so data files must be added in order to add space to tablespaces. First take a look at the free space of the specified tablespace, using the view SYS.DBA_FREE_SPACE, where each record represents the size of the fragment of the available space. Of course, you can also expand the tablespace.

1. Increase temporary tablespaces (or increase temporary tablespace files).

2. Set up automatic extension of temporary data files

Steps:

1. Query temporary tablespace status:

SQL > col file_name for A20

SQL > select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files

2. Expand temporary tablespace

SQL > alter database tempfile'/ u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'resize 819m

Or you can add temporary tablespace files

Alter tablespace temp add tempfile'/u01/app/oracle/oradata/CP7PV1DB/temp02.dbf' size 8192m

Note: temporary tablespace files can only be added if they have reached the maximum file size of 32G.

SQL > alter tablespace temp add tempfile'/ u01 size autoextend on next autoextend on next 128m oradata 6G

SQL > ALTER TABLESPACE TEMP DROP TEMPFILE'/ u01qqappActionoradataPlacement CP7PV1DBUniqtemp03.dbf'

SQL > ALTER DATABASE TEMPFILE'/ u01According to an oracle RESIZE RESIZE 6G

3. Set up automatic extension

SQL > alter database tempfile'/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' autoextend on next 10m maxsizeunlimited

4. Error report when expanding tablespace

ERROR atline 1:

ORA-00376:file 201 cannot be read at this time

ORA-01110:data file 201:'/ u01According to oradata, CP7PV1DB, temp01.dbf'

The reason is that the temporary tablespace is offline for some reason, and the modification is successful after being modified to online.

SQL > alter database tempfile'/ u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'online

Database altered.

5. Delete temporary tablespaces (supplementary)

SQL > drop tablespace temp01 including contents and datafiles

SQL > ALTER DATABASE TEMPFILE'/ u01 DROPINCLUDING DATAFILES

Database altered.

Note: when deleting temporary data files for temporary tablespaces, you do not need to specify the INCLUDING DATAFILES option to actually delete the physical files, otherwise you need to delete the physical files manually. Nor can you directly delete the current user's default tablespace, otherwise an ORA-12906 error will be reported. If you need to delete a default temporary table space, you must first create a temporary table space, then specify the newly created table space as the default table space, and then delete the original temporary table space.

6. Change the system default temporary tablespace

-- query the default temporary tablespace

SQL > select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

-

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

-- modify the default temporary tablespace

SQL > alterdatabase default temporary tablespace temp02

Databasealtered.

We can query whether to switch to TEMP02:

SQL > select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

-

DEFAULT_TEMP_TABLESPACE TEMP02 Name of default temporary tablespace

7. View the utilization of temporary tablespaces

SQL > SELECT temp_used.tablespace_name

Total-used as "Free"

Total as "Total"

Round (nvl (total-used, 0) * 100 / total, 3) "Free percent"

FROM (SELECT tablespace_name,SUM (bytes_used) / 1024 / 1024 used

FROM GV_$TEMP_SPACE_HEADER

GROUP BY tablespace_name) temp_used

(SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total

FROM dba_temp_files

GROUP BY tablespace_name) temp_total

WHEREtemp_used.tablespace_name = temp_total.tablespace_name

TABLESPACE_NAME Free Total Free percent

TEMP 6876 8192 83.936

8. Find sql statements that consume a lot of resources

Select se.username

Se.sid

Su.extents

Su.blocks * to_number (rtrim (p.value)) asSpace

Tablespace

Segtype

Sql_text

From v$sort_usage su, v$parameter p, v$session se, v$sql s

Where p.name = 'db_block_size'

And su.session_addr = se.saddr

And s.hash_value = su.sqlhash

And s.address = su.sqladdr

Order by se.username, se.sid

First create a temporary table space, set this table space as the default temporary table space, then delete the previous temporary table space, and then delete the data file, it is very simple.

Experience:

The data file in the Oracle table space can only be added but not deleted. You can move the data file and rename the pointer to the table space. The default temporary tablespace cannot be offline.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.