Temporary table space function

The primary purpose of a temporary tablespace is to sort operations in a database [such as creating indexes, orders by and group BY, DISTINCT, union/intersect/minus/, sort-merge and join, analyze commands], managing indexes [ such as the creation of indexes, IMP for data import], access to views, and so on to provide a temporary computing space, when the operation is completed, the system will automatically clean up.

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

If the temporary table space is not set to auto expand, then the transaction execution will report ora-01652 that cannot extend the temporary segment if the temporary table space is not sufficient: 1, set up the temporary data file automatic expansion, or 2, increase the temporary table space.

temporary table space related actions:

To query the default temp table space:

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

Query temp table space status:

Sql> Select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from Dba_temp_files;

Tablespace_name

——————————

file_name

——————————————————————————–

File_size AUT

———- —

TEMP

/u01/app/oracle/oradata/orcl/temp01.dbf

YES

To extend a temporary table space:

Method One, increase the temporary file size:

sql> ALTER DATABASE tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' resize 100m;

Database altered.

Method Two, set the temporary data file to automatic expansion:

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' autoextend on next 5m MaxSize Unlimited;

Database altered.

Method Three, add a data file to the temporary tablespace:

sql> Alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf ' size 100m;

Tablespace altered.

To delete a data file for a temporary table space:

sql> ALTER DATABASE Tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf ' drop;

Database altered.

Remove temporary tablespace (delete completely):

Sql> drop tablespace temp1 including contents and datafiles cascade;

Tablespace dropped.

To create a temporary table space:

sql> Create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf ' size 10M;

Tablespace created.

To change the default temp table space for a system:

sql> ALTER DATABASE default temporary tablespace Temp1;

Database altered.

The default temporary table space for all users will be switched to the new temporary table space:

Sql> select Username,temporary_tablespace,default_ from Dba_users;

USERNAME Temporary_tablespace

—————————— ——————————

Mgmt_view TEMP1

SYS TEMP1

SYSTEM TEMP1

Dbsnmp TEMP1

Sysman TEMP1

SCOTT TEMP1

Outln TEMP1

Mdsys TEMP1

Ordsys TEMP1

Exfsys TEMP1

Dmsys TEMP1

USERNAME Temporary_tablespace

—————————— ——————————

Wmsys TEMP1

Ctxsys TEMP1

ANONYMOUS TEMP1

XDB TEMP1

Ordplugins TEMP1

Si_informtn_schema TEMP1

Olapsys TEMP1

ORACLE_OCM TEMP1

Tsmsys TEMP1

BI TEMP1

PM TEMP1

To change a user's temporary table space:

sql> alter user Scott temporary tablespace temp;

User altered.

Sql> select Username,temporary_tablespace from Dba_users;

USERNAME Temporary_tablespace

—————————— ——————————

Mgmt_view TEMP1

SYS TEMP1

SYSTEM TEMP1

Dbsnmp TEMP1

Sysman TEMP1

SCOTT TEMP

Outln TEMP1

Mdsys TEMP1

Ordsys TEMP1

Exfsys TEMP1

Dmsys TEMP1

USERNAME Temporary_tablespace

—————————— ——————————

Wmsys TEMP1

Ctxsys TEMP1

ANONYMOUS TEMP1

XDB TEMP1

Ordplugins TEMP1

Si_informtn_schema TEMP1

Olapsys TEMP1

ORACLE_OCM TEMP1

Tsmsys TEMP1

BI TEMP1

PM TEMP1

USERNAME Temporary_tablespace

—————————— ——————————

Mddata TEMP1

IX TEMP1

SH TEMP1

DIP TEMP1

OE TEMP1

HR TEMP1

Rows selected.

Oracle temporary tablespaces are used primarily for querying and storing buffer data. 。 The primary reason for temporary table space consumption is the need to sort the intermediate results of a query. Restarting a database frees up temporary tablespaces, and if you can't restart the instance and keep the problem SQL statement up and down, the temp table space grows. Until you run out of hard disk space. Online speculation on the allocation of disk space, Oracle is using a greedy algorithm, if the last disk space consumption reached 1GB, then the temporary table space is 1GB. That is, the size of the current temporary tablespace file is the maximum size of a temporary table space used historically. Primary role of temporary tablespace: index create or rebuild order by or group by DISTINCT operations Union or intersect or minus Sort-merge joins

***************************************************************************************

When sorting, grouping, and indexing are done in an Oracle database, a lot of temporary data is generated. If you have a table of employee content, the database is the time to set up records to save. If a user queries by using the ORDER by sort statement to specify the sort by employee number, all the records that are created after sorting are temporary data. What do Oracle databases do with these temporary data?

Typically, Oracle databases first store these temporary data in the PGA (Program Global Area) of memory. In this program global area, there is a place where the cries are sorted, and are used specifically to store the temporary data generated by the sort operation. But the size of the partition is limited. When the size of the partition is not large enough to accommodate the sorted records, the database system stores the temporary data in the temporary tablespace. This is the origin of the temporary table space. It looks as if the temporary table space is a temp and won't have much impact on the database. In fact, we are misunderstanding this temporary table space. When a user makes a database operation, sorting, grouping, indexing, and so on, the jobs are unavoidable, which produces a lot of temporary data. This basically requires a temporary tablespace for each database. If the temporary table space is not set properly, it will have a great negative impact on the database function. For this reason, the administrator can not be complacent when maintaining this temporary table space. Avoid the ability to affect the database because of improper setting of temporary table spaces. Specifically, the main need to focus on the following aspects of the content.

