小工具知识网

当前位置: 首页 > 行业知识 > 关于临时表空间问题总结

行业知识

关于临时表空间问题总结

2026-11-18 14:27:43 来源:互联网转载

oracle���常需要查数据库临时表空间大小,使用率,加表空间等,这里总结临时表空间相关的语句:

()

0、查看实例的临时表空间

SELECT * FROM dba_tablespaces t where t.CONTENTS='TEMPORARY';SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

1、查询临时表空间路径:

()
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

2、新增临时表空间:

alter tablespace srmqy_temp add tempfile '/data/oradata/srmqy_temp01.dbf' size 64m autoextend on next 64m maxsize unlimited;

3、查询临时表空间使用情况:

select c.tablespace_name,to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,to_char(d.bytes_used*100/c.bytes,'99.99') || '%'usefrom  (select tablespace_name,sum(bytes) bytesfrom dba_temp_files GROUP by tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_usedfrom v$temp_extent_pool GROUP by tablespace_name) dwhere c.tablespace_name = d.tablespace_name;

查看临时表空间的使用情况

select a.tablespace_name,       to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,       to_char(b.bytes_used / 1024 / 1024, '99,999.999') use_bytes,       to_char(b.bytes_used * 100 / a.bytes, '99.99') || '%' use  from (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,       (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) b where a.tablespace_name = b.tablespace_name

4、删除临时表空间:

删除临时表空间的一个数据文件:

SQL> alter database tempfile ‘/data/oradata/srmqy_temp01.dbf’ drop;

5、删除临时表空间(彻底删除):

SQL> drop tablespace orcl_temp including contents and datafiles cascade constraints;

6、更改系统的默认临时表空间:

--查询默认临时表空间select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';--修改默认临时表空间(所有用户的默认临时表空间都将切换为新的临时表空间:)alter database default temporary tablespace temp1;

7、查看用户的临时表空间所属

select username,temporary_tablespace,default_ from dba_users;--更改某一用户的临时表空间:alter user scott temporary tablespace temp;

8、删除临时表空间

删除临时表空间的一个数据文件:SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;删除临时表空间(彻底删除):SQL> drop tablespace temp1 including contents and datafiles cascade constraints;

9、查看临时表空间的使用情况(GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询)

GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小dba_temp_files视图的bytes字段记录的是临时表空间的总大小临时表空间是否自动扩展select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE,MAXBYTES/1024/1024 "MAXBYTES(M)", USER_BYTES/1024/1024 "USER_BYTES(M)" from dba_temp_files;select FILE_NAME "临时表空间路径", TABLESPACE_NAME "临时表空间名", AUTOEXTENSIBLE "是否自动扩展",MAXBYTES/1024/1024 "表空间大小(M)", USER_BYTES/1024/1024 "表空间使用大小(M)" from dba_temp_files;
临时表空间使用率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; 
SELECT temp_used.tablespace_name as "临时表空间名", total - used as "空闲空间(M)", total as "表空间大小(M)", round(nvl(total - used, 0) * 100 / total, 3) "空闲比率(%)"  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; 
select h.tablespace_name tablespace_name,f.autoextensible,round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,round(sum(decode(f.autoextensible, 'YES',  f.maxbytes, 'NO', f.bytes)) / power(2, 30), 2) max_gbfrom v$temp_space_header h, v$temp_extent_pool p, dba_temp_files fwhere p.file_id(+) = h.file_idand p.tablespace_name(+) = h.tablespace_nameand f.file_id = h.file_idand f.tablespace_name = h.tablespace_namegroup by h.tablespace_name,f.autoextensible;
select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE,MAXBYTES/1024/1024 "MAXBYTES(M)", USER_BYTES/1024/1024 "USER_BYTES(M)" from dba_temp_files;
select a.username,a.sql_id,a.SEGTYPE,b.BYTES_USED/1024/1024/1024||'G',b.BYTES_FREE/1024/1024/1024  from   V$TEMPSEG_USAGE  a  join  V$TEMP_SPACE_HEADER b on   a.TABLESPACE=b.tablespace_name; 

解释:

username 正在执行sql的用户名

sql_id 正在执行的sql的的sql_id

segtype 正在执行的SQL语句做的是什么操作

BYTES_USED 正在执行sql语句使用的临时表空间的大小

BYTES_FREE 剩余多少临时表空间

——设置输出格式sql>col file_name format a55    sql>set line 120 pagesize 2000 

Oracle查看用户及对应的表空间与临时表空间

select username "用户名称",ACCOUNT_STATUS "用户状态",DEFAULT_TABLESPACE "表空间",TEMPORARY_TABLESPACE "临时表空间" from dba_users order by 3,1;

删除数据库数据操作:

1、解锁当前用户连接状态:

alter user healmall account lock;commit;

2、删除指定用户:

drop user healmall cascade;commit;

3、删除表空间:

drop tablespace healmall_tmp including contents and datafiles cascade constraint;drop tablespace healmall_data including contents and datafiles cascade constraint;

查看临时表空间的数据文件的状态

select file#,status,bytes/1024/1024 "MB",name from v$tempfile;select name,bytes/1024/1024 MB,status from v$datafile d union all select name,bytes/1024/1024 MB,status from v$tempfile;

上一篇:巢湖服务器数据迁移在哪里?

下一篇:怎么进入在国内的服务器?