ORACLE 臨時表空間的增刪改查：

DB_BLOCK發表於2013-11-04
Oracle

ORACLE 臨時表空間的增刪改查：

Oracle臨時表空間主要用來做查詢和存放一些緩衝區資料。臨時表空間消耗的主要原因是需要對查詢的中間結果進行排序。
重啟資料庫可以釋放臨時表空間，如果不能重啟例項，而一直保持問題sql語句的執行，temp表空間會一直增長。直到耗盡硬碟空間。


##. 1.檢視臨時表空間：
SQL>  select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME                               FILE_NAME                                      FILE_SIZE AUT
--------------------------------------------- --------------------------------------------- ---------- ---
TEMP                                          /u01/app/oracle/oradata/ora11g/temp01.dbf             38 YES

SQL> select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;

STATUS  ENABLED    NAME                                           FILE_SIZE
------- ---------- --------------------------------------------- ----------
ONLINE  READ WRITE /u01/app/oracle/oradata/ora11g/temp01.dbf             38

##. 2.改變臨時表空間的大小：

SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' resize 20M;

Database altered.
我們通過上面語句參看到，temp01以前是38M 現在已經修改為20M
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME                               FILE_NAME                                      FILE_SIZE AUT
--------------------------------------------- --------------------------------------------- ---------- ---
TEMP                                          /u01/app/oracle/oradata/ora11g/temp01.dbf             20 YES

##. 3.擴充套件臨時表空間：
方法一、增大臨時檔案大小：
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' resize 20M;
方法二、將臨時資料檔案設為自動擴充套件：
SQL> alter database tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf' autoextend on next 5m maxsize unlimited;
方法三、向臨時表空間中新增資料檔案：
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf’ size 100m;


##. 4.建立臨時表空間：

SQL> create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf'size 10M;
create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/ora11g/temp01.dbf'size 10M
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/ora11g/temp01.dbf' - file already part of database
注意：這個報錯是因為temp01預設已經有了，所以我們不能再建立temp01

SQL> create temporary tablespace temp02 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M;

Tablespace created.


##. 5.更改系統的預設臨時表空間：

--查詢預設臨時表空間
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace

--修改預設臨時表空間

SQL> alter database default temporary tablespace temp2;

Database altered.
我們可以查詢是否切換為TEMP2：
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2                Name of default temporary tablespace


##. 6.刪除臨時表空間：
--刪除臨時表空間的一個資料檔案：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
drop database temp2 including contents and datafiles;


SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M;
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ora11g/temp02.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora11g/temp02.dbf'
ORA-27038: created file already exists
Additional information: 1


SQL> drop tablespace temp2 including contents and datafiles;
drop tablespace temp2 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'TEMP2' does not exist


##. 7. 關於檢視當前臨時表空間使用大小與正在佔用臨時表空間的sql語句
SQL> col sql_text for a40
SQL> select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  2    from v$sort_usage sort, v$session sess, v$sql sql
  3   where sort.SESSION_ADDR = sess.SADDR
  4     and sql.ADDRESS = sess.SQL_ADDRESS
  5   order by blocks desc;

       SID SEGTYPE           MB SQL_TEXT
---------- --------- ---------- ----------------------------------------
        49 DATA           1.024 select sess.SID, segtype, blocks * 8 / 1
                                000 "MB", sql_text   from v$sort_usage s
                                ort, v$session sess, v$sql sql  where so
                                rt.SESSION_ADDR = sess.SADDR    and sql.
                                ADDRESS = sess.SQL_ADDRESS  order by blo
                                cks desc
 
##. 8.檢視臨時表空間的使用情況

SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;
 
TABLESPACE_NAME                                     Free      Total Free percent
--------------------------------------------- ---------- ---------- ------------
TEMP                                                  16         20           80
TEMP01                                                29         30       96.667

##. 9.查詢消耗資源比較的sql語句

Select se.username,
       se.sid,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as Space,
       tablespace,
       segtype,
       sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
 order by se.username, se.sid;


USERNAME          SID    EXTENTS      SPACE TABLESPACE SEGTYPE   SQL_TEXT
---------- ---------- ---------- ---------- ---------- --------- ----------------------------------------
SYS                49          1    1048576 TEMP       DATA      Select se.username,        se.sid,
                                                                   su.extents,        su.blocks * to_numb
                                                                 er(rtrim(p.value)) as Space,        tabl
                                                                 espace,        segtype,        sql_text
                                                                   from v$sort_usage su, v$parameter p, v
                                                                 $session se, v$sql s  where p.name = 'db
                                                                 _block_size'    and su.session_addr = se
                                                                 .saddr    and s.hash_value = su.sqlhash
                                                                    and s.address = su.sqladdr  order by
                                                                 se.username, se.sid

歡迎大家批評指正：
QQ交流群：300392987
論        壇：www.oraclefreebase.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29210156/viewspace-775649/，如需轉載，請註明出處，否則將追究法律責任。

相關文章