oracle数据文件优化常用sql

oracle使用过程中由于经常对数据增删改查，可能某些表占用的空间很大，但是其实际空间不是很大，这里记录了几个经常使用到的

sql，来帮助查看数据大小

1、查看数据文件及其大小

select file_name, bytes / 1024 / 1024 
  from dba_data_files;

2、查看Oracle用户占了哪几个表空间及大小

select *
  from (select owner || '.' || tablespace_name name, sum(b) g
          from (select owner,
                       t.segment_name,
                       t.partition_name,
                       round(bytes / 1024 / 1024 / 1024, 2) b,
                       tablespace_name
                  from dba_segments t)
         where owner not in
               ('SYS', 'OUTLN', 'SYSTEM', 'TSMSYS', 'DBSNMP', 'WMSYS')
         group by owner || '.' || tablespace_name)
 order by name

3、查看表空间，总共大小，剩余空间，已使用

select a.tablespace_name, total, free, total - free used
  from (select tablespace_name, sum(bytes) / 1024 / 1024 total
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) / 1024 / 1024 free
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by free;

4、如何查看oracle表空间已使用大小

select b.file_id 文件ID号,
       b.tablespace_name 表空间名,
       b.bytes / 1024 / 1024 || 'M' 字节数,
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 || 'M' 已使用,
       sum(nvl(a.bytes, 0)) / 1024 / 1024 || 'M' 剩余空间,
       100 - sum(nvl(a.bytes, 0)) / (b.bytes) * 100 占用百分比
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 group by b.tablespace_name, b.file_id, b.bytes
 order by b.file_id;

5、查看表空间中数据文件存放的路径：

select file_name,tablespace_name, bytes / 1024 / 1024 file_size_mb
   from dba_data_files;

6、查询当前用户下面对象大小

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

7、dbf文件重置大小

alter database datafile '/u01/app/oradata/dpcc/users02.dbf' RESIZE 100M;

删除表空间下面的某一个文件

ALTER TABLESPACE testSapce  DROP DATAFILE '2.dbf';

8、

select sum(t.MB)
  from (select segment_name, bytes / 1024 / 1024 as MB
          from user_segments u
         where segment_type = 'TABLE') t;

9、.查询表多少行数据

select t.TABLE_NAME, t.NUM_ROWS
  from user_tables t
 order by t.NUM_ROWS desc;

select owner, segment_name, segment_type, sum(bytes)
  from dba_extents
 where file_id = 4
 group by owner, segment_name, segment_type
 order by 3 desc;

10、经常需要使用到的表

dba_data_files   所有
dba_extents    已使用
dba_free_space  空闲
user_segments
dba_segments
user_tables
dba_tables
dba_indexes
dba_temp_files
user_lobs --  怎么察看LOB类型的段到底属于哪个表