oracle12c收缩临时表空间,Oracle性能优化：收缩临时表空间

1、查看当前临时表空间的情况

SQL> select * from v$version where rownum<2;

BANNER

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

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> @temp_sort_segment

+==================================================================================+

| Segment Name            : The segment name is a concatenation of the             |

|                           SEGMENT_FILE (File number of the first extent)         |

|                           and the                                                |

|                           SEGMENT_BLOCK (Block number of the first extent)       |

| Current Users           : Number of active users of the segment                  |

| Total Temp Segment Size : Total size of the temporary segment in MB              |

| Currently Used Bytes    : Bytes allocated to active sorts                        |

| Extent Hits             : Number of times an unused extent was found in the pool |

| Max Size                : Maximum number of MB ever used                         |

| Max Used Size           : Maximum number of MB used by all sorts                 |

| Max Sort Size           : Maximum number of MB used by an individual sort        |

| Free Requests           : Number of requests to deallocate                       |

+==================================================================================+

-->此时临时表空间go_temp中达到了32GB

Tablespace  Segment Current Currently Pct.   Extent      Max Max Used Max Sort     Free

Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests

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

TEMP           SYS.0.0        4         4    2    1,864      217      217      217        0

GO_TEMP        SYS.0.0        0         0    0    1,305   32,766      367      367        0

**************          ------- ---------      -------- -------- -------- -------- --------

sum                           4         4         3,169   32,983      584      584        0

SQL> col tbsname format a15

SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status

2  from v$tablespace s,v$tempfile t

3  where s.ts# = t.ts#;

TBSNAME         NAME                                                       MB STATUS

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

TEMP            /u02/database/ORADB/temp/tempORADB.dbf                  235 ONLINE

GO_TEMP         /u02/database/ORADB/temp/ORADB_tempORADB.dbf          32767 ONLINE

SQL> @temp_usage2      -->此时temp已使用的为4MB，而GO_TEMP未使用

TABLESPACE             MB_TOTAL    MB_USED    MB_FREE

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

GO_TEMP                 32767          0      32767

TEMP                      218          4        214

2、观察及分析临时表空间的耗用情况

SQL> select count(*) from big_table;    -->开启另一个session

COUNT(*)

----------

2000000

SQL> select * from big_table order by 2,3,4,5,7,8 desc;  -->对big_table 实施排序

SQL> alter index pk_stock_tbl_arc rebuild;      -->开启另一个session重建索引

SQL> @temp_sort_segment.sql           -->可以看到此时temp表空间耗用达到234MB,go_temp的耗用达到375MB

Tablespace  Segment Current Currently Pct.   Extent      Max Max Used Max Sort     Free

Name     Name   Users   Used MB Used     Hits  Size MB  Size MB  Size MB Requests

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

TEMP           SYS.0.0        4       234    2    2,077      234      234      230        0

GO_TEMP        SYS.0.0        1       375    1    2,055   32,766      375      375        0

**************          ------- ---------      -------- -------- -------- -------- --------

sum                           5       609         4,132   33,000      609      605        0

SQL> @temp_sort_users.sql  -->获得当前排序的会话

INST_ID SID_SERIAL Username   OSUSER          SPID         MODULE     PROGRAM       MB_USED TABLESPACE STATEMENTS

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

1 1064,9259  SCOTT      oracle          14456        SQL*Plus  oracle@SZD        234 TEMP                4

B (TNS V1-

V3)

1 1073,5166  GO_ADMIN   oracle          2480         SQL*Plus  oracle@SZD        375 GO_TEMP           1

B (TNS V1-

V3)

3、使用resize，缩小临时表空间，如不能缩小，转到下一步

SQL> SELECT 'alter database tempfile ''' || a.name || ''' resize ' || b.siz || 'M;'  resize_command

2  FROM v$tempfile a

3        ,(SELECT ceil(tmsize.maxblk * bk.value / 1024 / 1024) siz

4       FROM (SELECT nvl(MAX(segblk#), 128) maxblk

5            FROM v$sort_usage) tmsize

6             ,(SELECT VALUE

7            FROM v$parameter

8            WHERE NAME = 'db_block_size') bk) b;

RESIZE_COMMAND

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

alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;

alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;

-->实际上此时占用32GB的临时数据文件已经缩小

alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;

Database altered.

-->为便于演示，此时假定TEMP为过大的临时表空间且不能释放

-->下面调整表明已使用空间超出了分配的空间

SQL> alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;

alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

SQL> select count(*) from v$sort_usage where tablespace='TEMP';   -->当前有未释放的临时段

COUNT(*)

----------

4

/**************************************************/

/* Author: Robinson Cheng                         */

/* Blog:  http://blog.csdn.net/robinson_0612     */

/* MSN:   robinson_0612@hotmail.com              */

/* QQ:     645746311                              */

/**************************************************/

4、新建一个中转临时表空间

SQL> create temporary tablespace temp2 tempfile '/u02/database/ORADB/temp/ORADB_temp02.dbf'

2  size 10m autoextend on;

Tablespace created.

-->如果此时过大的临时表空间为缺省的临时表空间，则必须将缺省的临时表空间设置为新的临时表空间之后

SQL> select property_name,property_value from database_properties

2  where property_name like 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE

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

DEFAULT_TEMP_TABLESPACE        TEMP

SQL> alter database default temporary tablespace temp2;

Database altered.

5、转移用户到中转临时表空间

-->过大临时表空间上的那些用户需要迁移到新建的临时表空间

-->查询dba_users视图查询哪些用户位于过大的临时表空间之上

-->并使用下面的命令将其切换到新的临时表空间

alter user temporary tablespace temp2;

6.等到过大临时表空间上的没有临时段被使用，即已经全部释放即可删除过大的临时表空间

SQL> show user;    -->由于当前用户为scott，所以临时表空间未能释放

USER is "SCOTT"

SQL> conn / as sysdba   -->切换到sysdba

Connected.

SQL> @temp_usage2      -->临时段已经被释放

TABLESPACE             MB_TOTAL    MB_USED    MB_FREE

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

GO_TEMP                   106          0        106

TEMP                      235          0        235

-->如果没有释放在可以kill session的情况下kill session.利用前面获得的sid,serial#来执行(前提是允许该情况发生).

alter system kill session '1064,9259'

7.删除过大的临时表空间

SQL> alter tablespace temp tempfile offline;   -->先将其脱机

Tablespace altered.

SQL> drop tablespace temp including contents and datafiles;   -->删除临时表空间及相应的文件

Tablespace dropped.

SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status

2  from v$tablespace s,v$tempfile t

3  where s.ts# = t.ts#;

TBSNAME         NAME                                                       MB STATUS

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

GO_TEMP       /u02/database/ORADB/temp/ORADB_tempORADB.dbf           106 ONLINE

TEMP2        /u02/database/ORADB/temp/ORADB_temp02.dbf                10 ONLINE

-->也可以使用下面的命令来完成仅仅删除单个文件

ALTER DATABASE TEMPFILE '/u02/database/ORADB/temp/tempORADB.dbf' DROP INCLUDING DATAFILES; -->删除单个文件

7、根据需求可以创建原来的临时表空间并将切换出去用户切换到此临时表空间