From the bestselling Oracle10g book, Oracle Database 10g New Features by Mike Ault, Madhu Tumma, and Daniel Liu, published by Rampant TechPress.

Oracle 10g introduces a new term called “temporary tablespace group.”

Temporary Tablespace Group Overview

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 changes 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 RENAME old_tbs 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. The bigfile tablespace 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.

 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.

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.

--

Mike Ault is one of the leading names in Oracle technology. The author of more than 20 Oracle books and hundreds of articles in national publications, Mike Ault has five Oracle Masters Certificates and was the first popular Oracle author with his book Oracle7 Administration and Management. Mike also wrote several of the “Exam Cram” books, and enjoys a reputation as a leading author and Oracle consultant.

Mike has released his complete collection of more than 450 Oracle scripts, covering every possible area of Oracle administration and management.The collection of 465 scripts is available at: http://www.rampant-books.com/download_adv_mon_tuning.htm.