TEMP Tablespace Concepts

 


Check Temporary tablespace and Tempfile 

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME	FILE_NAME
------------------------------ ------------------------------
TEMP1		/u01/app/oracle/oradata/ORACLEAGENT/temp01.dbf

SQL> select file#,status,name from v$tempfile; 

FILE#      STATUS           NAME
---------- ------- --------------------------------------------------
   1        ONLINE   /u01/app/oracle/oradata/ORACLEAGENT/temp01.dbf


Create a New Temporary Tablespace

SQL> create temporary tablespace India tempfile 
'/u01/app/oracle/oradata/ORACLEAGENT/India01.dbf' size 100m;

Tablespace created.



SQL> select tablespace_name from dba_temp_files;

 TABLESPACE_NAME
------------------------------ 
Italy
India 

Check Property value in Database

SQL> select property_value from database_properties where 
property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
-----------------------
 Italy

SQL> alter database default temporary tablespace India;

 Database altered.

SQL> select property_value from database_properties where 
property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
------------------------

India

Create Temporary Tablespace Group

SQL> create temporary tablespace France
 tempfile '/u01/app/oracle/oradata/ORACLEAGENT/France01.dbf' size 30m 
tablespace group Tempgroup;

Tablespace created.

SQL> alter tablespace India tablespace group tempgroup;

 Tablespace altered.

SQL> alter database default temporary tablespace tempgroup;

 Database altered.

SQL> select property_value from database_properties where 
property_name='DEFAULT_TEMP_TABLESPACE';


PROPERTY_VALUE
----------------------
TEMPGROUP

Check Temporary tablespace size 

SQL> select tablespace_name,TABLESPACE_SIZE,ALLOCATED_SPACE,FREE_SPACE 
from dba_temp_free_space;

TABLESPACE_NAME	  TABLESPACE_SIZE   ALLOCATED_SPACE    FREE_SPACE
--------------- ---------------     ---------------     ----------
INDIA	       524288000	        10485760          517996544
ITALY	       104857600	        2097152           102760448
FRANCE	       31457280	                1048576           30408704

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8