參考文獻(xiàn)
一捡鱼、 告警原因
oracle內(nèi)部用10位數(shù)據(jù)記錄單個(gè)表空間下文件個(gè)數(shù)，因此當(dāng)單個(gè)表空間下文件數(shù)超出2^10時(shí)览爵，該表空間將無法繼續(xù)添加數(shù)據(jù)文件语淘，必須對表空間下數(shù)據(jù)進(jìn)行遷移栅炒。表空間文件數(shù)告警閾值為800個(gè)。
二说莫、 查詢文件個(gè)數(shù)達(dá)到閾值的表空間
SET linesize 200
SELECT *
FROM (SELECT tablespace_name,
Count(*) AS file_cnt
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name)
WHERE file_cnt > 800;
或者
SET linesize 200
SELECT *
FROM (SELECT ts#,
tablespace_name,
Count(*) AS file_cnt,
Max(creation_time) AS max_creation_time
FROM v$datafile_header
GROUP BY ts#,
tablespace_name) t
WHERE file_cnt > 800;
二杨箭、 找出表空間下數(shù)據(jù)量最大的用戶
SET lines 200
SELECT *
FROM (SELECT tablespace_name,
OWNER,
Sum(bytes) / 1024 / 1024 AS mb
FROM dba_segments
GROUP BY tablespace_name,
OWNER
ORDER BY mb DESC)
WHERE tablespace_name = '&tbsname';
或者
SET lines 200
SELECT *
FROM (SELECT tablespace_name,
OWNER,
Sum(bytes) / 1024 / 1024 AS mb
FROM dba_extents
GROUP BY tablespace_name,
OWNER
ORDER BY mb DESC)
WHERE tablespace_name = '&tbsname';
三、 組合查找
找出文件個(gè)數(shù)達(dá)到預(yù)警值的表空間储狭，并且找出其中空間使用量前三的用戶互婿。
SET lines 200
SELECT *
FROM (SELECT tablespace_name,
OWNER,
Sum(bytes) / 1024 / 1024 AS mb
FROM dba_extents
GROUP BY tablespace_name,
OWNER
ORDER BY mb DESC)
WHERE tablespace_name IN (SELECT tablespace_name
FROM (SELECT tablespace_name,
Count(*) AS file_cnt
FROM v$datafile_header
GROUP BY tablespace_name) t
WHERE file_cnt > 800)
AND rownum < 4;
