Oracle10g New Feature -- 5.Temporary Tablespace Group
Oracle10g引入了临时表空间组的概念，和单个的临时表空间相比，引入组以后允许数据库指定多个默认的临时表空间，同时相同用户的不同会话可以使用不同的临时表空间，并且单个SQL运行时可以利用多个临时表空间进行排序（自动调用多个临时表空间吗？还无从得知）。
目前感觉这个新特性作用并不是很大，主要是以前temp表空间出的问题不多。不知道真正用起来时如何。
创建组、增删表空间，指定组都是相对简单的操作
[@more@]Temporary Tablespace Group
· Create temporary tablespace group implicitly
SQL>create temporary tablespace LMTEMP1 tempfile 'D:ORACLE10ORCLtemp1_01.dbf' size 50M tablespace group GROUP1;
· Add a tablespace to group
SQL>create temporary tablespace lmtemp2 tempfile 'D:ORACLE10ORCLtemp1_02.dbf' size 2M tablespace group group1;
· Determine temporary tablespace groups SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
-------------------- ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
· Remove a temporary tablespace from a temporary tablespace group. SQL>ALTER TABLESPACE LMTEMP1 TABLESPACE GROUP ‘‘;
· Change temporary tablespace groups SQL> alter tablespace LMTEMP1 tablespace group GROUP2 ;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2 LMTEMP1
GROUP1 LMTEMP2
· Assign a user to a default temporary tablespace group SQL> ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;
· Set the group as the database default temporary tablespace SQL>alter database default temporary tablespace group1;
Database altered.
SQL>select * from database_properties where property_name like 'DEFAULT_TEMP%'
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- ---------------------------- ---------------------
DEFAULT_TEMP_TABLESPACE GROUP1 Name of defaulttemporary tablespace
· Determine which temporary tablespaces were used SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -----------
ZHYUH 387 TEMP
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
-------------------- ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
· Remove a temporary tablespace from a temporary tablespace group. SQL>ALTER TABLESPACE LMTEMP1 TABLESPACE GROUP ‘‘;
· Change temporary tablespace groups SQL> alter tablespace LMTEMP1 tablespace group GROUP2 ;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2 LMTEMP1
GROUP1 LMTEMP2
· Assign a user to a default temporary tablespace group SQL> ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;
· Set the group as the database default temporary tablespace SQL>alter database default temporary tablespace group1;
Database altered.
SQL>select * from database_properties where property_name like 'DEFAULT_TEMP%'
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- ---------------------------- ---------------------
DEFAULT_TEMP_TABLESPACE GROUP1 Name of defaulttemporary tablespace
· Determine which temporary tablespaces were used SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -----------
ZHYUH 387 TEMP
SQL> alter tablespace LMTEMP1 tablespace group GROUP2 ;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2 LMTEMP1
GROUP1 LMTEMP2
· Assign a user to a default temporary tablespace group SQL> ALTER USER scott TEMPORARY TABLESPACE tempgroup_A;
· Set the group as the database default temporary tablespace SQL>alter database default temporary tablespace group1;
Database altered.
SQL>select * from database_properties where property_name like 'DEFAULT_TEMP%'
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- ---------------------------- ---------------------
DEFAULT_TEMP_TABLESPACE GROUP1 Name of defaulttemporary tablespace
· Determine which temporary tablespaces were used SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -----------
ZHYUH 387 TEMP
SQL>alter database default temporary tablespace group1;
Database altered.
SQL>select * from database_properties where property_name like 'DEFAULT_TEMP%'
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- ---------------------------- ---------------------
DEFAULT_TEMP_TABLESPACE GROUP1 Name of defaulttemporary tablespace
· Determine which temporary tablespaces were used SQL> select username, session_num, tablespace from v$sort_usage;
USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -----------
ZHYUH 387 TEMP
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/207/viewspace-778740/ ，如需转载，请注明出处，否则将追究法律责任。
