select * from dba_segments;
select * from user_segments;
select
d.tablespace_name,
d.gbytes "total[GB]",
NVL(f.gbytes,0) "free[GB]",
d.gbytes - NVL(f.gbytes,0) "used[GB]",
(1 - (NVL(f.gbytes,0)/d.gbytes))*100 "used_percent"
from
(SELECT tablespace_name, (SUM(bytes)/(1024*1024*1024)) gbytes
FROM dba_data_files GROUP BY tablespace_name) d
left outer join
(SELECT tablespace_name, (SUM(bytes)/(1024*1024*1024)) gbytes
FROM dba_free_space GROUP BY tablespace_name) f
on d.tablespace_name=f.tablespace_name
;
https://qiita.com/ikenji/items/b868877492fee60d85ce
SELECT
table_schema, sum(data_length) /1024/1024 AS mb
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
sum(data_length+index_length) DESC;
SELECT
table_name, engine, table_rows AS tbl_rows,
avg_row_length AS rlen,
floor ( ( data_length+index_length)/1024/1024) AS allmb,
floor ( ( data_length)/1024/1024) AS dmb,
floor ( ( index_length)/1024/1024) AS imb
FROM
information_schema.tables
WHERE
table_schema=database()
ORDER BY
(data_length+index_length) DESC;
SELECT
relname,
relnamespace,
relkind,
reltuples,
(relpages / 128) as mbytes,
(relpages * 8192.0 / (reltuples + 1e-10)) as average_row_size
FROM pg_class
ORDER BY relnamespace, relkind,relname;
SELECT oid,nspname,nspowner FROM pg_namespace order by oid;
select pg_database_size('test');
select pg_total_relation_size('tab1');
select pg_relation_size('t_oil');
SELECT pg_size_pretty(pg_relation_size('t_oil'));
--データベースサイズ
use test
go
EXEC sp_spaceused;
go
sp_helpdb
go
sp_helpdb 'test'
go
--テーブルサイズ、インデックスサイズ
exec sp_spaceused 'dbo.tab1';
go
--tempdbサイズ
USE tempdb
GO
SELECT
SUM (user_object_reserved_page_count) * 1.0 / 128 as user_object_mb,
SUM (internal_object_reserved_page_count) * 1.0 / 128 as internal_objects_mb,
SUM (version_store_reserved_page_count) * 1.0 / 128 as version_store_mb,
SUM (unallocated_extent_page_count) * 1.0 / 128 as free_space_mb,
SUM (mixed_extent_page_count) * 1.0 / 128 as mixed_extents_mb
FROM sys.dm_db_file_space_usage
