查询数据库空间(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)'FROM
    information_schema.TABLESGROUPBY
    table_schema 
ORDERBYsum( 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)'FROM
    information_schema.TABLESORDERBY
    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)'FROM
    information_schema.TABLESWHERE
    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)'FROM
    information_schema.TABLESWHERE
    table_schema ='数据库名'ORDERBY
    data_length DESC,
    index_length DESC;

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

SHOWTABLESTATUS;

oracle版

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

--  不需要DBA权限SELECT SEGMENT_NAME TABLENAME,(BYTES/1024/1024) MB
,RANK()OVER(PARTITIONBYNULLORDERBY 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 
GROUPBY 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
      GROUPBY tablespace_name) a,(SELECT tablespace_name,SUM(bytes) total
      FROM dba_data_files
      GROUPBY 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(+)ORDERBY 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 
GROUPBY 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.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
 ORDERBY TABLESPACE_NAME,FILE_NAME;

oracle加强版

一、查看表空间使用率

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

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

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

--查看所有表空间的总容量select dba.TABLESPACE_NAME,sum(bytes)/1024/1024as MB  
from dba_data_files dba 
groupby 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/1024as MB 
from dba_free_space groupby tablespace_name) free,(select tablespace_name,sum(bytes)/1024/1024as MB 
from dba_data_files groupby tablespace_name) total     
where free.tablespace_name = total.tablespace_name 
orderby 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/1024as total from dba_data_files groupby tablespace_name) a,(select tablespace_name,sum(bytes)/1024/1024as free from dba_free_space groupby tablespace_name) b
where a.tablespace_name = b.tablespace_name
orderby 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
groupby tablespace_name) a,(select tablespace_name,sum(bytes) bytes
from dba_free_space
groupby tablespace_name) b
where a.tablespace_name = b.tablespace_name
unionallselect 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 groupby tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool groupby tablespace_name) d
where c.tablespace_name = d.tablespace_name
)orderby 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'groupby t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
orderby t.mb desc

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

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

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

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

altertablespace SYSTEM add datafile '/****' size 1000m autoextend onnext100m;

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

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

altertablespace TEMP01 add tempfile'/****' size 1000m autoextend onnext100m 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.创建新的临时表空间createtemporarytablespace TEMP01 tempfile 
'/home/temp01.dbf' size 31G;--3.更改默认临时表空间alterdatabasedefaulttemporarytablespace TEMP01;--4.删除原来的临时表空间droptablespace 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
orderby se.username,se.sid;--删除对应的'sid,serial#'alter system killsession'sid,serial#'

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

--查看表空间是否具有自动扩展的能力     SELECT T.TABLESPACE_NAME,D.FILE_NAME,     
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES D     
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME     
 ORDERBY TABLESPACE_NAME,FILE_NAME;