oracle扩容临时表空间,oracle临时表空间扩容

查看临时表空间

select *

from (Select a.tablespace_name,

to_char(a.bytes / 1024 / 1024, ‘99,999.999‘) total_bytes,

to_char(b.bytes / 1024 / 1024, ‘99,999.999‘) free_bytes,

to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,

‘99,999.999‘) use_bytes,

to_char((1 - b.bytes / a.bytes) * 100, ‘99.99‘) || ‘%‘ use

from (select tablespace_name, sum(bytes) bytes

from dba_data_files

group by tablespace_name) a,

(select tablespace_name, sum(bytes) bytes

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

union all

select c.tablespace_name,

to_char(c.bytes / 1024 / 1024, ‘99,999.999‘) total_bytes,

to_char((c.bytes - d.bytes_used) / 1024 / 1024, ‘99,999.999‘) free_bytes,

to_char(d.bytes_used / 1024 / 1024, ‘99,999.999‘) use_bytes,

to_char(d.bytes_used * 100 / c.bytes, ‘99.99‘) || ‘%‘ use

from (select tablespace_name, sum(bytes) bytes

from dba_temp_files

group by tablespace_name) c,

(select tablespace_name, sum(bytes_cached) bytes_used

from v$temp_extent_pool

group by tablespace_name) d

where c.tablespace_name = d.tablespace_name)

order by tablespace_name ;

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

查看临时表分区

SELECT *

FROM (SELECT USERNAME,

SESSION_ADDR,

SQL_ID,

CONTENTS,

SEGTYPE,

BLOCKS * 8 / 1024 / 1024 GB

FROM V$SORT_USAGE

ORDER BY BLOCKS DESC)

WHERE ROWNUM <= 200;

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

查看表空间使用率

SELECT a.tablespace_name "表空间名称",

total / (1024 * 1024) "表空间大小(M)",

free / (1024 * 1024) "表空间剩余大小(M)",

(total - free) / (1024 * 1024 ) "表空间使用大小(M)",

total / (1024 * 1024 * 1024) "表空间大小(G)",

free / (1024 * 1024 * 1024) "表空间剩余大小(G)",

(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",

round((total - free) / total, 4) * 100 "使用率 %"

FROM (SELECT tablespace_name, SUM(bytes) free

FROM dba_free_space

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM(bytes) total

FROM dba_data_files

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

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

1.查看临时表空间的数据文件及存放路径

select tablespace_name,

file_name,

user_bytes/bytes,

bytes / 1024 / 1024 "file_size(M)",

autoextensible

from dba_temp_files t

where t.TABLESPACE_NAME = ‘TEMP‘;

2.

登录数据库服务器，执行扩容语句，如下

SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf‘ resize 4G;

原文：https://www.cnblogs.com/liqing1009/p/14478398.html