2019獨角獸企業重金招聘Python工程師標準>>>

oracle 臨時表空間的增刪改查

1、查看臨時表空間?（dba_temp_files視圖）（v_$tempfile視圖）
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用戶查看

查看表空間：

select?b.file_id?文件ID號,

???????b.tablespace_name?表空間名,

???????b.bytes?/?1024?/?1024?||?'M'?字節數,

???????(b.bytes?-?sum(nvl(a.bytes,?0)))?/?1024?/?1024?||?'M'?已使用,

???????sum(nvl(a.bytes,?0))?/?1024?/?1024?||?'M'?剩余空間,

???????100?-?sum(nvl(a.bytes,?0))?/?(b.bytes)?*?100?占用百分比

??from?dba_free_space?a,?dba_data_files?b

?where?a.file_id?=?b.file_id

???and?b.tablespace_name?= ?

?group?by?b.tablespace_name,?b.file_id,?b.bytes

?order?by?b.file_id;

2、縮小臨時表空間大小
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;

3、擴展臨時表空間：
方法一、增大臨時文件大小：

alter?database?tempfile?‘/u01/app/oracle/oradata/orcl/temp01.dbf’?resize?100m;

方法二、將臨時數據文件設為自動擴展：?

alter?database?tempfile?‘/u01/app/oracle/oradata/orcl/temp01.dbf’?autoextend?on?next?5m?maxsize?unlimited;

方法三、向臨時表空間中添加數據文件：

alter?tablespace?temp?add?tempfile?‘/u01/app/oracle/oradata/orcl/temp02.dbf’?size?100m;

4、擴展表空間：

方法一、增大表空間文件大小：

alter?database?datafile?'/opt/oracle/oradata/esop/so_data01.dbf'?resize?200M;

方法二、將表空間數據文件大小：

alter?tablespace?users?add?datafile?'/opt/oracle/oradata/esop/so_data02.dbf'?size?200M;

?

5、創建臨時表空間：?

create?temporary?tablespace?temp1?tempfile?‘/u01/app/oracle/oradata/orcl/temp11.dbf’?size?10M;

6、更改系統的默認臨時表空間：

--查詢默認臨時表空間? select?*?from?database_properties?where?property_name='DEFAULT_TEMP_TABLESPACE';? --修改默認臨時表空間? alter?database?default?temporary?tablespace?temp1;? 所有用戶的默認臨時表空間都將切換為新的臨時表空間：? select?username,temporary_tablespace,default_?from?dba_users;? --更改某一用戶的臨時表空間：? alter?user?scott?temporary?tablespace?temp;

7、刪除臨時表空間
刪除臨時表空間的一個數據文件：

alter?database?tempfile?‘/u01/app/oracle/oradata/orcl/temp02.dbf’?drop;

刪除臨時表空間(徹底刪除)：

drop?tablespace?temp1?including?contents?and?datafiles?cascade?constraints;

8、查看臨時表空間的使用情況（GV_$TEMP_SPACE_HEADER視圖必須在sys用戶下才能查詢）
GV_$TEMP_SPACE_HEADER視圖記錄了臨時表空間的使用大小與未使用的大小
dba_temp_files視圖的bytes字段記錄的是臨時表空間的總大小

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

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

?
10、查看當前臨時表空間使用大小與正在占用臨時表空間的sql語句

select?sess.SID,?segtype,?blocks?*?8?/?1000?"MB",?sql_text?from?v$sort_usage?sort,?v$session?sess,?v$sql?sql?where?sort.SESSION_ADDR?=?sess.SADDR?and?sql.ADDRESS?=?sess.SQL_ADDRESS?order?by?blocks?desc;

11、臨時表空間組介紹
? 1）創建臨時表空間組：

create?temporary?tablespace?tempts1?tempfile?'/home/oracle/temp1_02.dbf'?size?2M?tablespace?group?group1;?create?temporary?tablespace?tempts2?tempfile?'/home/oracle/temp2_02.dbf'?size?2M?tablespace?group?group2;

?
?2）查詢臨時表空間組：dba_tablespace_groups視圖

select?*?from?dba_tablespace_groups;

GROUP_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1???????????????????????? TEMPTS1
GROUP2???????????????????????? TEMPTS2

?3）將表空間從一個臨時表空間組移動到另外一個臨時表空間組：
alter tablespace tempts1 tablespace group GROUP2 ;
select * from dba_tablespace_groups;

GROUP_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2???????????????????????? TEMPTS1
GROUP2???????????????????????? TEMPTS2

?4）把臨時表空間組指定給用戶

alter?user?scott?temporary?tablespace?GROUP2;

?5）在數據庫級設置臨時表空間
alter database <db_name> default temporary tablespace GROUP2;?

?6）刪除臨時表空間組 (刪除組成臨時表空間組的所有臨時表空間)
drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME???????????????????? TABLESPACE_NAME
------------------------------ ------------------------------
GROUP2???????????????????????? TEMPTS2

drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME???????????????????? TABLESPACE_NAME

12、對臨時表空間進行shrink（11g新增的功能）
--將temp表空間收縮為20M
alter tablespace temp shrink space keep 20M;?
--自動將表空間的臨時文件縮小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;?

臨時表空間作用
Oracle臨時表空間主要用來做查詢和存放一些緩沖區數據。臨時表空間消耗的主要原因是需要對查詢的中間結果進行排序。
重啟數據庫可以釋放臨時表空間，如果不能重啟實例，而一直保持問題sql語句的執行，temp表空間會一直增長。直到耗盡硬盤空間。
網上有人猜測在磁盤空間的分配上，oracle使用的是貪心算法，如果上次磁盤空間消耗達到1GB，那么臨時表空間就是1GB。
也就是說當前臨時表空間文件的大小是歷史上使用臨時表空間最大的大小。

臨時表空間的主要作用：
? 索引create或rebuild；
? Order by 或 group by；
? Distinct 操作；
? Union 或 intersect 或 minus；
? Sort-merge joins；
? analyze.

轉載于:https://my.oschina.net/u/210131/blog/268637

總結

以上是生活随笔為你收集整理的oracle 表空间的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。