New Features of Temp temporary tablespace and files in Oracle11g

Temporary tablespace is a special structure in the Oracle architecture. Generally, the database user only needs to set the corresponding Temporary tablespace (to the user) and temporary segment allocation

Temporary tablespace is a special structure in the Oracle architecture. Generally, the database user only needs to set the corresponding Temporary tablespace (to the user) and temporary segment allocation

Temporary tablespace is a special structure in the Oracle architecture. In general, the database user only needs to set the corresponding Temporary tablespace (to the user), and the assignment of temporary segments is automatically completed by the system. When temporary data is not needed, the Oracle background process SMON is also responsible for revoking temporary data segments.

In Oracle's backup recovery system, temporary files are relatively low. RMAN does not recover temporary files during Backup. If the temporary file does not exist during the recovery and startup process, Oracle will automatically create the temporary file.

-------------------------------------- Split line --------------------------------------

Oracle tablespace management and optimization

Use RMAN for Automatic Recovery of table space TSPITR

Troubleshooting of table space problems in ORA-3233

Oracle SQL statement for viewing database tablespace usage

Oracle tablespace usage monitoring

-------------------------------------- Split line --------------------------------------

1. Talk about Temp

Oracle temporary tablespace mainly plays two main roles: temporary table data segment allocation and sorting summary overflow segments. The temporary table we created will be allocated with a large number of data segment structures during use. This allocation uses temporary tablespace.

Sorting summary overflow has a wide range. In SQL statements, we perform order by/group by operations. First, we select the PGA memory sort area, hash area, and bitmap area. If SQL uses a high sorting space, when the PGA corresponding to a single server process is insufficient to support the sorting requirements, the temporary tablespace will act as the Data Writing of the sorting segment. In this way, sorting will degrade from memory to external storage.

Two phenomena: if our Temp tablespace file is set to small and cannot be expanded automatically. At the same time, we also hope to add an index to a large data table. We often encounter an error after the create index statement runs for a long time, saying that the Temp tablespace cannot be expanded and the operation is stopped. The index leaf nodes are ordered, and the index creation process is accompanied by database sorting.

Another phenomenon: if our memory settings are unreasonable, SQL often includes many "meaningless" "large sorting ". This will find that our Temp space consumption is relatively large, and some SQL Performance jitter is obvious.

Setting up the Temp space management policy is an important part of the application system architecture.

2. Specify a tablespace for the temporary table.

In Oracle, The ing between the user schema and the tablespace storage structure is flexible. If you have a space Quota (Quota), you can create data tables in any tablespace In the schema. You can place objects in any tablespace.

However, before the 11g period, the Temp tablespace was not like this. After creating a user, we need to create a temporary tablespace corresponding to the user schema. If this parameter is not specified, Oracle selects the system default temporary tablespace (usually temp) as the user's temporary tablespace.

After that, all temporary segments of the user are allocated on the temporary tablespace. We cannot specify that a temporary table is allocated to another temporary tablespace.

After 11 GB, Oracle provided such freedom.

SQL> select * from v $ version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

The default tablespace of the current sys user is TEMP.

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'sys ';

DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

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

SYSTEM TEMP

In this case, the database contains two temporary tablespaces.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 481296384 481296384 480247808

Temtest 5368709120 1048576 5367660544

We can create a temporary table that does not belong to the TEMP default temporary tablespace.

SQL> create global temporary table t_temp tablespace temptest as select * from t where 1 = 0;

Table created

Subsequent temporary segments are allocated on the temptest tablespace.

SQL> insert into t_temp select * from t;

19360512 rows inserted

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 481296384 481296384 480247808

Temtest 5368709120 2248146944 3120562176

Is the move operation supported like a data table?

SQL> create global temporary table t_temp tablespace temp as select * from dba_objects where 1 = 0;

Create global temporary table t_temp tablespace temp as select * from dba_objects where 1 = 0

ORA-14451: unsupported feature with temporary table

It seems that the current version does not support moving temporary tables.

For more details, please continue to read the highlights on the next page: