怎么查询mysql和oracle数据库空间

共计 7330 个字符，预计需要花费 19 分钟才能阅读完成。

本篇内容主要讲解“怎么查询 mysql 和 oracle 数据库空间”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么查询 mysql 和 oracle 数据库空间”吧!

Mysql 版 1、查看所有数据库容量大小

--  查看所有数据库容量大小
SELECT
 table_schema AS  数据库 ,
 sum( table_rows ) AS  记录数 ,
 sum( TRUNCATE ( data_length / 1024 / 1024, 2 )) AS  数据容量(MB) ,
 sum( TRUNCATE ( index_length / 1024 / 1024, 2 )) AS  索引容量(MB)  
 information_schema.TABLES 
GROUP BY
 table_schema 
ORDER BY
 sum( data_length ) DESC,
 sum( index_length ) DESC;

2、查看所有数据库各表容量大小

SELECT
 table_schema AS  数据库 ,
 table_name AS  表名 ,
 table_rows AS  记录数 ,
 TRUNCATE ( data_length / 1024 / 1024, 2 ) AS  数据容量(MB) ,
 TRUNCATE ( index_length / 1024 / 1024, 2 ) AS  索引容量(MB)  
 information_schema.TABLES 
ORDER BY
 data_length DESC,
 index_length DESC;

3、查看指定数据库容量大小

SELECT
 table_schema AS  数据库 ,
 sum( table_rows ) AS  记录数 ,
 sum( TRUNCATE ( data_length / 1024 / 1024, 2 )) AS  数据容量(MB) ,
 sum( TRUNCATE ( index_length / 1024 / 1024, 2 )) AS  索引容量(MB)  
 information_schema.TABLES 
WHERE
 table_schema =  数据库名

4. 查看指定数据库各表容量大小

SELECT
 table_schema AS  数据库 ,
 table_name AS  表名 ,
 table_rows AS  记录数 ,
 TRUNCATE ( data_length / 1024 / 1024, 2 ) AS  数据容量(MB) ,
 TRUNCATE ( index_length / 1024 / 1024, 2 ) AS  索引容量(MB)  
 information_schema.TABLES 
WHERE
 table_schema =  数据库名  
ORDER BY
 data_length DESC,
 index_length DESC;

5. 查看指定数据库各表信息

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 // 根据表大小进行排序
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、查看数据库对象

SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status;

7、查看数据库版本

SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) =  Oracle

8、查看数据库的创建日期和归档方式

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. 查看数据库表空间文件:

-- 查看数据库表空间文件
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. 查看数据库表空间使用率

-- 查看数据库表空间使用率
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% 
(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) ||  %   使用率 
(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 数据文件分配空间大小

alter database datafile ‘...\system_01.dbf  autoextend on;
alter database datafile ‘...\system_01.dbf  resize 1024M;

2. 为表空间新增一个数据文件(表空间满 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 临时表空间使用率爆满问题
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理，但有些时候我们会遇到临时段没有被释放，TEMP 表空间几乎满使用率情况；
引起临时表空间增大主要使用在以下几种情况：
1、order by or group by (disc sort 占主要部分)；
2、索引的创建和重创建；
3、distinct 操作；
4、union intersect minus sort-merge joins；
5、Analyze 操作；
6、有些异常也会引起 TEMP 的暴涨。
解决方法一：用上述方法给 temp 增加表空间文件
解决方法二：在服务器资源空间有限的情况下，重新建立新的临时表空间替换当前的表空间

--1. 查看当前的数据库默认表空间：select * from database_properties
where property_name= DEFAULT_TEMP_TABLESPACE 
 
--2. 创建新的临时表空间
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;

到此，相信大家对“怎么查询 mysql 和 oracle 数据库空间”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！