Oracle数据库数据量统计

1、单表数据量统计：
SELECT segment_name AS TABLENAME,BYTES FROM user_segments WHERE segment_name='表名';

2、查询各表空间数据量（MB）：
select tablespace_name,(sum(bytes)/1024/1024) as MB from dba_data_files group by tablespace_name;

3、查询某个用户所属表空间名
select default_tablespace from dba_users where username='SCOTT';

4、查询某个表空间下有哪些用户
select distinct owner from dba_segments where tablespace_name = 'USERS';

5、查看前一天所有表空间的增长量

select C.tablespace_name,
D."Total(MB)",
D."Used(MB)" - C."Used(MB)" AS "Increment(MB)",
to_char(trunc(sysdate - 1),'yyyy/mm/dd') "TIME"
from (select B.name tablespace_name,
case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
END as "Total(MB)",
round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
A.rtime
from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
where A.tablespace_id = B.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate - 1), 'yyyymmdd hh24:mi')) C,
(select B.name tablespace_name,
case when B.name not like 'UNDO%' then round(A.tablespace_size * 8 / 1024)
when B.name like 'UNDO%' then round(A.tablespace_size * 8 / 1024 / 2)
END as "Total(MB)",
round(A.tablespace_usedsize*8 / 1024) "Used(MB)",
A.rtime
from DBA_HIST_TBSPC_SPACE_USAGE A, v$tablespace B
where A.tablespace_id = B.TS#
and to_char(to_date(replace(rtime, '/', null),
'mmddyyyy hh24:mi:ss'),
'yyyymmdd hh24:mi') =
to_char(trunc(sysdate), 'yyyymmdd hh24:mi')) D
where C.tablespace_name = D.tablespace_name;