Oracle数据库表空间使用情况查看管理
Oracle RAC环境查看表空间使用情况
·
Oracle RAC环境查看表空间使用情况
查询字段释义：
NEED_ADDFILE,--是否需增加表空间文件
TABLESPACE_NAME,--表空间名称
TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
NOW_FILEENABLE_BLOCKS,--表空间文件当前数据块数
NOW_FILEENABLE_BYTES_GB,--表空间文件当前大小
USED_BLOCKS,--已分配的数据块数
USED_BYTES_GB,--已分配的空间大小
FREE_SPACE,--数据文件当前空间中剩余空间大小
MAXBLOCKS,--最大空间数据块数
MAXBYTES_GB,--最大空间数据大小
TABLESPACE_USE_RATE--已分配空间占用率
select NEED_ADDFILE,--是否需增加表空间文件
TABLESPACE_NAME,--表空间名称
TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
NOW_FILEENABLE_BLOCKS,--表空间文件当前数据块数
NOW_FILEENABLE_BYTES_GB,--表空间文件当前大小
USED_BLOCKS,--已分配的数据块数
USED_BYTES_GB,--已分配的空间大小
FREE_SPACE,--数据文件当前空间中剩余空间大小
MAXBLOCKS,--最大空间数据块数
MAXBYTES_GB,--最大空间数据大小
TABLESPACE_USE_RATE--已分配空间占用率
from (SELECT CASE
WHEN TABLESPACE_USE_RATE >= 96 AND
ABS(MAXBYTES_GB - NOW_FILEENABLE_BYTES_GB) <= 2 AND
FREE_SPACE <= 2 THEN
'YES'
ELSE
'NO'
END AS NEED_ADDFILE, --是否需增加表空间文件
A.*
FROM (SELECT A.TABLESPACE_NAME, --表空间名称
TO_CHAR(COUNT(A.FILE_NAME)) AS TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
TO_CHAR(SUM(A.BLOCKS)) AS NOW_FILEENABLE_BLOCKS, --表空间文件当前数据块数
TO_CHAR(SUM(A.BYTES) / 1024 / 1024 / 1024, 9990.099) AS NOW_FILEENABLE_BYTES_GB, --表空间文件当前大小
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS)) - MAX(B.FREE_BLOCKS)) AS USED_BLOCKS, --已分配的数据块数
TRIM(TO_CHAR(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024 -
MAX(B.FREE_SPACE),
99990.099)) AS USED_BYTES_GB, --已分配的空间大小
ROUND(MAX(B.FREE_SPACE), 2) FREE_SPACE, --数据文件当前空间中剩余空间大小
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS))) AS MAXBLOCKS, --最大空间数据块数
TO_CHAR(SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES)) / 1024 / 1024 / 1024,
9990.099) AS MAXBYTES_GB, --最大空间数据大小
TO_NUMBER(((SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024) -
MAX(B.FREE_SPACE)) * 100 /
(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024)) AS TABLESPACE_USE_RATE --已分配空间占用率
FROM DBA_DATA_FILES A
JOIN (SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 FREE_SPACE,
SUM(BLOCKS) FREE_BLOCKS
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAME
UNION ALL
SELECT A.TABLESPACE_NAME AS "表空间名",
TO_CHAR(COUNT(A.FILE_NAME)) AS "表空间文件数",
TO_CHAR(SUM(A.BLOCKS)) AS "当前可用数据块数",
TO_CHAR(SUM(A.BYTES) / 1024 / 1024 / 1024, 9990.099) AS "当前可用大小(G)",
TO_CHAR(MAX(B.USED_BLOCKS)) AS "已分配数据块数",
TRIM(TO_CHAR(MAX(B.USED_SPACE), 99990.099)) AS "已分配大小(G)",
ROUND(MAX(TEMPFREE.FREE_SPACE) / 1024 / 1024 / 1024, 2) 自由空间,
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS))) AS "可分配最大数据块数",
TO_CHAR(SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES)) / 1024 / 1024 / 1024,
9990.099) AS "可分配的最大空间(G)",
TO_NUMBER(MAX(B.USED_SPACE) * 100 /
(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024)) AS "表空间大小使用率"
FROM DBA_TEMP_FILES A
JOIN (SELECT TABLESPACE_NAME,
SUM(BYTES_CACHED) / 1024 / 1024 / 1024 USED_SPACE,
SUM(BLOCKS_CACHED) USED_BLOCKS
FROM (SELECT DISTINCT * FROM GV$TEMP_EXTENT_POOL)
GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
JOIN DBA_TEMP_FREE_SPACE TEMPFREE
ON TEMPFREE.TABLESPACE_NAME = A.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAME) A)
若为非RAC环境，将SQL中的gv$前缀替换为v$便可。
数据库自动检测并增加表空间文件方法
如果想让数据库自己定期自动检查表空间，并为达到阈值的表空间自动增加表空间文件，可以结合存过及定时任务结合完成，如：
在SYS用户下创建存储过程查询表空间使用情况，并为空间不足的表空间增加数据文件；创建定时任务每天自动执行
CREATE PACKAGE SYS.PKG_SCENE_JOB AS
/*检查表空间使用情况
* 自动扩展占用过高的表空间
* @parameter
* in_tablespace 表空间名 default null
* */
PROCEDURE PRC_CHK_TABLESPACE_STAT(IN_TABLESPACE VARCHAR2 DEFAULT '0');
END PKG_SCENE_JOB;
/
CREATE PACKAGE BODY SYS.PKG_SCENE_JOB AS
/*检查表空间使用情况
* 自动扩展占用过高的表空间
* @parameter
* in_tablespace 表空间名 default null
* */
PROCEDURE PRC_CHK_TABLESPACE_STAT(IN_TABLESPACE VARCHAR2 DEFAULT '0') IS
TYPE T_ARRCHAR IS TABLE OF VARCHAR2(50);
V_TABLESPACE T_ARRCHAR; --表空间扩展DDL
V_QSPACE CLOB; --表空间查看语句
BEGIN
V_QSPACE := 'SELECT TABLESPACE_NAME FROM (
SELECT
a.TABLESPACE_NAME,
to_char(count(a.FILE_NAME)) AS TABLESPACE_FILE_COUNT,
to_char(sum(a.BLOCKS)) AS NOW_ENABLE_BLOCKS,
to_char(sum(a.BYTES)/ 1024 / 1024 / 1024, 9990.099) AS NOW_ENABLE_BYTES_GB,
to_char(sum(decode(a.MAXBLOCKS, 0, a.BLOCKS, a.MAXBLOCKS))-max(b.free_blocks)) AS USED_BLOCKS,
trim(to_char(sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024-max(b.free_space), 99990.099)) AS USED_BYTES_GB,
round(max(b.free_space),2) free_space,
to_char(sum(decode(a.MAXBLOCKS, 0, a.BLOCKS, a.MAXBLOCKS))) AS MAXBLOCKS,
to_char(sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024, 9990.099) AS MAXBYTES_GB,
to_number(((sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024)-max(b.free_space))* 100 /(sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024)) AS TABLESPACE_USE_RATE
FROM
dba_data_files a
JOIN (
SELECT
TABLESPACE_NAME,
sum(BYTES)/ 1024 / 1024 / 1024 free_space,
sum(BLOCKS) free_blocks
FROM
dba_free_space
GROUP BY
TABLESPACE_NAME
) b ON
a.TABLESPACE_NAME = b.TABLESPACE_NAME
GROUP BY
a.TABLESPACE_NAME,
b.TABLESPACE_NAME';
V_QSPACE := V_QSPACE || '
UNION ALL
SELECT
a.TABLESPACE_NAME AS "表空间名",
to_char(count(a.FILE_NAME)) AS "表空间文件数",
to_char(sum(a.BLOCKS)) AS "当前可用数据块数",
to_char(sum(a.BYTES)/ 1024 / 1024 / 1024, 9990.099) AS "当前可用大小(G)",
to_char(max(b.used_blocks)) AS "已分配数据块数",
trim(to_char(max(b.used_space), 99990.099)) AS "已分配大小(G)",
round(max(tempfree.free_space)/ 1024 / 1024 / 1024,2) 自由空间,
to_char(sum(decode(a.MAXBLOCKS, 0, a.BLOCKS, a.MAXBLOCKS))) AS "可分配最大数据块数",
to_char(sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024, 9990.099) AS "可分配的最大空间(G)",
to_number(max(b.used_space)* 100 /(sum(decode(a.MAXBYTES, 0, a.BYTES, a.MAXBYTES))/ 1024 / 1024 / 1024)) AS "表空间大小使用率"
FROM
dba_temp_files a
JOIN (
SELECT
TABLESPACE_NAME,
sum(BYTES_CACHED)/ 1024 / 1024 / 1024 used_space,
sum(BLOCKS_CACHED) used_blocks
FROM
(select distinct * from gv$temp_extent_pool)
GROUP BY
TABLESPACE_NAME
) b ON
a.TABLESPACE_NAME = b.TABLESPACE_NAME
join dba_temp_free_space tempfree
on tempfree.tablespace_name=a.tablespace_name
GROUP BY
a.TABLESPACE_NAME,
b.TABLESPACE_NAME)
WHERE TABLESPACE_USE_RATE>=95
AND abs(MAXBYTES_GB-NOW_ENABLE_BYTES_GB)<=6
and free_space<=6';
IF IN_TABLESPACE != '0' THEN
SELECT IN_TABLESPACE BULK COLLECT INTO V_TABLESPACE FROM DUAL;
ELSE
EXECUTE IMMEDIATE V_QSPACE BULK COLLECT
INTO V_TABLESPACE;
END IF;
<<扩充表空间>>
BEGIN
IF V_TABLESPACE.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('无表空间需进行扩充!');
RETURN;
END IF;
FOR I IN 1 .. V_TABLESPACE.COUNT LOOP
DECLARE
V_TEMPSQL VARCHAR2(200) := 'ALTER tablespace :tsp ADD tempfile ''+DATA'' SIZE 10G autoextend on';
V_SQL VARCHAR2(200) := 'ALTER tablespace :tsp ADD datafile ''+DATA'' SIZE 10G autoextend on';
V_FPATH VARCHAR2(500); --数据文件路径
BEGIN
--临时表空间
IF V_TABLESPACE(I) = 'TEMP' THEN
SELECT SUBSTR(A.FILE_NAME, 0, INSTR(A.FILE_NAME, '/', -1)) FPATH
INTO V_FPATH
FROM DBA_TEMP_FILES A
WHERE A.TABLESPACE_NAME = V_TABLESPACE(I)
AND ROWNUM = 1;
--没启用ASM管理数据文件
IF INSTR(V_FPATH, '+DATA') = 0 THEN
V_TEMPSQL := REPLACE(V_TEMPSQL,
'+DATA',
V_FPATH || V_TABLESPACE(I) ||
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ||
'.DBF');
END IF;
V_TEMPSQL := REPLACE(V_TEMPSQL,
':tsp',
'"' || V_TABLESPACE(I) || '"');
DBMS_OUTPUT.PUT_LINE(V_TEMPSQL);
EXECUTE IMMEDIATE V_TEMPSQL;
DBMS_OUTPUT.PUT_LINE('扩充表空间：' || V_TABLESPACE(I) || '成功!');
ELSE
SELECT SUBSTR(A.FILE_NAME, 0, INSTR(A.FILE_NAME, '/', -1)) FPATH
INTO V_FPATH
FROM DBA_DATA_FILES A
WHERE A.TABLESPACE_NAME = V_TABLESPACE(I)
AND ROWNUM = 1;
--没启用ASM管理数据文件
IF INSTR(V_FPATH, '+DATA') = 0 THEN
V_SQL := REPLACE(V_SQL,
'+DATA',
V_FPATH || V_TABLESPACE(I) ||
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ||
'.DBF');
END IF;
V_SQL := REPLACE(V_SQL, ':tsp', '"' || V_TABLESPACE(I) || '"');
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('扩充表空间：' || V_TABLESPACE(I) || '成功!');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END LOOP;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END PRC_CHK_TABLESPACE_STAT;
END PKG_SCENE_JOB;
/
begin
sys.dbms_scheduler.create_job(job_name => 'SYS.JOB_CHECK_TABLESPACE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin SYS.DBMS_SCHEDULER.PURGE_LOG(job_name => ''JOB_check_tablespace'');sys.pkg_scene_job.prc_chk_tablespace_stat(); end;',
start_date => to_date('20-12-2023 22:00:52', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=DAILY;ByHour=01',
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => '检查表空间使用状态，自动扩展占用过高的表空间');
end;
/
一站式 AI 云服务平台
更多推荐
1
0- 0
已为社区贡献3条内容
所有评论(0)