【oracle】
查询表空间存路径位置:
select file_name,tablespace_name from dba_data_files;
查询所有表空间::
SELECT A.TABLESPACE_NAME "表空间名",
A.TOTAL_SPACE "总空间(M)",
NVL(B.FREE_SPACE, 0) "剩余空间(M)",
A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(M)",
trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) "剩余百分比%"
FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024 ,2) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024 ),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
查询指定表空间内各表大小(用户级):
SELECT a.OWNER, a.SEGMENT_NAME ,b.table_name, sum(a.bytes), a.SEGMENT_TYPE
FROM DBA_SEGMENTS a,User_Tables b
WHERE a.TABLESPACE_NAME = '表空间名称(要修改)'
and a.segment_name=b.table_name
group by a.OWNER, a.SEGMENT_NAME,b.table_name,a.SEGMENT_TYPE
order by sum(bytes) desc
查询指定表空间内各表大小(dba级):
SELECT a.OWNER, a.SEGMENT_NAME ,b.table_name, sum(a.bytes), a.SEGMENT_TYPE
FROM DBA_SEGMENTS a,dba_tables b
WHERE a.TABLESPACE_NAME = '表空间名称(要修改)'
and a.segment_name=b.table_name
group by a.OWNER, a.SEGMENT_NAME,b.table_name,a.SEGMENT_TYPE
order by sum(bytes) desc
【mysql】
mysql 查看表的大小方法:
查看所有数据库容量大小:
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;
查看所有数据库各表容量大小:
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;
查看指定数据库容量大小:
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 = 'mysql';
查看指定数据库各表容量大小:
例:查看mysql库各表容量大小
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 = 'mysql'
ORDER BY
data_length DESC,
index_length DESC;