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: