How To Use Temporary Tablespaces

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables.

Each database should have one temporary tablespace that is created when the database is created. You can create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands.

Allocate temporary tablespace to each user in the daabase, so we can avoid from sort space in the System tablespace.

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;


You can remove a TEMPFILE from a database.

SQL> ALTER DATABASE TEMPFILE '/db2/oradata/dev/data/temp02.dbf' DROP INCLUDING DATAFILES;

If you remove all tempfiles from a temporary tablespace, you may encounter error:
ORA-25153: Temporary Tablespace is Empty. So add a TEMPFILE to a temporary tablespace:

SQL>ALTER TABLESPACE temp ADD TEMPFILE '/db2/oradata/dev/data/temp002.dbf' SIZE 200M;

SQL> ALTER TABLESPACE temp OFFLINE

SQL> DROP TABLESPACE temp;

HOW TO create Temporary Tablespaces?

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/db2/oradata/dev/data/temp01.dbf' SIZE 3000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Add Temp Datafiles

SQL> ALTER TABLESPACE temp
ADD TEMPFILE '/db2/oradata/dev/data/temp02.dbf' SIZE 2000M REUSE;

How to Set Default Temporary Tablespaces

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Monitoring Temporary Tablespaces and Sorting

Ttempfiles are not listed in V$DATAFILE and DBA_DATA_FILES
Use V$TEMPFILE and DBA_TEMP_FILES.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP 328204288 1819279360
TEMP 332398592 1815085056
TEMP 317718528 1829765120

2 comments:

Microsoft Office 2007 said...

NEVERTHELESS, THE CIVIL LAW is and must be neutral about who has a more noble or rewarding faith. The breakaway parishes ought to win every Office 2010facet of the lawsuit not becauseMicrosoft Office 2010 their beliefs or their politics are better, Microsoft wordbut because both lawOffice 2007and equity, along with common sense, are on Microsoft Officetheir side.Microsoft Office 2007 Not only does Virginia state law (the Division Statute)Office 2007 keyexplicitly apply to just such a Office 2007 downloadsituation as now exists, but the history Office 2007 Professionalespecially of The Falls Church argues against the claims of Outlook 2010the Virginia Diocese with which theyMicrosoft outlookhave disassociated.Microsoft outlook 2010First, The Falls Church wasWindows 7 founded, formed, and developed long before the diocese, or the national Episcopal Church, even existed.

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.