Oracle中和空间有关的视图总结（1）

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下面的子分区信息：