常用查看数据库信息

<>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.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)' FROM 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)' FROM 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)' FROM 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_spaceGROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes)
totalFROM 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;

• « 上一篇：lingo 目标规划篇（3/3）
• » 下一篇：Vue的组件化编程