查詢數(shù)據(jù)庫空間(mysql和oracle)
更新時間：2023年04月18日 10:54:44 作者：悸%動
本文通過代碼示例詳細介紹了如何查詢MySQL數(shù)據(jù)空間和Oracle數(shù)據(jù)空間，具有一定的參考價值，感興趣的小伙伴可以參考閱讀
Mysql版
1、查看所有數(shù)據(jù)庫容量大小
-- 查看所有數(shù)據(jù)庫容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
sum( table_rows ) AS '記錄數(shù)',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
sum( data_length ) DESC,
sum( index_length ) DESC;
2、查看所有數(shù)據(jù)庫各表容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
table_name AS '表名',
table_rows AS '記錄數(shù)',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
ORDER BY
data_length DESC,
index_length DESC;
3、查看指定數(shù)據(jù)庫容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
sum( table_rows ) AS '記錄數(shù)',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = '數(shù)據(jù)庫名';
4.查看指定數(shù)據(jù)庫各表容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
table_name AS '表名',
table_rows AS '記錄數(shù)',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = '數(shù)據(jù)庫名'
ORDER BY
data_length DESC,
index_length DESC;
5.查看指定數(shù)據(jù)庫各表信息
SHOW TABLE STATUS;
oracle版
1、查看表所占的空間大小
-- 不需要DBA權限 SELECT SEGMENT_NAME TABLENAME,(BYTES/1024/1024) MB ,RANK() OVER (PARTITION BY NULL ORDER BY BYTES DESC) RANK_ID //根據(jù)表大小進行排序 FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE' -- 需要DBA權限,一般情況下很少會給這么高的權限,可以說這個權限基本沒有,所以一般工作中不是DBA的人不會常用到這個命令 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;
2、查看表空間的使用情況
SELECT a.tablespace_name "表空間名稱",
total / (1024 * 1024) "表空間大小(M)",
free / (1024 * 1024) "表空間剩余大小(M)",
(total - free) / (1024 * 1024 ) "表空間使用大小(M)",
total / (1024 * 1024 * 1024) "表空間大小(G)",
free / (1024 * 1024 * 1024) "表空間剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
3、查看回滾段名稱及大小
SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name;
4、查看控制文件
SELECT NAME FROM v$controlfile;
5、查看日志文件
SELECT MEMBER FROM v$logfile;
6、查看數(shù)據(jù)庫對象
SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status;
7、查看數(shù)據(jù)庫版本
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';
8、查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式
SELECT created, log_mode, log_mode FROM v$database;
9、查看表空間是否具有自動擴展的能力
SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME;
oracle加強版
一、查看表空間使用率
1.查看數(shù)據(jù)庫表空間文件:
--查看數(shù)據(jù)庫表空間文件 select * from dba_data_files;
2.查看所有表空間的總容量：
--查看所有表空間的總容量 select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB from dba_data_files dba group by dba.TABLESPACE_NAME;
3.查看數(shù)據(jù)庫表空間使用率
--查看數(shù)據(jù)庫表空間使用率 select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct from ( select tablespace_name, sum(bytes) /1024/1024 as MB from dba_free_space group by tablespace_name) free, (select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name order by used_pct desc;
4.1.查看表空間總大小、使用率、剩余空間
--查看表空間總大小、使用率、剩余空間 select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by a.tablespace_name
4.2.查看表空間使用率(包含temp臨時表空間)
--查看表空間使用率(包含臨時表空間) select * from ( Select a.tablespace_name, (a.bytes- b.bytes) "表空間使用大小(BYTE)", a.bytes/(1024*1024*1024) "表空間大小(GB)", b.bytes/(1024*1024*1024) "表空間剩余大小(GB)", (a.bytes- b.bytes)/(1024*1024*1024) "表空間使用大小(GB)", to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率" from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union all select c.tablespace_name, d.bytes_used "表空間使用大小(BYTE)", c.bytes/(1024*1024*1024) "表空間大小(GB)", (c.bytes-d.bytes_used)/(1024*1024*1024) "表空間剩余大小(GB)", d.bytes_used/(1024*1024*1024) "表空間使用大小(GB)", to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率" from (select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name,sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name ) order by tablespace_name
5.查看具體表的占用空間大小
--查看具體表的占用空間大小 select * from ( select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb from dba_segments t where t.segment_type='TABLE' group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type ) t order by t.mb desc
二、擴展大小或增加表空間文件
1.更改表空間的dbf數(shù)據(jù)文件分配空間大小
alter database datafile ‘...\system_01.dbf' autoextend on; alter database datafile ‘...\system_01.dbf' resize 1024M;
2. 為表空間新增一個數(shù)據(jù)文件(表空間滿32G不能擴展則增加表空間文件)
alter tablespace SYSTEM add datafile '/****' size 1000m autoextend on next 100m;
3. 如果是temp臨時表新增表空間會報錯:
0RA-03217: 變更TEMPORARY TABLESPACE 無效的選項
解決方法: datafile改為tempfile
alter tablespace TEMP01 add tempfile'/****' size 1000m autoextend on next 100m maxsize 10000m
針對temp臨時表空間使用率爆滿問題
臨時表空間主要用途是在數(shù)據(jù)庫進行排序運算、管理索引、訪問視圖等操作時提供臨時的運算空間，當運算完成之后系統(tǒng)會自動清理，但有些時候我們會遇到臨時段沒有被釋放，TEMP表空間幾乎滿使用率情況；
引起臨時表空間增大主要使用在以下幾種情況：
1、order by or group by (disc sort占主要部分)；
2、索引的創(chuàng)建和重創(chuàng)建；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些異常也會引起TEMP的暴漲。
解決方法一：用上述方法給temp增加表空間文件
解決方法二：在服務器資源空間有限的情況下，重新建立新的臨時表空間替換當前的表空間
--1.查看當前的數(shù)據(jù)庫默認表空間： select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; --2.創(chuàng)建新的臨時表空間 create temporary tablespace TEMP01 tempfile '/home/temp01.dbf' size 31G; --3.更改默認臨時表空間 alter database default temporary tablespace TEMP01; --4.刪除原來的臨時表空間 drop tablespace TEMP02 including contents and datafiles; --如果刪除原來臨時表空間報錯ORA-60100：由于排序段，已阻止刪除表空間... --（說明有語句正在使用原來的臨時表空間，需要將其kill掉再刪除，此語句多為排序的語句） --查詢語句 Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space, tablespace,segtype,sql_text from v$sort_usage su,v$parameter p,v$session se,v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr order by se.username,se.sid; --刪除對應的'sid,serial#' alter system kill session 'sid,serial#'
附：查看表空間是否具有自動擴展的能力
--查看表空間是否具有自動擴展的能力 SELECT T.TABLESPACE_NAME,D.FILE_NAME, D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS FROM DBA_TABLESPACES T,DBA_DATA_FILES D WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME ORDER BY TABLESPACE_NAME,FILE_NAME;
以上就是查詢數(shù)據(jù)庫空間(mysql和oracle)的詳細內容，更多關于查詢數(shù)據(jù)庫空間的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要給大家介紹了關于MySQL中長事務的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-09-09
mysql8.0 windows x64 zip包安裝配置教程
這篇文章主要為大家詳細介紹了mysql8.0 windows x64 zip包安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-05-05
這篇文章主要介紹了Mysql如何在linux中實現(xiàn)定時備份,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-09-09
無論是在小得可憐的免費數(shù)據(jù)庫空間或是大型電子商務網(wǎng)站，合理的設計表結構、充分利用空間是十分必要的。這就要求我們對數(shù)據(jù)庫系統(tǒng)的常用數(shù)據(jù)類型有充分的認識。下面我就將我的一點心得寫出來跟大家分享。2008-04-04
MySQL5.7中的sql_mode默認值帶來的坑及解決方法
這篇文章主要介紹了MySQL5.7中的sql_mode默認值帶來的坑及解決方法,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2018-11-11
這篇文章主要給大家介紹了關于Mysql大型SQL文件快速恢復方案的相關資料，文中通過示例代碼介紹的非常詳細，對大家學習或者使用Mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧2019-09-09
在本文里我們給大家總結了關于MySQL短鏈接的實操方法和相關知識點，有需要的朋友們跟著學習下。2019-03-03
這篇文章主要介紹了MySQL 5.7.30 的安裝與升級教程（所有可能的坑都在這里）,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-05-05
審計插件是包含在 MariaDB 中的,所以需要先下載 MariaDB 然后將 server_audit.so 審計插件 copy 出來,這篇文章主要介紹了MySQL 使用開源審計插件,需要的朋友可以參考下2023-08-08
這篇文章主要介紹了Mysql 5.7.17安裝后登錄mysql的教程以及mysql5.7.17的安裝方法，需要的朋友參考下吧2017-01-01