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

Mysql版

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

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

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

SELECT
table_schemaAS'数据库',
table_nameAS'表名',
table_rowsAS'记录数',
TRUNCATE(data_length/1024/1024,2)AS'数据容量(MB)',
TRUNCATE(index_length/1024/1024,2)AS'索引容量(MB)'
FROM
information_schema.TABLES
ORDERBY
data_lengthDESC,
index_lengthDESC;
    

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

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

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

SELECT
table_schemaAS'数据库',
table_nameAS'表名',
table_rowsAS'记录数',
TRUNCATE(data_length/1024/1024,2)AS'数据容量(MB)',
TRUNCATE(index_length/1024/1024,2)AS'索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema='数据库名'
ORDERBY
data_lengthDESC,
index_lengthDESC;
    

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

SHOWTABLESTATUS;
    

oracle版

1、查看表所占的空间大小

--不需要DBA权限
SELECTSEGMENT_NAMETABLENAME,(BYTES/1024/1024)MB
,RANK()OVER(PARTITIONBYNULLORDERBYBYTESDESC)RANK_ID//根据表大小进行排序
FROMUSER_SEGMENTS
WHERESEGMENT_TYPE='TABLE'

--需要DBA权限,一般情况下很少会给这么高的权限,可以说这个权限基本没有,所以一般工作中不是DBA的人不会常用到这个命令
SELECTt.tablespace_name,round(SUM(bytes/(1024*1024)),0)ts_size
FROMdba_tablespacest,dba_data_filesd
WHEREt.tablespace_name=d.tablespace_name
GROUPBYt.tablespace_name;
    

2、查看表空间的使用情况

SELECTa.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(SELECTtablespace_name,SUM(bytes)free
FROMdba_free_space
GROUPBYtablespace_name)a,
(SELECTtablespace_name,SUM(bytes)total
FROMdba_data_files
GROUPBYtablespace_name)b
WHEREa.tablespace_name=b.tablespace_name

3、查看回滚段名称及大小

SELECTsegment_name,
tablespace_name,
r.status,
(initial_extent/1024)initialextent,
(next_extent/1024)nextextent,
max_extents,
v.curextcurextent
FROMdba_rollback_segsr,v$rollstatv
WHEREr.segment_id=v.usn(+)
ORDERBYsegment_name;
    

4、查看控制文件

SELECTNAMEFROMv$controlfile;
    

5、查看日志文件

SELECTMEMBERFROMv$logfile;
    

6、查看数据库对象

SELECTowner,object_type,status,COUNT(*)count#
FROMall_objects
GROUPBYowner,object_type,status;
    

7、查看数据库版本

SELECTversion
FROMproduct_component_version
WHEREsubstr(product,1,6)='Oracle';
    

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

SELECTcreated,log_mode,log_modeFROMv$database;
    

9、查看表空间是否具有自动扩展的能力

SELECTT.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROMDBA_TABLESPACEST,DBA_DATA_FILESD
WHERET.TABLESPACE_NAME=D.TABLESPACE_NAME
ORDERBYTABLESPACE_NAME,FILE_NAME;
    

oracle加强版

一、查看表空间使用率

1.查看数据库表空间文件:

--查看数据库表空间文件
select*fromdba_data_files;
    

2.查看所有表空间的总容量：

--查看所有表空间的总容量
selectdba.TABLESPACE_NAME,sum(bytes)/1024/1024asMB
fromdba_data_filesdba
groupbydba.TABLESPACE_NAME;
    

3.查看数据库表空间使用率

--查看数据库表空间使用率
selecttotal.tablespace_name,round(total.MB,2)asTotal_MB,round(total.MB-free.MB,2)asUsed_MB,round((1-free.MB/total.MB)*100,2)||'%'asUsed_Pct
from(
selecttablespace_name,sum(bytes)/1024/1024asMB
fromdba_free_spacegroupbytablespace_name)free,
(selecttablespace_name,sum(bytes)/1024/1024asMB
fromdba_data_filesgroupbytablespace_name)total
wherefree.tablespace_name=total.tablespace_name
orderbyused_pctdesc;
    

