Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Database Administration / Oracle Temporary Tablespace

Oracle Temporary Tablespace

Summary: in this tutorial, you will learn about the Oracle temporary tablespaces and how to manipulate the temporary tablespaces effectively.

Introduction to Oracle temporary tablespaces

A temporary tablespace, as its name implied, stores the temporary data that only exists during the database session.

Oracle uses temporary tablespaces to improve the concurrency of multiple sort operations which do not fit in memory. On top of this, Oracle stores temporary tables, temporary indexes, temporary B-trees, and temporary LOBs in temporary tablespaces.

By defeault, Oracle creates a single temporary tablespace named TEMP for each new Oracle Database installation. This TEMP tablespace can be shared by multiple users.

Besides the TEMP default temporary tablespace, you can create addition temporary tablespaces and assign them to a user using the CREATE USER or ALTER USER statement.

Oracle default temporary tablespace

When you create a user without specifying a temporary tablespace, Oracle assigns the default temporary tablespace TEMP to user. If you want to change the default temporary tablespace, you can use the following command:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To find the current default temporary tablespace, you execute the following statement:

SELECT property_name, property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';

Here is the output:

Oracle default temporary tablespace

Viewing space allocation in a temporary tablespace

This statement returns the space allocated and free space in a temporary tablespace:

SELECT * FROM dba_temp_free_space;

Creating a temporary tablespace

To create a new temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement:

CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE 'path_to_file' SIZE size;

Oracle default tablespace examples

First, create a new temporary tablespace named temp2 with the size of 100MB:

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE 'temp2.dbf' SIZE 100m;

Next, find all temporary tablespaces in the current Oracle Database:

SELECT tablespace_name, file_name, bytes/1024/1024 MB, status FROM dba_temp_files;
temporary tablespaces

Then, check which tablespace is the default temporary tablespace:

SELECT property_name, property_value FROM database_properties WHERE property_name='DEFAULT_TEMP_TABLESPACE';
Oracle default temporary tablespace

After that, change the default temporary tablespace name to temp2:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Finally, drop the temp2 tablespace:

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Oracle issued the following error:

SQL Error: ORA-12906: cannot drop default temporary tablespace

You cannot drop the default temporary tablespace. To delete the temp2 tablespace as the default temporary tablespace, you must first change the default tablespace back to the TEMP tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

And then drop the temp2 temporary tablespace:

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

In this tutorial, you have learned how about the Oracle temporary tablespaces and how to manipulate them effectively.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Extend Tablespace
Next Oracle Tablespace Group

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.