Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Friday, 8 November 2013

Temporary Tablespace Group in Oracle 10g

A tablespace group facilitates to assign multiple temporary tablespaces to a single user and thus increases the addressability capacity of temporary tablespaces. Oracle 10g introduces this concept. You can set this tablesapce group as a default database temporary tablespace group. The benefit is that a single SQL statement may use more than one temporary tablespace. In fact there is no CREATE TABLESPACE GROUP explicitly defined you can create it implicitly during tablesapce creation by specifying the TABLESPACE GROUP clause.
It has the following properties:–
  • It can contain one or more temporary tablespace.
  • It can contain only a single temporary tablesapce.
  • It is not explicitly created. In fact it is created implicitly when the first tablespace assign to it and is deleted the last temp tablesapce removed from the group.
Benefits:
  • It allows the user to use multiple temporary tablesapce in different session at the same time.
  • Multiple Temporary tablespace helps to reduce contention issue.
  • It allows single SQL statement to use multiple temp tablesapces.
  • You can distribute operation across multiple temporary tablesapce.
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE 'D:\oracle\oradata\temp01.dbf' SIZE 10M AUTOEXTEND ON TABLESPACE GROUP temp_group;
Note: If the tablespace group does not already exist then oracle automatically creates it during execution of this statement.
Adding a temporary tablespace to temporary tablespace group:
SQL> ALTER TEMPORARY TABLESPACE temp02 TABLESPACE GROUP temp_group;
Removing a temporary tablespace from temporary tablespace group:
SQL> ALTER TEMPORARY TABLESPACE temp02 TABLESPACE GROUP '';
Assigning temporary tablespace group to a user (same as assigning temporary tablespace to a user):
SQL> ALTER USER hrms TEMPORARY TABLESPACE temp_group;
Assigning temporary tablespace group as default temporary tablespace:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;
To see the tablespaces in the temporary tablespace group:
SQL> Select * from DBA_TABLESPACE_GROUPS;
Related Views:
DBA_TABLESPACE_GROUPS
DBA_TEMP_FILESV$TEMPFILE
V$TEMPSTATV$TEMP_SPACE_HEADER
V$TEMPSEG_USAGE

1 comments:

  1. Quantum Binary Signals

    Professional trading signals delivered to your cell phone daily.

    Start following our signals NOW & gain up to 270% per day.

    ReplyDelete