Temporary Tablespace managed in Oracle

Temporary Tablespace managed in Oracle

Temporary tablespace is used for sorting operation in Oracle database during select queries operations. Cannot have objects in it permanently its all temporary stored for operations.

Create Syntax of Temporary tablespace

CREATE TEMPORARY TABLESPACE temp TEMPFILE 'E:\oradata\orcl\temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

Make Default Temporary Tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Check the temporary tablespace

Select file_name, tablespace_name from dba_temp_files;

Altering the Temporary tablespace

ALTER TABLESPACE temp ADD TEMPFILE 'E:\oradata\orcl\temp02.dbf' SIZE 2M;

Offline the temporary tablespace

ALTER TABLEPSACE TEMPFILE 'E:\oradata\orcl\temp01.dbf' OFFLINE;

Online the temporary tablespace

ALTER TABLEPSACE TEMPFILE 'E:\oradata\orcl\temp01.dbf' ONLINE;

Drop the existing temporary tablespace datafile

ALTER DATABASE TEMPFILE 'E:\oradata\orcl\temp02.dbf' DROP INCLUDING DATAFILES;

Assign default temporary tables based on users

ALTER USER user1 TEMPORARY TABLESPACE temp;

Drop temporary tablespace

DROP TABLESPACE temp;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.