Oracle 11g online recovery temporary tablespace

Oracle 11g online recovery temporary tablespace

-- Database version
SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

-- View the default temporary tablespace of the database
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
Select *
From database_properties
Where property_name like 'default % TABLESPACE ';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP2 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace

-- Check the current idle space of the temporary tablespace (view the space of the temporary tablespace before recovery)
Set line 150
Col TABLESPACE_NAME for a30
Col TABLESPACE_SIZE for 99999999999999
Col ALLOCATED_SPACE for 99999999999999
Col FREE_SPACE for 99999999999999
SELECT TABLESPACE_NAME,
TABLESPACE_SIZE/1024/1024/1024 as "TABLESPACE_SIZE (G )",
ALLOCATED_SPACE/1024/1024/1024 as "ALLOCATED_SPACE (G )",
FREE_SPACE/1024/1024/1024 as "FREE_SPACE (G )"
From DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE (G) ALLOCATED_SPACE (G) FREE_SPACE (G)
--------------------------------------------------------------------------------
TEMP2 31.9999847 31.9999847 31.9980469

-- View the current temporary file size
Col name for a50
SELECT file #, name, bytes/1024/1024/1024 as "TEMPFILE (G)" FROM v $ tempfile order by name;
SQL> SELECT file #, name, bytes/1024/1024/1024/as "TEMPFILE (G)" FROM v $ tempfile;

FILE # name tempfile (G)
-----------------------------------------------------------------------
1/oradata/app/oracle/oradata/img/temp2.dbf 31.9999847

-- Oracle 11g uses commands to release unused temporary tablespace (only locally managed temporary tablespaces are available: Doc ID 452697.1)
-- Syntax:
-- Alter tablespace tablespace shrink space | TEMPFILE tempfile_name [KEEP size];

-- Alter tablespace tablespace shrink space [KEEP size];
SQL> alter tablespace temp2 shrink space keep 30G;

Tablespace altered.

SQL> SELECT file #, name, bytes/1024/1024/1024/as "TEMPFILE (G)" FROM v $ tempfile;

FILE # name tempfile (G)
-----------------------------------------------------------------------
1/oradata/app/oracle/oradata/img/temp2.dbf 30.0009766

-- Alter tempfile tablespace SHRINK tempfile tempfile_name [KEEP size];
SQL> alter tablespace temp2 shrink tempfile '/oradata/app/oracle/oradata/img/temp2.dbf 'keep 29G;

Tablespace altered.

SQL> SELECT file #, name, bytes/1024/1024/1024/as "TEMPFILE (G)" FROM v $ tempfile;

FILE # name tempfile (G)
-----------------------------------------------------------------------
1/oradata/app/oracle/oradata/img/temp2.dbf 29.0009689

Summary:
Oracle 11g online recovery default temporary tablespace function is very powerful (only locally managed temporary tablespaces available: Doc ID 452697.1)
Revoke the default Temporary Tablespace before Oracle 11g :( How to Shrink the Datafile of Temporary Tablespace (Doc ID 273276.1 ))
1. Create a new small default temporary tablespace temp1
2. Run the alter tablespace command to make temp1 the default temporary tablespace.
3. Delete the old default temporary tablespace temp

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)