引用 :/cosio/article/details/3978747 , /question/
block 块
extent 区
segment 段
--增加USERS表空间500M空间
alter tablespace USERS add datafile '/data01/oradata/bidb96/USER_test30.bdf' size 500M;
select file_name , tablespace_name, t.* from dba_data_files t where tablespace_name = 'USERS' order by file_id;
--每个表占用的空间
select segment_name,sum(bytes )/1024/1024/1024 GB
from user_segments --where segment_name ='TR_GOV_WJ_XNH_D'
group by segment_name ;
--20190530这天TW层占用的空间
select count(distinct segment_name) tb_cnt,sum(bytes)/(1024*1024*1024) GB
from user_segments t
where partition_name='P_20190530'
and segment_name like 'TW%';
----查看剩余表空间 汇总
SELECT t1 表空间,z 总表空间,z-s 已用表空间,s 剩余表空间,ROUND((z-s)/z*100,2) "使用率%"
From (Select tablespace_name t1,Sum(bytes)/(1024*1024*1024) s
From DBA_FREE_SPACE Group by tablespace_name),
(Select tablespace_name t2,Sum(bytes)/(1024*1024*1024) z
From DBA_DATA_FILES Group by tablespace_name) Where t1=t2;
---表空间总共的空间
select tablespace_name,sum(bytes)/(1024*1024*1024) GB from dba_data_files
group by tablespace_name
;
--表空间剩余空间
select tablespace_name,sum(bytes)/(1024*1024*1024) GB from user_free_space
group by tablespace_name
;
--表空间占用的空间 表空间所有对象的物理占用(表，分区，索引等) 这个和DBA_DATA_FILES的大小才一样
select tablespace_name,sum(bytes)/(1024*1024*1024) GB from user_segments
group by tablespace_name
;
----表实际使用大小 这个大小相加必定
select table_name,(num_rows * avg_row_len )/(1024*1024*1024) 该表大小GB
from user_tables
where table_name = '表名大写'--672699条数据 0.306965947151184GB
order by 2 desc