oracle中和表空间、数据文件有关的视图经常会用到，今天总结一下，常用的视图如下：
1、DBA_TABLESPACES
记录了表空间的基本信息，重要字段有status（online、offline、read only），contents（undo、permanent、temporary）、EXTENT_MANAGEMENT（一般为local）、 SEGMENT_SPACE_MANAGEMENT（manual和auto）
2、DBA_DATA_FILES 
记录了具体数据文件的信息，重要字段有file_name,tablespace_name,bytes,blocks, AUTOEXTENSIBLE
从此表中，可以得出所有数据文件（不包括临时表空间）的大小
SQL> select sum(blocks*8192/1024/1024/1024)||'G'  from dba_data_files ;
SUM(BLOCKS*8192/1024/1024/1024)||'G'
-----------------------------------------
22.880859375G
3、DBA_TEMP_FILES
如果要得到临时表空间的大小，可以从DBA_TEMP_FILES中获取

SQL> select file_name,status,blocks*8192/1024/1024/1024||'G' from dba_temp_files;
FILE_NAME                                STATUS    BLOCKS*8192/1024/1024/1024||'G'
---------------------------------------- --------- -----------------------------------------
/home5/oradata/riji/temp02.dbf           AVAILABLE 18.06640625G
综合DBA_DATA_FILES和DBA_TEMP_FILES，可以得到所有表空间的总大小。
select
(
 (select sum(blocks*8192/1024/1024/1024) from dba_data_files)
  +
  (select sum(blocks*8192/1024/1024/1024) from dba_temp_files)
 )
  as total_size
  from dual;
例如：
SQL> select
  2  (
  3  (select sum(blocks*8192/1024/1024/1024) from dba_data_files)
  4  +
  5  (select sum(blocks*8192/1024/1024/1024) from dba_temp_files)
  6  )
  7  as total_size
  8  from dual;
TOTAL_SIZE
----------
40.9472656
4、DBA_FREE_SPACE 
记录了每个表空间中的各个数据文件目前可用空间（没有考虑扩展）。重要字段有tablespace_name,file_id,blocks,block_id（extent的起始块号）。
例如，计算当前各个表空间的可用空间sql如下：
SQL> select tablespace_name, sum(blocks*8192/1024/1024/1024)||' G' free_space from dba_free_space group by tablespace_name;
TABLESPACE_NAME                FREE_SPACE
------------------------------ ------------------------------------------
INDXNEW                        1.21160888671875 G
PERFSTAT                       .00994873046875 G
SYSTEM                         .32708740234375 G
UNDOTBS                        .548095703125 G
USERS                          1.2169189453125 G
YPAGE                          1.943359375 G
6 rows selected.
 
如果想具体到某个数据文件的目前可用空间，sql如下：
SQL> l
  1*  select tablespace_name,file_id,sum(blocks*8192/1024/1024/1024)||' G' free_space from dba_free_space group by tablespace_name,file_id
SQL> /
TABLESPACE_NAME                   FILE_ID FREE_SPACE
------------------------------ ---------- ------------------------------------------
USERS                                   2 1.2169189453125 G
YPAGE                                   5 .0665283203125 G
YPAGE                                   6 1.84368896484375 G
YPAGE                                   9 .03314208984375 G
SYSTEM                                  1 .32708740234375 G
INDXNEW                                 3 1.21160888671875 G
UNDOTBS                                 4 .00189208984375 G
UNDOTBS                                 7 .54620361328125 G
PERFSTAT                               11 .00994873046875 G
9 rows selected.
但是一定要注意，这里的空间是指当前实际空间，如果数据文件可以扩展，则剩余空间的size应该从DBA_DATA_FILE中取得。
SQL> select tablespace_name,sum((maxblocks-blocks)*8192/1024/1024/1024) ||' G' free_size from dba_data_files group by tablespace_name;
TABLESPACE_NAME                FREE_SIZE
------------------------------ ------------------------------------------
INDXNEW                        27.1171722412109375 G
PERFSTAT                       29.1874847412109375 G
SYSTEM                         31.5117034912109375 G
UNDOTBS                        3.28125 G
USERS                          27.9687347412109375 G
YPAGE                          89.9491729736328125 G
 
 5  、DBA_TABLES
记录了表的基本信息，和空间相关的主要有num_rows、blocks、empty_blocks、avg_space等，此视图中的这些参数需要通过分析命令才可以得到：
exec dbms_stats.gather_table_stats(ownname=>'username',tabname=>'tablename',ESTIMATE_PERCENT=>30,cascade=>true);
可以根据里面的blocks大致估计表的size：
  SQL> select sum(BLOCKS*8192/1024/1024) from dba_tables where table_name='YP_CORP_INFO';
SUM(BLOCKS*8192/1024/1024)
--------------------------
                880.695313
6、DBA_TAB_PARTITIONS
记录了表分区的基本信息，主要信息和DBA_TABLES类似，下面显示了表order_tab每个分区的size信息
SQL> select table_name,partition_name,subpartition_count ,tablespace_name,blocks*8192/1024/1024 as partition_size  from dba_tab_partitions WHERE table_name='ORDER_TAB';
TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT TABLESPACE_NAME                PARTITION_SIZE
------------------------------ ------------------------------ ------------------ ------------------------------ --------------
ORDER_TAB                      POD0                                            4 TOD0                               3773.34375
ORDER_TAB                      POD1                                            4 TOD1                               619.117188
ORDER_TAB                      POD2                                            4 TOD2                                737.78125
ORDER_TAB                      POD3                                            4 TOD3                               868.117188
ORDER_TAB                      POD4                                            4 TOD4                                 .0078125
ORDER_TAB                      POD5                                            4 TOD5                                 .0078125
ORDER_TAB                      POD6                                            4 TOD6                                 .0078125
ORDER_TAB                      POD7                                            4 TOD7                                 .0078125
ORDER_TAB                      POD8                                            4 TOD8                                 .0078125
 
 7、DBA_TAB_SUBPARTITIONS
和DBA_TAB_PARTITIONS类似，下面记录了pod0下面的子分区信息：