4.1.查看表空间总大小、使用率、剩余空间

--查看表空间总大小、使用率、剩余空间
selecta.tablespace_name,total,free,total-freeasused,substr(free/total*100,1,5)as"FREE%",substr((total-free)/total*100,1,5)as"USED%"
from
(selecttablespace_name,sum(bytes)/1024/1024astotalfromdba_data_filesgroupbytablespace_name)a,
(selecttablespace_name,sum(bytes)/1024/1024asfreefromdba_free_spacegroupbytablespace_name)b
wherea.tablespace_name=b.tablespace_name
orderbya.tablespace_name

4.2.查看表空间使用率(包含temp临时表空间)

--查看表空间使用率(包含临时表空间)
select*from(
Selecta.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(selecttablespace_name,
sum(bytes)bytes
fromdba_data_files
groupbytablespace_name)a,
(selecttablespace_name,
sum(bytes)bytes
fromdba_free_space
groupbytablespace_name)b
wherea.tablespace_name=b.tablespace_name
unionall
selectc.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
(selecttablespace_name,sum(bytes)bytes
fromdba_temp_filesgroupbytablespace_name)c,
(selecttablespace_name,sum(bytes_cached)bytes_used
fromv$temp_extent_poolgroupbytablespace_name)d
wherec.tablespace_name=d.tablespace_name
)
orderbytablespace_name

5.查看具体表的占用空间大小

--查看具体表的占用空间大小
select*from(
selectt.tablespace_name,t.owner,t.segment_name,t.segment_type,sum(t.bytes/1024/1024)mb
fromdba_segmentst
wheret.segment_type='TABLE'
groupbyt.tablespace_name,t.OWNER,t.segment_name,t.segment_type
)t
orderbyt.mbdesc

二、扩展大小或增加表空间文件

1.更改表空间的dbf数据文件分配空间大小

alterdatabasedatafile‘...\system_01.dbf'autoextendon;
    
alterdatabasedatafile‘...\system_01.dbf'resize1024M;
    

2. 为表空间新增一个数据文件(表空间满32G不能扩展则增加表空间文件)

altertablespaceSYSTEMadddatafile'/****'size1000mautoextendonnext100m;
    

3. 如果是temp临时表新增表空间会报错:

0RA-03217: 变更TEMPORARY TABLESPACE 无效的选项
解决方法: datafile改为tempfile

altertablespaceTEMP01addtempfile'/****'size1000mautoextendonnext100mmaxsize10000m

针对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*fromdatabase_properties
whereproperty_name='DEFAULT_TEMP_TABLESPACE';
    

--2.创建新的临时表空间
createtemporarytablespaceTEMP01tempfile
'/home/temp01.dbf'size31G;
    

--3.更改默认临时表空间
alterdatabasedefaulttemporarytablespaceTEMP01;
    

--4.删除原来的临时表空间
droptablespaceTEMP02includingcontentsanddatafiles;
    

--如果删除原来临时表空间报错ORA-60100：由于排序段，已阻止删除表空间...
--（说明有语句正在使用原来的临时表空间，需要将其kill掉再删除，此语句多为排序的语句）
--查询语句
Selectse.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))asSpace,
tablespace,segtype,sql_text
fromv$sort_usagesu,v$parameterp,v$sessionse,v$sqls
wherep.name='db_block_size'andsu.session_addr=se.saddrands.hash_value=su.sqlhash
ands.address=su.sqladdr
orderbyse.username,se.sid;
    

--删除对应的'sid,serial#'
altersystemkillsession'sid,serial#'

附：查看表空间是否具有自动扩展的能力

--查看表空间是否具有自动扩展的能力
SELECTT.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROMDBA_TABLESPACEST,DBA_DATA_FILESD
WHERET.TABLESPACE_NAME=D.TABLESPACE_NAME
ORDERBYTABLESPACE_NAME,FILE_NAME;
    

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

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！