签到成功

知道了

CNDBA社区CNDBA社区

Oracle Temporary Tablespaces Group 说明

2016-09-06 11:02 3348 0 原创 Oracle 11g
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/231


Oracle Temporary Tablespaces Group 是Oracle 10g 引入的特性， 在VLDB或DSS 系统中可以配置。 
 
 
一.temporary tablespace group说明

1）.A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces.
2）It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.
3）A temporary tablespace group name can appear where a tablespace name would appear when assigning a default temporary tablespace for the database or assigning a temporary tablespace for a user.
4）It is not created explicitly. It gets created implicitly when the first temporary tablespace is assigned to it and it is dropped when the last temporary tablespace is removed from it.
5）Any temporary tablespace can be
    moved from a group to another (if the group does not exist, it gets created)
    removed from a group
    added to a group if standalone
6）The temporary tablespace group helps to
    avoid the problem where one temporary tablespace is inadequate to hold the results of a sort, particularly on a partition table.
    to use different temporary tablespaces when a user connects in multiple sessions simultaneously.
    enable parallel execution servers in a single parallel operation to use multiple temporary tablespaces.


二.示例

1.http://www.cndba.cn/cndba/dave/article/231

SQL> create temporary tablespace LMTEMP 1
tempfile 'D:/ORACLE10/ORCL/temp1_01.dbf' size 50M
tablespace group GROUP1;
The group GROUP1 did not exist before the creation of the new temporary tablespace LMTEMP1. LMTEMP1 is the first temporary tablespace to belong to this group.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP1



2.http://www.cndba.cn/cndba/dave/article/231

SQL> create temporary tablespace lmtemp2
tempfile 'D:/ORACLE10/ORCL/temp1_02.dbf' size 2M
tablespace group group1;

Tablespace created.

A new temporary tablespace LMTEMP2 is added to the existing group GROUP1.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2



3. A temporary tablespace can be moved to a new or another group:http://www.cndba.cn/cndba/dave/article/231

http://www.cndba.cn/cndba/dave/article/231
http://www.cndba.cn/cndba/dave/article/231 

SQL> alter tablespace LMTEMP1 tablespace group GROUP2 ;
Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2 LMTEMP1
GROUP1 LMTEMP2



4. A temporary tablespace can be removed from a group so as to be standalone;

SQL> alter tablespace LMTEMP1 tablespace group '';
Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP2

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME 
------------------------------ 
TEMP 
LMTEMP1 
LMTEMP2

Of course, this standalone temporary tablespace can be re-incorporated to a group :

SQL> alter tablespace LMTEMP1 tablespace group GROUP1;
Tablespace altered.



5. 
http://www.cndba.cn/cndba/dave/article/231

http://www.cndba.cn/cndba/dave/article/231 

SQL> create temporary tablespace lmtemp5 
tempfile 'D:/ORACLE10/ORCL/temp1_05.dbf' size 50M
tablespace group lmtemp5;
create temporary tablespace lmtemp5
*
ERROR at line 1:
ORA-10918: TABLESPACE GROUP name cannot be the same as tablespace name

As the namespace is shared, the group name cannot be same as tablespace name.



6. If you drop all temporary tablespaces from GROUP1, the group is automatically removed:http://www.cndba.cn/cndba/dave/article/231

SQL> create temporary tablespace LMTEMP3
tempfile 'D:/ORACLE10/ORCL/temp1_03.dbf' size 2M
tablespace group GROUP2;

Tablespace created.

SQL> create temporary tablespace LMTEMP4
tempfile 'D:/ORACLE10/ORCL/temp1_04.dbf' size 2M
tablespace group GROUP2;

Tablespace created.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
GROUP2 LMTEMP3
GROUP2 LMTEMP4

SQL> drop tablespace lmtemp3 including contents and datafiles;
Tablespace dropped.

SQL> drop tablespace lmtemp4 including contents and datafiles;
Tablespace dropped.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2


7. A default temporary tablespace group can be assigned to a user:

SQL> alter user scott temporary tablespace GROUP1;
User altered


 
8. A temporary tablespace group can also be assigned as the default temporary tablespace group at the database level :

http://www.cndba.cn/cndba/dave/article/231 

SQL> alter database <db_name> default temporary tablespace GROUP1;
Database altered.
Now, any of the temporary tablespaces belonging to the default database temporary tablespace group cannot be dropped :

SQL> drop tablespace LMTEMP2 including contents and datafiles;
drop tablespace lmtemp2 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace
group


9. A user can benefit from this new feature , using different temporary tablespaces while connected under distinct sessions and performing several sort operations simultaneously :

-- DBA Session --

SQL> select username, session_num, tablespace from v$sort_usage;
no rows selected
-- SCOTT Session 1 --

SQL> select a.table_name, b.table_name from dict A, dict B;
-- SCOTT Session 2 simultaneously--

SQL> select a.table_name, b.table_name from dict A, dict B;
-- DBA Session --

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

USERNAME SESSION_NUM TABLESPACE
------------------------------ ----------- -------------------------------
SCOTT 97 LMTEMP2
SCOTT 150 LMTEMP1

User SCOTT is sorting on 2 different temporary tablespaces.



ORA-10921 Error

ORA-10921 error is reported if we try to drop any of the temporary tablespaces belonging to the default database temporary tablespace group.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1 LMTEMP1
GROUP1 LMTEMP2
GROUP2 LMTEMP3
GROUP2 LMTEMP4

SQL> alter database <db_name> default temporary tablespace GROUP1;
Database altered.

SQL> drop tablespace LMTEMP2 including contents and datafiles;
drop tablespace lmtemp2 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace
group



The solution will be either to drop the tablespace group or to remove the tablespace from the group.

SQL> alter tablespace LMTEMP2 tablespace group '';
Tablespace altered.
The tablespace can now be dropped

SQL> drop tablespace lmtemp2 including contents and datafiles;
Tablespace dropped.


http://www.cndba.cn/cndba/dave/article/231

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问：8186804次
  • 积分：4428
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