整理了下这两天用到的oracle 查询语句，作为记录。

1，查询当前用户所有的表信息

select * from user_tables order by table_name;

2，查询表空间的使用情况

?

select ? a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 ? "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used" ?

from ?

(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) ? a, ?

(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) ? b ?

where ? a.tablespace_name=b.tablespace_name ?

order ? by ? ((a.bytes-b.bytes)/a.bytes) ? desc ?;

?

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

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments? group by segment_name;
? ? 3.1、表占用空间
? ?? select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type='TABLE' ?group by segment_name order by ( sum(bytes)/1024/1024) desc;
? ? 3.2、索引占用空间

select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type ='INDEX' group by segment_name;
? ? 3.3、分区表TABLE PARTITION占用空间

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

4，设置给指定的表空间增加数据文件

ALTER TABLESPACE tablespacename ?add datafile 'F:\oracle\product\10.2.0\oradata\eas0304\EAS_D_EAS_STANDARD06.DBF' size 10G;

?

5，查询表空间的磁盘碎片

select tablespace_name,count(*) chunks ,max(bytes/1024/1024) max_chunk?from dba_free_space?group by tablespace_name;

?