Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


Temporary tablespace group tips

OracleTips by Burleson Consulting

Updated May 28, 2015

by Mike Ault

Also see how to view temp tablespace segments for specific sessions.

Temporary Tablespace Group Overview

Oracle 10g first introduced "temporary tablespace group."  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.

Temporary Tablespace 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.

New Data Dictionary View

Oracle 10g introduces a new data dictionary view, dba_tablespace_groups, for the temporary tablespace group. Using a tablespace with a temporary tablespace group will result in the following select statement. However, using a tablespace without a temporary tablespace group will not return the select statement below.

select 
   tablespace_name, group_name
from 
   DBA_TABLESPACE_GROUPS;

TABLESPACE_NAME GROUP_NAME
--------------- -----------
TEMP01         TEMPGROUP_A
TEMP02         TEMPGROUP_A
TEMP03         TEMPGROUP_B

Examples

Example 1: Create a temporary tablespace and implicitly add it to a temporary tablespace group.

CREATE TEMPORARY TABLESPACE temp01
TEMPFILE /u02/oradata/temp01.dbs' SIZE 500M
TABLESPACE GROUP tempgroup_a;

Example 2: Create a temporary tablespace without assigning it to a temporary tablespace group.

The following two statements are exactly equivalent:

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE /u02/oradata/temp04.dbs' SIZE 200M
TABLESPACE GROUP ';

CREATE TEMPORARY TABLESPACE temp04
TEMPFILE /u02/oradata/temp04.dbs' SIZE 200M;

Example 3: Remove a temporary tablespace from a temporary tablespace group.

This statement will remove temporary tablespace temp04 from its original temporary tablespace group:

ALTER TABLESPACE temp04 TABLESPACE GROUP ;

Example 4: Add a temporary tablespace to a temporary tablespace group.

ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;

Example 5: Assign a user to a default temporary tablespace group.

In this case, user Scott will have multiple default temporary tablespaces (see Figure 3.1). A single SQL operation by Scott can use more than one temporary tablespace for sorting.

ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;


Figure 3.1: Temporary tablespace group.


Rename Tablespace

Another great new feature in tablespace management is rename tablespace.

Tablespace Rename Overview

In Oracle 10g, you can simply rename a tablespace TBS01 to TBS02 by issuing the following command:

ALTER TABLESPACE tbs01 RENAME TO tbs02;

However, you must follow the rules when renaming a tablespace:

  • You must set compatibility level to at least 10.0.1.
  • You cannot rename the SYSTEM or SYSAUX tablespaces.
  • You cannot rename an offline tablespace.
  • You cannot rename a tablespace that contains offline datafiles.
  • Renaming a tablespace does not change its tablespace identifier.
  • Renaming a tablespace does not change the name of its datafiles.

Tablespace Rename Benefits

Tablespace rename provides the following benefits:

  • It simplifies the process of tablespace migration within a database.
  • It simplifies the process of transporting a tablespace between two databases.

Examples

Example 1: Rename a tablespace within a database. In Oracle9i or earlier releases, you must take the following steps to rename a tablespace from OLD_TBS to NEW_TBS:

  1. Create a new tablespace NEW_TBS.
  2. Copy all objects from OLD_TBS to NEW_TBS.
  3. Drop tablespace OLD_TBS.

In Oracle 10g, you can accomplish the same thing in one step:

  • Rename tablespace OLD_TBS to NEW_TBS.

ALTER TABLESPACE old_tbs RENAME TO new_tbs;

Example 2: Transport a tablespace between two databases. In the following example (see figure 3.2), you cannot transport a tablespace TBS01 from database A to database B in the previous release of Oracle server because database B also has a tablespace called TBS01. In Oracle 10g, you can simply rename TBS01 to TBS02 in database B before transporting tablespace TBS01.

Figure 3.2: Rename tablespace for tablespace transport.


Bigfile Tablespace

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview

The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. An Oracle database using one or more bigfile tablespaces has the following characteristics:

  • An Oracle database can contain both bigfile and smallfile tablespaces.
  • System default is to create the traditional smallfile tablespace.
  • The SYSTEM and SYSAUX tablespaces are always created using the system default type.
  • Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

There are two exceptions when bigfile tablespace segments are manually managed:

  • Locally managed undo tablespace
  • Temporary tablespace

Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.

However, you can also use it without ASM.

Bigfile Tablespace Benefits

Bigfile tablespace has the following benefits:

  • It simplifies large database tablespace management by reducing the number of datafiles needed.
  • It simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
  • It allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
  • It follows the concept that a tablespace and a datafile are logically equivalent.

Maximum Database Size

The BFT extended the maximum size of tablespace and database. Let's take a look at the two formulas that calculate the maximum size of data file and database.

The maximum data file size is calculated by:

Maximum datafile size = db_block_size * maximum number of blocks

The maximum amount of data in an Oracle database is calculated by:

Maximum database size = maximum datafile size * maximum number of datafile

The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.

The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.

BLOCK SIZE

MAXIMUM DATA FILE SIZE

MAXIMUM DATABASE SIZE

32 K

128 GB

8,388,608 GB

16 K

64 GB

4,194,304 GB

8 K

32 GB

2,097,152 GB

4 K

16 GB


1,048,579 GB

2 K

8 GB

524,288 GB

Table 3.1: Maximum database size in Oracle9i.

The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.

BLOCK SIZE

MAXIMUM DATA FILE SIZE

MAXIMUM DATABASE SIZE

32 K

131,072 GB

8,589,934,592 GB

16 K

65,536 GB

4,294,967,296 GB

8 K

32,768 GB

2,147,483,648 GB

4 K

16,384 GB


1,073,741,824 GB

2 K

8,192 GB

536,870,912 GB

Table 3.2: Maximum database size in Oracle 10g.

As you can see, with the new BFT addressing scheme, Oracle 10g can contain astronomical amounts of data within a single database.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational