Oracle临时表空间管理

最新推荐文章于 2024-04-28 16:15:24 发布

z1blog

最新推荐文章于 2024-04-28 16:15:24 发布

阅读量1.1k

点赞数 14

分类专栏：数据库管理员(DBA) 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_41824599/article/details/135321368

版权

数据库管理员(DBA) 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

本文介绍了如何在Oracle数据库中查询表空间使用情况，包括临时表空间的大小、数据文件路径，以及如何调整、创建、删除临时表空间，以确保足够的存储空间和性能优化。

摘要由CSDN通过智能技术生成

–查表空间使用率情况（含临时表空间）
SELECT d.tablespace_name “Name”,
d.status “Status”,
TO_CHAR(NVL(a.BYTES / 1024 / 1024, 0), ‘99,999,990.90’) “Size (M)”,
TO_CHAR(NVL(a.BYTES - NVL(f.BYTES, 0), 0) / 1024 / 1024,
‘99999999.99’) USE,
TO_CHAR(NVL((a.BYTES - NVL(f.BYTES, 0)) / a.BYTES * 100, 0),
‘990.00’) “Used %”
FROM SYS.dba_tablespaces d,
(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) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management LIKE ‘LOCAL’ AND d.CONTENTS LIKE ‘TEMPORARY’)
UNION ALL
SELECT d.tablespace_name “Name”,
d.status “Status”,
TO_CHAR(NVL(a.BYTES / 1024 / 1024, 0), ‘99,999,990.90’) “Size (M)”,
TO_CHAR(NVL(t.BYTES, 0) / 1024 / 1024, ‘99999999.99’) USE,
TO_CHAR(NVL(t.BYTES / a.BYTES * 100, 0), ‘990.00’) “Used %”
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM(BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE ‘LOCAL’
AND d.CONTENTS LIKE ‘TEMPORARY’;

查询临时表空间剩余字节大小
SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS “FREE SPACE(M)”
FROM DBA_TEMP_FREE_SPACE
WHERE TABLESPACE_NAME = ‘&tablespace_name’;
查询临时表空间所有数据文件路径
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS “SPACE(M)”
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = ‘&tablespace_name’;
为空间不足的临时表空间增加数据文件
ALTER TABLESPACE &tablespace_name ADD TEMPFILE ‘&datafile_name’ SIZE 2G;
eg:
ALTER TABLESPACE ts_ecif_user_temp ADD TEMPFILE ‘/data/oradata/CDB01/ecifdb/datafile/ts_ecif_user_temp02.dbf’ SIZE 500M reuse AUTOEXTEND ON;

4.查看临时表空间的大小和数据文件路径
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS “SPACE(M)”
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = ‘TEMP’;
或者
select name, bytes/1024/1024 as size_mb from v$tempfile order by bytes;

5.重建并修改默认临时表空间：
–查询当前数据库默认临时表空间名
select * from database_properties where property_name=‘DEFAULT_TEMP_TABLESPACE’;
–创建新的临时表空间
create temporary tablespace temp02 tempfile ‘\u01\app\oracle\oradata\TEMP02.DBF’ size 1024M autoextend on;
–修改默认表空间为刚刚建立的临时表空间
alter database default temporary tablespace temp02;
–查看用户所用临时表空间的情况
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
–删除原来的临时表空间
drop tablespace temp including contents and datafiles;
–查看所有表空间名确认临时表空间是否已删除
select tablespace_name from dba_tablespaces;