查询数据库空间(mysql和oracle)

Mysql版

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

-- 查看所有数据库容量大小SELECTtable_schema AS '数据库',sum( table_rows ) AS '记录数',sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' FROMinformation_schema.TABLES GROUP BYtable_schema ORDER BYsum( data_length ) DESC,sum( index_length ) DESC;

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

SELECTtable_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' FROMinformation_schema.TABLES ORDER BYdata_length DESC,index_length DESC;

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

SELECTtable_schema AS '数据库',sum( table_rows ) AS '记录数',sum(TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',sum(TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' FROMinformation_schema.TABLES WHEREtable_schema = '数据库名';

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

SELECTtable_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' FROMinformation_schema.TABLES WHEREtable_schema = '数据库名' ORDER BYdata_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_SEGMENTSWHERE 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) freeFROM dba_free_spaceGROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) totalFROM dba_data_filesGROUP BY tablespace_name) bWHERE 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.STATUSFROM DBA_TABLESPACES T,DBA_DATA_FILES DWHERE 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 MBfrom 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%"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) bwhere a.tablespace_name = b.tablespace_nameorder 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) bytesfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name,sum(bytes) bytesfrom dba_free_spacegroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameunion allselect 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) bytesfrom dba_temp_files group by tablespace_name) c,(select tablespace_name,sum(bytes_cached) bytes_usedfrom v$temp_extent_pool group by tablespace_name) dwhere 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) mbfrom dba_segments twhere t.segment_type='TABLE'group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type) torder 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_propertieswhere 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_textfrom v$sort_usage su,v$parameter p,v$session se,v$sql swhere p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhashand s.address=su.sqladdrorder 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_NAMEORDER BY TABLESPACE_NAME,FILE_NAME;