ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Tablespace Group usage and bug 5455880

Tablespace Group usage and bug 5455880

原创 Linux操作系统 作者:licheng79 时间:2011-09-09 10:36:14 0 删除 编辑

Just for a technical hint, we can use temporary tablespace group in place of singe temporary tablespacce to be dynamic management of temporary space. 

 

Using a tablespace group for temporary tablespace management

 

Using temporary tablespace group for version<11 should be caution of BUG 5455880, marked in METALINK.

 

Creating a Tablespace Group

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'

     SIZE 50M

     TABLESPACE GROUP group1;

 

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group

You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.

The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'

     SIZE 25M

     TABLESPACE GROUP group1;

 

The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

 

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.

You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

 

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace

Use the ALTER DATABASE...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

 

Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.

If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.

 

Reference:

1 query $sort_usage, $session, $sqltext for top SQL using temporary tablespace.

2 BUG 5455880 (reference Eygle's blog):  

Affected version Oracle 10.2.0.3, 10.1.0.5 (confirmed), version<11 (believed)

Top SQL impacted performance: select min(bitmapped) from ts$ where dflmaxext:1 and bitabt(flag, 1024)=1024 

When using a tablespace group as the temporary group excessive recursive queried against TS$ can impact performance.

3 excessive space allocation

resize temporary tablespace data file for excessive space allocation:

alter database tempfile '/data/xxx.tmp' resize 20G;

alter tablespace temp shrink space keep 100M;

4 v$tempseg_usage relace of v$sort_usage

Img318781632.jpg

Img319271529.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-707015/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    69809