Tuesday, October 2, 2012

Temporary tablespace group

What We Mean By That ?

A temporary tablespace group consists of only temporary tablespace, and has the following properties:
      • It contains one or more temporary tablespaces.
      • It contains only temporary tablespace.
      • It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group. 

Benefits :

Temporary tablespace group has the following benefits:
      • It allows multiple default temporary tablespaces to be specified at the database level.
      • It allows the user to use multiple temporary tablespaces in different sessions at the same time.
      • It allows a single SQL operation to use multiple temporary tablespaces for sorting. 

How to Know How Much Group you have And Each temp assign to them ? 

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

 How to Create One :


CREATE TEMPORARY TABLESPACE temp01
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP groupaA_temp;
 
Note : you can create temp tablespace without assign to any group .  


Example 

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP ‘’;

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G;
 
Using Alter with them 

Remove from the group : 

 ALTER TABLESPACE temp01 TABLESPACE GROUP ‘‘;

Assign to Group :

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Define Group for specific user :

     ALTER USER Osama TEMPORARY TABLESPACE tempgroup_A;


 Refecnce Link :
1-ORACLE BASE
2-DBA Kevlar


Enjoy
Osama Mustafa

No comments:

Post a Comment