Shulou(Shulou.com)06/01 Report--

Oracle10g new features temporary tablespace group example analysis, in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Prior to oracle10g, each user could only use one temporary tablespace, which is its default temporary tablespace. Oracle10g provides the function of a new temporary tablespace group, which allows you to package multiple temporary tablespaces into a group, and then specify that the user's default temporary tablespace is the temporary tablespace group, thus achieving the goal that a user can use multiple temporary tablespaces.

A temporary tablespace group contains at least one temporary tablespace.

A temporary tablespace group cannot be created explicitly and is automatically created when the first temporary tablespace is assigned to the group and deleted when all temporary tablespaces in the group are removed.

Operations supported by temporary tablespace groups:

Temporary tablespaces can be moved from one group to another (automatically created if the group does not exist).

Temporary tablespaces in a group can be removed from the group.

You can add temporary tablespaces that do not currently belong to any group to a group.

Benefits of using temporary tablespace groups:

Prevent a temporary tablespace from running out of space.

Different temporary table spaces can be used when the same user connects multiple session at the same time.

Multiple temporary tablespaces can be used in parallel operations.

Here are a few examples:

Environment

SQL > select * from v$version

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

1. Create a temporary tablespace group

SQL > create temporary tablespace temp1

2 tempfile'elux size oracle size 10m

3 tablespace group group1

The tablespace has been created.

SQL > create temporary tablespace temp2

2 tempfile ''eVuGUA size oracleUnix size 10m

3 tablespace group group1

The tablespace has been created.

two。 View information about temporary tablespace groups that currently exist in the system

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP1 TEMP2

3. Move temporary tablespaces to a new temporary tablespace group

SQL > alter tablespace temp2 tablespace group group2

The tablespace has changed.

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP2 TEMP2

4. Remove temporary tablespaces from the temporary tablespace group

SQL > alter tablespace temp2 tablespace group''

The tablespace has changed.

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

5. The name of a temporary table space group cannot conflict with the name of a table space.

SQL > create temporary tablespace temp3

2 tempfile''eVOREGUR size oracleUnix oradataAccord temp301.dbf' oradata

3 tablespace group temp3

Create temporary tablespace temp3

*

An error occurred on line 1:

ORA-10918: TABLESPACE GROUP name cannot be the same as tablespace name

SQL > create temporary tablespace temp3

2 tempfile'elux size oracle size 10m

3 tablespace group temp1

Create temporary tablespace temp3

*

An error occurred on line 1:

ORA-01543: tablespace 'TEMP1' already exists

6. When all temporary tablespaces in the group are deleted, the group is automatically deleted

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

SQL > drop tablespace temp1 including contents and datafiles

The tablespace was deleted.

SQL > select * from dba_tablespace_groups

No rows selected

7. You can specify a database or a user's default temporary tablespace as a temporary tablespace group

SQL > create temporary tablespace temp1

2 tempfile''e _ size _ size _ 10m

3 tablespace group group1

The tablespace has been created.

SQL > alter tablespace temp2 tablespace group group1

The tablespace has changed.

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP1 TEMP2

SQL > alter database default temporary tablespace group1

The database has changed.

SQL > alter user ning temporary tablespace group1

The user has changed.

8. Different session of the same user can use different temporary tablespaces

First, use sys users to query

SQL > select username, session_num, tablespace from v$sort_usage

No rows selected

New session1 execution

SQL > conn ning/ning

Connected.

Select a.table_name, b.table_name from dict A, dict B order by a.table_name

New session2 execution

SQL > conn ning/ning

Connected.

Select a.table_name, b.table_name from dict A, dict B

Sys query:

SQL > select username, session_num, tablespace from v$sort_usage

USERNAME SESSION_NUM TABLESPACE

NING 74 TEMP2

NING 62 TEMP1

You can see that two session of the same user ning use two different temporary tablespaces.

This is the answer to the sample analysis question about the temporary tablespace group, which is the new feature of Oracle10g. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.