To create a user, remember to create a temporary tablespace for the user.

It is a good idea to specify temporary tablespace space for users when creating users. You can use the statement default temporary table space statement to set the default temporary tablespace for the database. However, this is not mandatory in Oracle databases. But I strongly recommend doing so. Because if you do not specify a default temporary tablespace for the user, the database system will be "smart" to create a temporary segment using system table space Systems when the user needs to use the temporary table space because of the sort operation. As we all know, this is a system table space. It is always suggested that the user's data cannot be stored in this tablespace because it is stored in the table space for data related to the operation of the system. So what would be the downside if the user's temporary tablespaces were prevented from being within the system tablespace?

This is always because the data in the temporary table space is temporary. This database system requires frequent allocation and release of temporary segments. These frequent operations result in a large amount of storage fragmentation in the system table space. When these storage fragments are more, the efficiency of the system reading the hard disk is affected, which affects the function of the database. Second, the size of the system table space is often limited. The temporary segment is also plugged in, which takes up the size of the system table space.

To do this, the database administrator needs to be aware that when a temporary table space is not specified for the user, a temporary segment is still required for the user to sort and so on. The database system then puts the temporary segments into the system tablespace. This will negatively affect the functionality of the database. So I suggest that readers and database administrators, when creating users at the same time to specify a default table space for the user to reduce the temporary segment of the system table space occupied.

Second, reasonable set up PGA, reduce the chance of temporary table space use.

When a sort operation produces temporary data, the database is not immediately stored in a temporary tablespace. Typically, these temporary data are stored in the PGA program Global area of memory first. The database system enables temporary segments in the temporary tablespace to hold the data only if the program global area cannot hold all of the data. But it is well known that the operating system reads data from memory thousands of times times more than it reads from the hard disk. The ideal scenario for this is that the program's global area is large enough to hold all of the temporary data. At this point, the database system will never use the temporary table space. So that you can improve the functionality of the database. Website Construction Www.Cx-Web.Com

But this is only an ideal. The size of the PGA program area is often limited due to the limitations of memory size. So while doing some large sort operations, this temporary table space is still unavoidable. What the database administrator can do now is to reasonably set the size of the PGA program Global area to minimize the chance of temporary table space usage. In practice, the database administrator can set initialization parameter sort_area_size parameters as needed. This parameter mainly controls the size of the sorting area within this PGA program Global zone. Usually, if this database system is mainly used to query and need a lot of sorting, grouping, indexing and other operations, then you can adjust this parameter, to enlarge the size of the PGA partition. Conversely, if the system is primarily for upgrade operations, or if other applications are deployed on the database server, the PGA partition will not be able to consume too much memory to prevent adverse effects on other applications. Therefore, the database officials can not across, need to adjust according to the actual situation. If necessary, can add system memory to increase the size of the PGA partition, thus reducing the use of temporary table space, so as to improve the database sorting, grouping and other operations functions. Baike.cx-web.com

In short, if the temporary segment is frequently used, because of memory and hard disk in the functional differences, thereby reducing the functionality of the database. Therefore, in peacetime work, the database administrator also need to monitor the use of temporary table space, to determine whether the need to take measures to reduce the use of temporary table space to improve the database query function. In order to achieve this goal, the author suggests that the database administrator can view the dynamic function view of v$sort_segment. This dynamic feature view allows you to view the usage of the system sort segment (one of the temporary segments). In addition, the dynamic function view V$sort_usage can also query user session content using sorted segments. This provides data support for database administrators to optimize database functionality. For this sort of paragraph, I would like to explain one point. For a sorted segment, all the SQL statements for the same routine (if a sort operation is required) will share the same sort segment. And the sort segment is created the first time it needs to be used. This sort segment is not freed after the sort is completed, and the sorted segment is released only after the process closes. For the above two views to be integrated analysis, to be able to get the content that the database administrator wants.

Keep enough hard disk space for the temporary table space. Website design, Www.Cx-Web.Com

The data files for other table spaces are fully allocated and initialized when they are created, i.e. they are allocated storage space when they are created. However, the temporary table space corresponds to a different temporary file. For example, in a Linux operating system, temporary tablespaces are not allocated and initialized when temporary table space is created. In other words, no storage space is allocated for temporary files. Only temporary data occurs when temporary files are needed, and the system allocates a place on the hard disk to hold temporary files. At this point, there may be a problem, that is, when the temporary file system is needed to allocate space, the system partition does not have enough storage space. At this point there will be some unpredictable consequences. Website construction

For these temporary files, it is best for the database administrator to reserve enough space for them beforehand. As in a Linux operating system, it can be prevented from being used in a separate partition and not allowed by other applications. In that case, there's no need to worry about temporary files being stored anywhere. In addition, the temporary table space is mainly used to hold some temporary files for sorting. To do this, you can also increase the speed at which the database system reads data in a temporary table space if you can store this temporary table space in a functionally better partition. In addition, because the system needs to frequently allocate data in the temporary tablespace, there are more fragments of the partition where the temporary tablespace resides. At this point, if the temporary table space is stored in a separate partition, the database administrator can defragment the partition separately to improve the functionality of the partition. So for whatever reason, it's a good idea to prevent temporary tablespaces from being in a separate partition. Not only can the temporary files have storage space, but also can improve the function of the database. Baike.cx-web.com

The last thing you need to note about temporary table spaces is that this temporary tablespace is shared by default for each user. This means that each user who is connected to the database can use the default temporary tablespace. The database administrator can specify additional temporary tablespace space for it. In particular, only a temporary tablespace can be