查看表空间数据文件相关

1.查看表空间数据文件个数

select dbf.tablespace_name, count(*)
  from dba_data_files dbf
 group by dbf.tablespace_name;


select dbs.tablespace_name,count(*)
  from dba_data_files dbs, v$datafile dbf
 where dbs.file_id = dbf.FILE#
 group by dbs.tablespace_name;

2.查看数据文件创建时间以及大小

select dbs.tablespace_name,
       dbs.file_name,
       dbf.CREATION_TIME,
       dbf.BYTES / 1024 / 1024 M
  from dba_data_files dbs, v$datafile dbf
 where dbs.file_id = dbf.FILE#;

3.查看数据文使用率

SELECT B.FILE_NAME file_name,
       B.TABLESPACE_NAME tb_name,
       B.BYTES / 1024 / 1024 file_M,
       (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 used_M,
       SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) use_rate
  FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
 WHERE A.FILE_ID = B.FILE_ID
 GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES
 ORDER BY B.TABLESPACE_NAME;

4.查看数据文件状态，扩展性

SELECT FILE_NAME       AS file_name,
       TABLESPACE_NAME AS tablespace_name,
       AUTOEXTENSIBLE  AS auto_extend,
       STATUS,
       MAXBYTES        AS max_extend_value,
       USER_BYTES      AS used_m,
       INCREMENT_BY
  FROM DBA_DATA_FILES;