檢視oracle資料庫----size
檢視物理size和實際size
select name,sum(dbsz)/1024/1024 "Physical siZe MB" from
(select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log),v$database
group by name
OR
select name,sum(a.dbsz)/1024/1024 "Physical siZe MB",b.actsz/1024/1024 "Actual Size in Mb"
from (select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log) a ,
(select sum(bytes) actsz from dba_extents) b ,v$database
group by name,b.actsz
NAME Physical siZe MB Actual Size in Mb
--------- ---------------- -----------------
ORA11G 28348.8125 22796.625
(select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log),v$database
group by name
OR
select name,sum(a.dbsz)/1024/1024 "Physical siZe MB",b.actsz/1024/1024 "Actual Size in Mb"
from (select sum(bytes) dbsz from dba_data_files
union all
select sum(bytes) dbsz from dba_temp_files
union all
select sum(bytes) dbsz from v$log) a ,
(select sum(bytes) actsz from dba_extents) b ,v$database
group by name,b.actsz
NAME Physical siZe MB Actual Size in Mb
--------- ---------------- -----------------
ORA11G 28348.8125 22796.625
查詢表空間使用情況
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 1
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 1
--查詢表空間的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
--查詢表空間的總容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查詢表空間使用率
SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
round(b.bytes / 1024 / 1024) "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
round(b.bytes / 1024 / 1024) "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27000195/viewspace-731847/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【轉】檢視Oracle資料庫阻塞Oracle資料庫
- 檢視oracle資料庫真實大小Oracle資料庫
- Oracle 資料庫字典 檢視 基表Oracle資料庫
- 檢視oracle資料庫字符集Oracle資料庫
- 檢視各項Oracle資料庫資訊Oracle資料庫
- ORACLE之檢視資料庫的SQLOracle資料庫SQL
- 資料庫檢視資料庫
- 資料庫-檢視資料庫
- ORACLE資料庫檢視執行計劃Oracle資料庫
- 檢視oracle資料庫的連線數以及使用者檢視Oracle資料庫
- ORACLE資料庫檢視ACQ(ACTIVE CHECKPOINT QUEUE)資訊Oracle資料庫
- Oracle資料庫中物化檢視的原理剖析Oracle資料庫
- 檢視oracle資料庫是否為歸檔模式Oracle資料庫模式
- Oracle資料庫檢視使用者狀態Oracle資料庫
- MySQL資料庫檢視:檢視定義、建立檢視、修改檢視MySql資料庫
- 資料庫無法建立資料庫檢視資料庫
- 【檢視】oracle 資料字典檢視之 DICT / DICTIONARYOracle
- Oracle 資料庫檢視與基表的關係Oracle資料庫
- Oracle常用命令 檢視資料庫的SQLOracle資料庫SQL
- Oracle資料庫巡檢Oracle資料庫
- 資料庫檢視詳解資料庫
- 檢視資料庫的版本資料庫
- 資料庫的物化檢視資料庫
- 資料庫檢視的作用資料庫
- 資料庫檢視的使用資料庫
- oracle常用資料字典.檢視Oracle
- MySQL檢視當前資料庫庫MySql資料庫
- 持續更新,檢視dump oracle資料塊檢視Oracle
- 2.12 資料庫資料字典檢視資料庫
- Oracle 資料字典和資料字典檢視Oracle
- Oracle資料庫DBA日常Sql列表及常用檢視(轉)Oracle資料庫SQL
- oracle 資料庫裡檢視錶空間使用狀況;Oracle資料庫
- 爛筆頭——Oracle檢視資料庫開啟狀態Oracle資料庫
- 【檢視】oracle 資料字典檢視之 “小”檢視 CAT, TAB, SEQ, SYN ...Oracle
- oracle資料庫巡檢(轉)Oracle資料庫
- 什麼是資料庫檢視?資料庫
- 檢視MySQL資料庫的命令MySql資料庫
- 檢視資料庫中的物件資料庫物件