Oracle 19c Tablespace Monitoring (Check Descriptionfor SQL Quesries)

Опубликовано: 29 Сентябрь 2024
на канале: Oracle DBA Cafe

609

Oracle 19c Tablespace Monitoring
(SQL Quesries are provided below)
-- Total Available size for all data files --
SET LINES 300
COL file_name FORMAT A70
SELECT file_name, tablespace_name, ROUND(bytes/1024/1024/1024,2) file_size_gb, status,
autoextensible, ROUND(maxbytes/1024/1024/1024,2) max_size_gb, online_status
FROM dba_data_files
ORDER BY tablespace_name, file_name;

-- Total Available size in GB for all Tablespaces --
SET LINES 300
SELECT
tablespace_name,
ROUND(SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1024/1024/1024,2) Total_size_GB
FROM dba_data_files
GROUP BY tablespace_name;

-- Total space allocated/ Used from each Tablesapce --
SET LINES 300
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024,2) Used_size_GB
FROM dba_segments
GROUP BY tablespace_name;

-- Tablespace Monitoring Script --
SET LINES 300
SELECT total_ts.tablespace_name, used_ts.used_size_gb, total_ts.total_size_gb, ROUND((used_ts.used_size_gb / total_ts.total_size_gb * 100),2) percent_used
FROM
(SELECT tablespace_name,ROUND(SUM(bytes)/1024/1024/1024,2) Used_size_GB FROM dba_segments GROUP BY tablespace_name) used_ts,
(SELECT tablespace_name,ROUND(SUM(DECODE(autoextensible,'YES',maxbytes,bytes))/1024/1024/1024,2) Total_size_GB FROM dba_data_files GROUP BY tablespace_name) total_ts
WHERE used_ts.tablespace_name = total_ts.tablespace_name
ORDER by (used_ts.used_size_gb / total_ts.total_size_gb * 100) DESC;