表空間常用sql彙總
表空間碎片相關:
select tablespace_name,
round(sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))),
2) FSFI,
(case
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) > = 30 then
'正常'
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) < 30 then
'表空間破碎化程度高,請整理'
end) Prompt
from dba_free_space
group by tablespace_name
order by 2;
--排除SYSAUX和UNDOTBS1
整理碎片: alter tablespace coalesce;
檢視單個資料檔案使用情況:
select distinct d.name,
t.name,
round(s.bytes / (1024 * 1024), 0) total_space,
round(s.bytes / (1024 * 1024), 0)-round(f.bytes / (1024 * 1024), 0) used
from v$tablespace t, v$datafile d, dba_data_files s, dba_free_space f
where t.ts# = d.ts#
and d.name = s.file_name
and s.file_id = f.file_id
and d.file# = f.file_id
and t.name = f.tablespace_name
表空間使用率:
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),'990.99') || '%' "使用比(%)",
F.TOTAL_BYTES "空閒空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 desc;
檢視某個表空間實際剩餘空間sql:
select ((
nvl((select sum(t.maxblocks*8/1024) from dba_data_files t where t.autoextensible='YES'and t.tablespace_name = 'SYSTEM'),0)
+
nvl((select sum(t.blocks*8/1024) from dba_data_files t where t.autoextensible='NO'and t.tablespace_name = 'SYSTEM'),0))
-
(
SELECT D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME AND D.TABLESPACE_NAME = 'SYSTEM'
)) "實際剩餘空間(M)"
from dual;
資料檔案使用率:
select tot.file_name,100*(tot.total-free.total)/tot.total||'%' from
(select file_name,file_id,sum(bytes) as total from dba_data_files tot group by file_id,file_name ) tot,
(select file_id,sum(bytes) as total from dba_free_space free group by file_id) free
where tot.file_id=free.file_id
經測試,檢視資料檔案使用率,效能較好,較準確的:
SELECT B.TABLESPACE_NAME,
B.FILE_NAME,
ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
CASE
WHEN MAXBYTES = 0
THEN
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
ELSE
ROUND (
( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
END
"TOTAL FREE SPACE GB"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
B.FILE_NAME,
B.BYTES,
B.MAXBYTES
ORDER BY 1, 2;
檢視TEMP表空間使用情況:
SELECT a.tablespace_name,
a.BYTES / 1024 / 1024 "total(M)",
(a.bytes - nvl(b.bytes, 0)) / 1024 / 1024 "free(M)"
FROM (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) b
WHERE a.tablespace_name = b.tablespace_name(+)
select tablespace_name,
round(sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))),
2) FSFI,
(case
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) > = 30 then
'正常'
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) < 30 then
'表空間破碎化程度高,請整理'
end) Prompt
from dba_free_space
group by tablespace_name
order by 2;
--排除SYSAUX和UNDOTBS1
整理碎片: alter tablespace coalesce;
檢視單個資料檔案使用情況:
select distinct d.name,
t.name,
round(s.bytes / (1024 * 1024), 0) total_space,
round(s.bytes / (1024 * 1024), 0)-round(f.bytes / (1024 * 1024), 0) used
from v$tablespace t, v$datafile d, dba_data_files s, dba_free_space f
where t.ts# = d.ts#
and d.name = s.file_name
and s.file_id = f.file_id
and d.file# = f.file_id
and t.name = f.tablespace_name
表空間使用率:
SELECT UPPER(F.TABLESPACE_NAME) "表空間名",
D.TOT_GROOTTE_MB "表空間大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),'990.99') || '%' "使用比(%)",
F.TOTAL_BYTES "空閒空間(M)",
F.MAX_BYTES "最大塊(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 desc;
檢視某個表空間實際剩餘空間sql:
select ((
nvl((select sum(t.maxblocks*8/1024) from dba_data_files t where t.autoextensible='YES'and t.tablespace_name = 'SYSTEM'),0)
+
nvl((select sum(t.blocks*8/1024) from dba_data_files t where t.autoextensible='NO'and t.tablespace_name = 'SYSTEM'),0))
-
(
SELECT D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空間(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME AND D.TABLESPACE_NAME = 'SYSTEM'
)) "實際剩餘空間(M)"
from dual;
select tot.file_name,100*(tot.total-free.total)/tot.total||'%' from
(select file_name,file_id,sum(bytes) as total from dba_data_files tot group by file_id,file_name ) tot,
(select file_id,sum(bytes) as total from dba_free_space free group by file_id) free
where tot.file_id=free.file_id
經測試,檢視資料檔案使用率,效能較好,較準確的:
SELECT B.TABLESPACE_NAME,
B.FILE_NAME,
ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
CASE
WHEN MAXBYTES = 0
THEN
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
ELSE
ROUND (
( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
END
"TOTAL FREE SPACE GB"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
B.FILE_NAME,
B.BYTES,
B.MAXBYTES
ORDER BY 1, 2;
檢視TEMP表空間使用情況:
SELECT a.tablespace_name,
a.BYTES / 1024 / 1024 "total(M)",
(a.bytes - nvl(b.bytes, 0)) / 1024 / 1024 "free(M)"
FROM (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) b
WHERE a.tablespace_name = b.tablespace_name(+)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29135257/viewspace-1744350/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 常用SQL技巧彙總SQL
- 表空間sqlSQL
- My SQL常用操作彙總SQL
- 常用SQL語句彙總SQL
- SQL特性彙總表SQL
- RDSforSQLserver空間問題排查彙總SQLServer
- undo表空間總結
- MySQL sys庫常用SQL彙總大全MySql
- MySql常用操作SQL語句彙總MySql
- undo表空間中常用的操作
- SQL Performance Analyzer SPA常用指令碼彙總SQLORM指令碼
- 總結-表空間傳輸
- 常用的PB時間函式彙總函式
- oracle sql 表空間利用率OracleSQL
- Oracle SQL 基本操作之 表空間OracleSQL
- 修改表空間的SQL程式碼SQL
- 資料庫常用的sql語句彙總資料庫SQL
- Oracle表空間維護總結Oracle
- 臨時表空間操作總結
- ORACLE臨時表空間總結Oracle
- 檢視單個SQL消耗TEMP表空間以及TEMP表空間使用率SQL
- Oracle 表空間查詢相關sqlOracleSQL
- 常用js彙總JS
- Oracle - 表空間相關常用操作語句Oracle
- mysql關於表空間的總結MySql
- Oracle的UNDO表空間管理總結Oracle
- sql檢視所有表空間使用情況SQL
- 分析表空間空閒率並收縮表空間
- Oracle DB 相關常用sql彙總6[知乎系列續]OracleSQL
- 常用函式彙總函式
- Oracle 常用方法彙總Oracle
- PostgreSQL資料庫修改sql表的方法彙總SQL資料庫
- 達夢資料庫表空間等空間大小查詢方法總結資料庫
- oracle的表空間、分割槽表、以及索引的總結Oracle索引
- 有關表空間查詢的sql指令碼SQL指令碼
- 貼一個求表空間的sql 語句SQL
- 檢視SQL SERVER表的空間使用情況SQLServer
- MySQL InnoDB 共享表空間和獨立表空間MySql