有关oracle表空间查询的一些记录

1.查询剩余表空间

select sum(bytes/1024/1024) sizeMB from dba_free_space z where z.tablespace_name='DIMS_INVALID_PICTURE2'

2.查询索引信息

select s.index_name,s.table_name,s.tablespace_name,s.initial_extent,s.next_extentfrom user_indexes s where s.index_name = 'IDX_INVALID_PICNAME'

3.查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;

4.表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' group by segment_name;

5.索引占用空间

select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;

6.分区表TABLE PARTITION占用空间

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;

7.通过加大数据文件

alter database datafile 'D:\ORACLE\ORADATA\DMSDB\DIMS_INVALID_PICTURE2.DBF' resize 1000M
8.重建该索引 

alter index index_name rebuild tablespace indexes storage(initial 256K next 256K pctincrease 0):