ArcSDE for Oracle Table Space Management-Temporary (TEMP) tablespace

The Oracle temporary tablespace is used primarily for querying and storing some buffer data. The primary reason for the temporary table space consumption is the need to sort the intermediate results of the query.

Restarting the database frees up the temporary tablespace, assuming that the instance cannot be restarted, and the temp table space continues to grow as the problem SQL statement runs. Until you run out of hard disk space.


The following operations can consume a large amount of temporary:
1, users run IMP/EXP import and export operations, will use a large number of temporary segments
2, when the user at Create or rebuild index
3. When you run the CREATE TABLE ... as statement
4. When you move your data to another table space
5. User run sort order by or group by
6. User run distinct operation
7. The user runs union or intersect or minus
8, the user runs Sort-merge joins
9. User Run Analyze


For ArcGIS users, the Oracle logical migration, the rebuilding of Spatial indexes, the Association of Spatial Data with attribute data, and so on, are used to the temporal tables of Oracle, especially if spatial data with larger data volumes is reconstructed for spatial indexes, using PGA memory. Assuming that the memory resource is insufficient, the temporary tablespace resource is used, so the management of the temporal tablespace is also more important to the user.


In general, after you create an Oracle library, there is a temporary tablespace temp, the default size is 30MB, and when you create a database user, a temporary tablespace is set for that user, and some of the above actions by that user will naturally occupy temporary tablespace resources. So the assumption is OLTP system, multiple users are doing different operations, it is bound to bring temporary table space resources occupy. So a very large number of users can create more than one table space, according to the user business type allocation of the corresponding temporary table space size. For large operations (large queries, large categorical queries, large statistical analyses, and so on), you should specify a separate, high-capacity temporary tablespace, and of course we can create a temporary table space group to allow Oracle to proactively allocate temporary tablespace resources.


A temporary tablespace group is created with multiple temporal tablespace data files, which are then formed into a temporary tablespace group that sets the default temporary tablespace for Oracle, so the temporary tablespace set created by the user is also the temporary tablespace group, allowing Oracle to proactively manage temporal tablespace resources.

The following are official Oracle Help:

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace are Inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces .

----------------------------------------------------------------------------------

Copyright all, the article agreed to reprint, but must be linked to the source address, otherwise investigate legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Let's practice creating a temporary tablespace group

To find out the default temporary tablespace information

Sql> select Tablespace_name from Dba_tablespaces; Tablespace_name------------------------------Systemsysauxundotbs1tempusersexamplesdeesriesri2test has selected 10 rows.

Create multiple temporal tablespace data files

sql> Create temporary tablespace temp2 tempfile ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp02.dbf ' size 10M; table space created. sql> Create temporary tablespace temp3 tempfile ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp03.dbf ' size 10M; table space created. sql> Create temporary tablespace temp4 tempfile ' E:\APP\ADMINISTRATOR\ORADATA\ORCL\temp04.dbf ' size 10M; table space created. Sql> select name from V$tempfile; NAME--------------------------------------------------------------------------------E:\APP\ADMINISTRATOR\ Oradata\orcl\temp01. Dbfe:\app\administrator\oradata\orcl\temp02. Dbfe:\app\administrator\oradata\orcl\temp03. Dbfe:\app\administrator\oradata\orcl\temp04. Dbf

join a temporary table space group

sql> Alter tablespace temp tablespace group temp_group; table space has changed. sql> alter tablespace TEMP2 tablespace group temp_group; table space has changed. sql> alter tablespace Temp3 tablespace group temp_group; table space has changed. sql> alter tablespace temp4 tablespace group temp_group; table space has changed. Sql> select * from Dba_tablespace_groups; Group_name                     tablespace_name------------------------------------------------------------temp_group                     Temptemp_group                     temp2temp_group                     temp3temp_group                     TEMP4

set the temporary tablespace as a temporary tablespace group

sql> ALTER DATABASE default temporary tablespace temp_group; Sql> Select Temporary_tablespace from dba_users where username= ' SDE '; Temporary_tablespace------------------------------Temp_group

----------------------------------------------------------------------------------

Copyright all, the article agreed to reprint, but must be linked to the source address, otherwise investigate legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Questions about temporal tablespace:

Very many users will use the temporary table space now, assuming that the operation task is complete, the system will not voluntarily clean up the temporary tablespace resources.

Ability to view temporary tablespace occupancy and spare rates with oracle11g new Dba_temp_free_space view

Sql> select * from Dba_temp_free_space; Tablespace_name                tablespace_size allocated_space free_space-------------------------------------------------- --------------------TEMP                                  32497664         2088960   31457280temp4                                 10485760         2097152    9437184temp3                                 10485760         4194304    7340032temp2                                 10485760         3145728    9437184sql> Select Allocated_space *100/tablespace_size as used from Dba_temp_free_space;      Used----------6.42803126        30

Assuming that the type of temporary tablespace is the used space in Temporary,temporary tablespace, it will not be released unless shutdown.

Assuming that the temporary type of temporary tablespace is used, after the database has just been started, the first statement using temporary tablespace will create a sort segment, which will not be released, Unless the database is restart, you can use V$sort_segment to see the current assigned SORT segments usage.

Assuming that the type of temporary tablespace is Permanent,smon, it will be cleaned after the process no longer uses the ephemeral segment.

The assumption is that the permanent tablespace is used by Smon to delete the temporary segments created after the end of the statement, so that the space can be used by other objects.

For performance reasons, when a temporary extent is assigned, Tablespace will make a mark, the extent will not be released or recycled after the operation, and the extent is simply marked as free, The following sort operations are available, eliminating the burden of system allocation and recycling of temporary extent. The recommendations are in the temporary type.

Of course, in the oracle11g version number the user can also use the ALTER tablespace SHRINK command to SHRINK the temporary tablespace for the freed resource.

----------------------------------------------------------------------------------

Copyright all, the article agreed to reprint, but must be linked to the source address, otherwise investigate legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Refer to Oracle Help documentation:

Shrinking a locally Managed temporary tablespace

shrink SPACE  clause of the  alter tablespace  statement to shrink a temporary tablespace, or the < Code style= "FONT-SIZE:13PX;" >shrink tempfile  clause of The alter tablespace   Statement to shrink a specific temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The Optional keep  clause defines a minimum size for the tablespace or Temp file.

Shrinking is a online operation, which means that user sessions can continue to allocate sort extents if needed, and alre Ady-running queries is not affected.

The following example shrinks the locally managed temporary tablespace while lmtmp1 ensuring a minimum size of 20M.

ALTER tablespace lmtemp1 SHRINK SPACE KEEP 20M;

The following example shrinks the temp file of the lmtemp02.dbf locally managed temporary tablespace lmtmp2 . Because KEEP the clause is omitted and the database attempts to shrink the temp file to the minimum possible size.

ALTER tablespace lmtemp2 SHRINK tempfile '/u02/oracle/data/lmtemp02.dbf ';

The default is the ability to omit the keep parameter, assuming that the keep clause is ignored, and the database tries to shrink the tablespace/temporary file (the total space of all currently used extents) as much as possible by simply satisfying the other storage properties, assuming that the keep size cannot exceed the maximum value of the operation's temporal tablespace by using the Keep parameter. Assume that the Tablespace object participates in a temporary tablespace group that also applies to the command.

Note: The temporary table space is too large or too small will have a direct impact on database performance, so it is recommended that the shrink temporary table space is used keep parameters.

References:

Http://blog.chinaunix.net/uid-21267700-id-3295645.html

http://blog.csdn.net/tianlesoftware/article/details/8225395

----------------------------------------------------------------------------------

Copyright all, the article agreed to reprint, but must be linked to the source address, otherwise investigate legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------