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

The concept and brief description of temporary tablespace groups can be found in the "Oracle Database SQL Reference" (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm#SQLRF01403) of the official Oracle documentation. For more information, please refer to MOS's "10g: Temporary Tablespaces Group [ID 245645.1]" article.

This paper makes a practice on the creation and simple maintenance of temporary tablespace groups.

1. Database version information

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 Linux: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

two。 Create a temporary tablespace group TEMP_GRP that contains two temporary tablespaces TEMP1 and TEMP2

Sys@ora10g > create temporary tablespace TEMP1 tempfile'/ oracle/oradata/ora10g/temp1_01.dbf' size 10m autoextend on tablespace group group1

Tablespace created.

Sys@ora10g > create temporary tablespace TEMP2 tempfile'/ oracle/oradata/ora10g/temp2_01.dbf' size 10m autoextend on tablespace group group1

Tablespace created.

3. Use the DBA_TABLESPACE_GROUPS view to view information about temporary tablespace groups

Sys@ora10g > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP1 TEMP2

As you can see, the temporary tablespace group GROUP1 contains two temporary tablespaces TEMP1 and TEMP2. It meets the requirements of our temporary tablespace group creation.

4. Specifies that the temporary tablespace group GROUP1 is the default temporary tablespace for all newly created users

Sys@ora10g > alter database default temporary tablespace GROUP1

Database altered.

Sys@ora10g > create user secooler identified by secooler

User created.

Sys@ora10g > select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = 'SECOOLER'

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

SECOOLER USERS GROUP1

OK, at which point the default temporary tablespace for the newly created secooler user has been assigned to the temporary tablespace group GROUP1.

5. Move temporary tablespace TEMP2 to temporary tablespace group GROUP2

Sys@ora10g > alter tablespace TEMP2 tablespace group GROUP2

Tablespace altered.

Sys@ora10g > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP2 TEMP2

There is no GROUP2 temporary tablespace group in the system, where the database object is automatically created.

6. The temporary tablespace group is automatically deleted when all temporary tablespaces in the temporary tablespace group are removed

Sys@ora10g > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP2 TEMP2

Sys@ora10g > alter tablespace TEMP2 tablespace group''

Tablespace altered.

Sys@ora10g > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

7. An application example of temporary tablespace group

Sys@ora10g > alter tablespace TEMP2 tablespace group GROUP1

Tablespace altered.

Sys@ora10g > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMP1

GROUP1 TEMP2

Open two different session connections to secooler users and execute the following sort statement

Secooler@ora10g > select a.table_name, b.table_name from all_tables a, all_tables b order by 1

After repeated attempts, you can see that different session of the same user can use different temporary tablespaces in temporary tablespace groups.

Sys@ora10g > select username, session_num, tablespace from v$sort_usage

USERNAME SESSION_NUM TABLESPACE

-

SECOOLER 1506 TEMP1

SECOOLER 3662 TEMP2

8. Summary

Advantages and considerations of using temporary tablespace groups:

1. A temporary tablespace group can contain numerous temporary tablespaces without restriction, which expands the size of temporary tablespaces and ensures sufficient temporary tablespaces.

two。 The name of a temporary tablespace group cannot be the same as an existing temporary tablespace name

3. Temporary tablespace groups are not created explicitly, but implicitly when temporary tablespaces are assigned to temporary tablespace groups. When all temporary table spaces in the temporary table space group are removed or deleted, the temporary table space group is also deleted

4. Multiple sessions connected by the same user can use different temporary tablespaces in the temporary tablespace group to improve system performance.

Good luck.

Secooler

10.07.13

-- The End--

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.