Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand the Oracle temporary tablespace group". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Prior to oracle10g, each user could only use one temporary tablespace, which is its default temporary tablespace. Oracle10g and later versions provide 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.

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 end of "how to understand the Oracle temporary tablespace group". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.