In this post, we will discuss the step by step guide to create a temporary tablespace and drop existing temporary tablespace.

Steps to create a new temporary tablespace.

1. Check the existing temporary tablespace.
2. Create a new temporary tablespace.
3. Make the newly created temporary tablespace as default.
4. Kill the existing sessions which are using existing temporary tablespace.
5. Drop the existing temporary tablespace.

how to check temp tablespace size in Oracle 11g

SYS>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME                                                            TABLESPACE_NAME

—————————-                                                    ——————-

/u01/TEST/temp01.dbf                                                                     TEMP

 

How to create temporary tablespace 11g

SYS> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE ‘/u01/TEST/temp02.dbf’ SIZE 100M  ;

Tablespace created.

Make the newly created tablespace as default temp tablespace

SYS>  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

how to check default temporary tablespace in Oracle 11g

SYS> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME =’DEFAULT_TEMP_TABLESPACE’;

PROPERTY_VALUE

——————————————————————————–

TEMP2

Get ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.

SYS> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,

a.username,a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr; 

no rows selected

 

If you get any results, kill the sessions

SQL> alter system kill session ‘SID_NUMBER, SERIAL#NO’;

For example:

SQL> alter system kill session ‘123,123456’;

Now, we can drop old temporary tablespace

SYS> drop tablespace temp  including contents and datafiles;

Tablespace dropped.

 

SYS>

Find Temporary tablespace size

select tablespace_name,sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

SYS> select tablespace_name,sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES_USED/1024/1024)

—————————— ————————-

SUM(BYTES_FREE/1024/1024)

————————-

TEMP2                                                  2

98

Some of the important data dictionary views for temporary tablespace are

 

DBA_DATA_FILES

DBA_TEMP_FILES

V$TEMPFILE

DBA_FREE_SPACE

V$TEMP_SPACE_HEADER

DBA_TABLESPACE_GROUPS

 

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.