How to create temporary tablespace 11g
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.
- Check the existing temporary tablespace.
- Create a new temporary tablespace.
- Make the newly created temporary tablespace as default.
- Kill the existing sessions which are using existing temporary tablespace.
- 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.