Temporary tablespace in Oracle Database

During the daily system check, I checked the database tablespace usage through the Oracle Enterprise Manager Console, and found that the system tablespace usage is 100%, and the temporary tablespace corresponding to each user is the system tablespace. Check the problem to avoid affecting the normal operation of the system.
1. Use SQL to view the temporary tablespace of the database, the temporary tablespace corresponding to the user, and the default temporary tablespace of the system.
Select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_files; autoextensible auto-extension Column
Select username, default_tablespace, temporary_tablespace from dba_users where username = 'system ';
Select * from database_properties where property_name = 'default _ TEMP_TABLESPACE'
SELECT temp_used.tablespace_name,
Total-used as "Free ",
Total as "Total ",
Round (nvl (total-used, 0) * 100/total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM (bytes_used)/1024/1024 used
From gv $ TEMP_SPACE_HEADER
Group by tablespace_name) temp_used,
(SELECT tablespace_name, SUM (bytes)/1024/1024 total
FROM dba_temp_files
Group by tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
2. Create a temporary tablespace for each user
Create temporary tablespace oastemp tempfile '/dev/vgoracle/rdboastemp. dbf' size 1024 M;
Create temporary tablespace eletemp tempfile '/dev/vgoracle/rdbeletemp. dbf' size 500 M;
Create temporary tablespace tcmtemp tempfile '/dev/vgoracle/rdbtcmtemp. dbf' size 500 M;
And specify the corresponding user.
3. Create default system tablespace
// Create a temporary tablespace for transit:
(1)> create temporary tablespace temp2
Tempfile 'd: \ oracle \ oradata \ test \ temp2.dbf 'size 512 M
Reuse autoextend on next 100 M maxsize 2048 M;
(2)> alter database default temporary tablespace temp2;
(3)> drop tablespace temp including contents and datafiles;

// Create a new temporary tablespace:
(1)> create temporary tablespace temp
Tempfile 'd: \ oracle \ oradata \ test \ temp01.dbf 'size 512 M
Reuse autoextend on next 100 M maxsize 1024 M;
(2)> alter database default temporary tablespace temp; // modify the tablespace corresponding to the user
(3)> drop tablespace temp2 including contents and datafiles;

We recommend that you back up the control file as follows:

1. Back up the control file as a binary file

SQL> alter database backup controlfile to 'I: \ oracle \ backup \ control. bkp ';

2. Back up the control file as a text file (back up to the tracking file under the oracle \ base \ admin \ sid \ udump directory, and generate an SQL script in the tracking file)

SQL> alter database backup controlfile to trace;

3. Generate a pfile backup control file through spfile

SQL> create pfile = '/pfile_backup.ora' from spfile = '/home/oracle/product/10.2.0/db_1/dbs/spfileSID. ora ';

Temporary Oracle tablespace management and impaired recovery

Oracle temporary tablespace is too large to solve the problem

Resolving ORA-14450: attempting to access a temporary table for a transaction that is already in use

Oracle creates a transaction-based and session-based temporary table and temporary table index creation Experiment

Temporary tablespace group for Oracle temporary tables (TTG)