ArcSDEforOracle tablespace management temporary (TEMP) tablespace

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results. Restarting the database can release temporary tablespace. If the instance cannot be restarted, the temp tablespace will continue to grow as the problematic SQL statement is executed. Until the hard disk space is exhausted. The following operations will take up

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results. Restarting the database can release temporary tablespace. If the instance cannot be restarted, the temp tablespace will continue to grow as the problematic SQL statement is executed. Until the hard disk space is exhausted. The following operations will take up

Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results.

Restarting the database can release temporary tablespace. If the instance cannot be restarted, the temp tablespace will continue to grow as the problematic SQL statement is executed. Until the hard disk space is exhausted.


The following operations occupy a large amount of temporary:
1. When you perform imp/exp import and export operations, a large number of temporary segments are used.
2. When you Create or rebuild index
3. When executing the create table... as statement
4. When moving data from a user to another tablespace
5. the user performs Order by or group
6. the user performs the Distinct operation.
7. The user executes Union, intersect, or minus.
8. The user executes Sort-merge joins
9. The user executes analyze


For ArcGIS users, Oracle logical migration, spatial index reconstruction, spatial data association with attribute data, and other operations will all use Oracle temporary tables, in particular, if you re-create a spatial index with a large amount of data, the PGA memory will be used. If the memory resources are insufficient, temporary tablespace resources will be used, therefore, temporary tablespace management is also important for users.


Generally, after an Oracle database is created, a temporary tablespace TEMP is created by default. The default size is 30 MB. When a database user is created, a temporary tablespace is set for the user, some of the above operations will naturally occupy Resources in the temporary tablespace. If it is an OLTP system, multiple users are performing different operations, which will inevitably lead to the occupation of temporary tablespace resources. Therefore, many users can create multiple tablespaces and allocate the corresponding Temporary tablespace size according to the user's business type. For large-sized operations (large-sized queries, large-sized classified queries, large-sized statistical analysis, etc.), a separate large-sized temporary tablespace should be specified, of course, we can also create temporary tablespace groups so that Oracle can automatically allocate temporary tablespace resources reasonably.


A temporary tablespace group is used to create multiple temporary tablespace data files and then form these temporary tablespace into a temporary tablespace group to set the default temporary tablespace of Oracle, the temporary tablespace set by the user is also the temporary tablespace group, so that Oracle can automatically manage the temporary tablespace resources.

Oracle official help is as follows:

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 is inadequate to hold the results of a sort, participant ularly on a table that has partition partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

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

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

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

The following describes how to create a temporary tablespace group.

Find out the default temporary tablespace Information

SQL> select tablespace_name from dba_tablespaces; 10 rows have been selected for the TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERSEXAMPLESDEESRIESRI2TEST.

Create multiple temporary tablespace data files

SQL> create temporary tablespace temp2 tempfile 'e: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ temp02.dbf' size 10 M; the tablespace has been created. SQL> create temporary tablespace temp3 tempfile 'e: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ temp03.dbf' size 10 M; the tablespace has been created. SQL> create temporary tablespace temp4 tempfile 'e: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ temp04.dbf' size 10 M; the tablespace has been created. 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

Add a temporary tablespace Group

SQL> alter tablespace temp tablespace group temp_group; The tablespace has been changed. SQL> alter tablespace temp2 tablespace group temp_group; The tablespace has been changed. SQL> alter tablespace temp3 tablespace group temp_group; The tablespace has been changed. SQL> alter tablespace temp4 tablespace group temp_group; The tablespace has been changed. SQL> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME -------------------------------- ---------------------------- TEMP_GROUP TEMPTEMP_GROUP TEMP2TEMP_GROUP TEMP3TEMP_GROUP TEMP4

Set a temporary tablespace as a temporary tablespace Group

SQL> alter database default temporary tablespace temp_group; the database has been changed. SQL> select temporary_tablespace from dba_users where username = 'sde'; TEMPORARY_TABLESPACE------------------------------TEMP_GROUP

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

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

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

About the temporary tablespace:

Many users will find that when using temporary tablespace, the system will not automatically clear the resources of the temporary tablespace after the operation task is completed.

You can use the DBA_TEMP_FREE_SPACE view added by Oracle11g to view the usage and idle rate of the temporary tablespace.

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        20        40        30

If the temporary tablespace type is TEMPORARY, the space used in the temporary tablespace will not be released unless shutdown.

If the TEMPORARY tablespace of the TEMPORARY type is used, after the database is started up, the first statement that uses the TEMPORARY tablespace for sorting will create an sort segment, which will not be released unless the database restart, you can use V $ SORT_SEGMENT to view the usage of the currently assigned sort segments.

If the temporary tablespace type is PERMANENT, SMON will clean up after the process no longer uses the temporary segment.

If PERMANENT tablespace is used for sorting, smon is responsible for deleting created temporary segments after statement, so that the space can be used by other objects.

Considering the performance, when a temporary extent is assigned, tablespace will mark it. After the operation, the extent will not be released or recycled, this extent is marked as free and available for the subsequent sort operations. This saves the system from allocating and revoking the temporary extent load. We recommend that you use a temporary type.

Of course, in Oracle11g version, users can also use the alter tablespace shrink command to Shrink the resources in the temporary TABLESPACE to be released.

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

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

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

See Oracle help documentation:

Shrinking a Locally Managed Temporary Tablespace

Large sort operations saved med by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. after the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. for this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.

You useSHRINK SPACEClause ofALTER TABLESPACEStatement to shrink a temporary tablespace, orSHRINK TEMPFILEClause ofALTER TABLESPACEStatement 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 optionalKEEPClause defines a minimum size for the tablespace or temp file.

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

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

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

The following example shrinks the temp filelmtemp02.dbfOf the locally managed temporary tablespacelmtmp2. BecauseKEEPClause is omitted, the database attempts to shrink the temp file to the minimum possible size.

ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

By default, the KEEP parameter is not included. If the KEEP clause is ignored, the database tries to contract the tablespace/temporary files (the total space of all currently used zones) as long as other storage properties are satisfied ), if the KEEP parameter is used, it is recommended that the size of the KEEP cannot exceed the maximum value of the temporary tablespace operated. This command is also applicable if the tablespace object is in a temporary tablespace group.

Note: Temporary tablespaces that are too large or too small will have a direct impact on the database performance. Therefore, we recommend that you use the KEEP parameter in the Shrink temporary tablespaces.

References:

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

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

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

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

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