Oracle 临时表空间使用量监测

alter tablespace TEMP add tempfile autoextend on;

ALTER TABLESPACE TEMP   ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M autoextend on;

SELECT s.inst_id,       s.sid,       s.serial#,       s.username,       s.program,       t.tablespace,       t.segtype,       t.blocks *       (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 AS mb_used  FROM gv$session s, gv$tempseg_usage t WHERE s.saddr = t.session_addr ORDER BY mb_used DESC;

select t.* from v$temp_space_header t order by 2;select t.* from dba_temp_free_space t;SELECT bytes / 1024 / 1024 AS total_mb, blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024 AS used_mb, t.*  FROM v$tempfile t;

create temporary tablespace temp2  tempfile '/u01/app/oracle/oradata/PROD/temp02.dbf' size 10m;alter tablespace temp2 add tempfile  '/u01/app/oracle/oradata/PROD/temp03.dbf' size 5m;select file_id,file_name,tablespace_name  from dba_temp_files;-- 查看默认临时表空间select PROPERTY_NAME,PROPERTY_VALUE from database_properties  where property_name like 'DEFAULT%';-- 为用户指定单独的临时表空间alter user scott temporary tablespace temp2;

[oracle@racdb1 ~]$ oerr ora 0165201652, 00000, "unable to extend temp segment by %s in tablespace %s"// *Cause:  Failed to allocate an extent of the required number of blocks for//          a temporary segment in the tablespace indicated.// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more//          files to the tablespace indicated.[oracle@racdb1 ~]$