ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 由ORA-01652想到的

由ORA-01652想到的

原创 Linux操作系统 作者:gaopengtttt 时间:2012-10-18 17:49:59 0 删除 编辑

(原创转载请注明)

 

今天遇到了错误ORA-01652: unable to extend temp segment by 128 in tablespace TEMP,当然第一个想到可能是由于PGA中的workarea中的空间不够而转而使用了临时表空间,可能是某些语句产生了大量的SORT或者HASH,大家都知道当进行ORDER BYDISTINCTUNIONGROUP BY的时候可能导致大量的排序,而使用HASH JION的时候或者GROUP (HASH)的时候都会进行HASH,如果遇到这样的问题当然是想办法优化SQL,但是可能使用到临时表空间的地方不止这些,如下对v$sort_usage(这个视图和v$tempseg_usage是等价的)SEGTYPE进行说明

 

SEGTYPE

SORT -----指的是排序

HASH -----指的是HASH

DATA -----指的使用临时表

INDEX ----指在临时表中建立的索引

LOB_DATA –指在程序中使用到临时的LOB

LOB_INDEX-LOB段需要一个索引进行导航

所以引起临时表空间不足的情况并非都是sorthash引起的。对于sorthash已经进行了说明,然后对余下的部分进行说明

1、  临时表

截取一些说明如下:

 

ORACLE的临时表在应用系统中有很大的作用,它可以让用户只能够操作各自的数据中而互不干扰,不用担心会破坏或影响其他SESSION/TRANSACTION的数据,这也是数据安全的一种解决方法。

临时表分为SESSIONTRANSACTION两种,SESSION级的临时表数据在整个SESSION都存在,直到结束此次SESSION;而TRANSACTION级的临时表数据在TRANACTION结束后消失,即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。

建立临时表语法

AON COMMIT DELETE ROWS 定义了建立事务级临时表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

ON COMMIT DELETE ROWS

-----AS SELECT … FROM TABLE…;

当前session发出commit/rollback命令,则该事务周期发生的所有数据自动被Oracle删除(Oracle truncate table)。但不影响任何其他session的数据。

BON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法

CREATE GLOBAL TEMPORARY TABLE TABLE_NAME

-----(COUMNS …)

ON COMMIT PRESERVE ROWS

-----AS SELECT … FROM TABLE…;

当前session结束(用户正常退出 / 用户不正常退出 / Oracle实例崩溃)Oracle对这个会话的中发生的数据进行删除(Oracle truncate table)。但不影响任何其他session的数据。

2. 特点说明

A.临时表数据自动清空后,但是临时表的结构以及元数据还存储在用户的数据字典中。表的定义对所有的会话可见

B.临时表不需要DML

C.可以索引临时表和在临时表基础上建立视图

D.在临时表上的索引也是临时的,也是只对当前会话或者事务有效

E.临时表可以拥有触发器

F.可以用exportimport工具导入导出临时表的定义,但是不能导出数据

我们可以这样去查看系统中有多少临时表和临时索引

select * from user_tables where TEMPORARY='Y';

DURATION= SYS$SESSION说明其是会话级临时表

DURATION= SYS$TRANSACTION说明其实事物级的临时表

同理可以通过如下语句查看系统中有那些临时索引

select * from user_indexes where TEMPORARY='Y';

而其DURATION也是一样的道理。

实验:

1、  建立一个事物临时表

 SQL> CREATE GLOBAL TEMPORARY TABLE test

  2  ON COMMIT DELETE ROWS

3  AS SELECT *  FROM dba_tables;

这里基于事物虽然是SELECT* 但是DDL一旦这个表建立完成事物就结束了,所以表是空的

  2、建立索引

 SQL> create index test_i

on test(owner);

3、插入数据不要结束事物

   SQL> insert into test

     2  select * from dba_tables;

  4、进行查看对象

SQL> select table_name,TEMPORARY,DURATION  from user_tables where TEMPORARY='Y';

 

TABLE_NAME                     TEMPORARY DURATION

------------------------------ --------- ---------------

TEST                           Y         SYS$TRANSACTION

 

SQL> select table_name,TEMPORARY,DURATION  from user_indexes where TEMPORARY='Y';

 

TABLE_NAME                     TEMPORARY DURATION

------------------------------ --------- ---------------

TEST                           Y         SYS$TRANSACTION

5、查看临时空间使用情况

 SQL> select username,user,SESSION_ADDR,TABLESPACE,EXTENTS,BLOCKS,SEGTYPE from v$sort_usage;

 

USERNAME                       USER                           SESSION_ADDR TABLESPACE                         EXTENTS     BLOCKS SEGTYPE

------------------------------ ------------------------------ ------------ ------------------------------- ---------- ---------- ---------

PPZHU                          PPZHU                          30EF50C0     TEMP1                                    1        128 DATA

PPZHU                          PPZHU                          30EF50C0     TEMP1                                    1        128 INDEX

这里可以看到很多信息,session_addr可以找到SESSIONblocks可以查看当前使用分配了多少个块,EXTENTS可以找到当前使用了多个区,明显这里也能找到SQL_ID从而找到问题的根源,我的列子中没有给出SQL_ID字段,因为我使用了PLSQL(无语)但是如果是PLSQL这里就是SQL_ID 9m7787camwh4m sql是(begin :id := sys.dbms_transaction.local_transaction_id; end

如下:

SQL> select username,user,SESSION_ADDR,TABLESPACE,EXTENTS,BLOCKS,SEGTYPE,SQL_ID from v$sort_usage;

 

USERNAME                       USER                           SESSION_ADDR TABLESPACE                         EXTENTS     BLOCKS SEGTYPE   SQL_ID

------------------------------ ------------------------------ ------------ ------------------------------- ---------- ---------- --------- -------------

OGG                            SYS                            442F7628     TEMP1                                    1        128 LOB_DATA  gtcy5x286huzb

OGG                            SYS                            44303130     TEMP1                                    1        128 LOB_DATA  bcahz4tdb4vwk

SYS                            SYS                            442EF33C     TEMP1                                    1        128 DATA      gmz9cp5g52zyg

有了SQL_ID你就能找到相应的SQL了,这里OGG也用了TEMP1(^_^)

select * from v$sql where sql_id='gmz9cp5g52zyg';

 

同时这里的SEGFILE#,SEGBLK#表示这个临时对象开始的区域

我这里是

SQL> select SEGTYPE,SEGFILE#,SEGBLK# from v$sort_usage;

 

SEGTYPE     SEGFILE#    SEGBLK#

--------- ---------- ----------

DATA             202        393

INDEX            202        265

202是因为TEMPFILEDATAFILE+1,参数db_files的大小是200

所以我这里表的第一个块开始是tempfile id=202-200 开始的块是393

而所以是也是文件2开始块是265,但是如果这个文件不够就可能使用另外的临时文件。

使用v$temp_extent_map可以找到对应的开始区。但是我始终没找到办法能找到对应的对象,v$temp_extent_map中的OWNER是那个实例拥有这个区,在RAC中可以反映出来,如果是0及代表没有使用,但是已经分配了的区。

下面是RAC中的测试

SQL> select OWNER,count(*) from v$temp_extent_map group by owner;

 

     OWNER   COUNT(*)

---------- ----------

         1         13

         2        116

 

 未完 待续

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

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

注册时间:2008-10-13

  • 博文量
    645
  • 访问量
    2840261