查询Oracle数据库表空间和数据文件方法

--查看Oracle数据库中表空间信息的命令方法：
SELECT A.A1 "表空间名称",
       C.C2 "类型",
       C.C3 "区管理",
       (B.B2 / 1024 / 1024) "表空间大小M",
       (B.B2 - A.A2) / 1024 / 1024 "已使用M",
       SUBSTR((B.B2 - A.A2) / B.B2 * 100, 1, 5) "利用率"
  FROM (SELECT TABLESPACE_NAME A1, SUM(NVL(BYTES, 0)) A2
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME B1, SUM(BYTES) B2
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) B,
       (SELECT TABLESPACE_NAME C1, CONTENTS C2, EXTENT_MANAGEMENT C3
          FROM DBA_TABLESPACES) C
 WHERE A.A1 = B.B1
   AND C.C1 = B.B1;
--查看Oracle数据库中数据文件信息的命令方法
SELECT B.FILE_NAME "物理文件名",
       B.TABLESPACE_NAME "表空间",
       B.BYTES / 1024 / 1024 "大小M",
       (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 "已使用M",
       SUBSTR((B.BYTES - SUM(NVL(A.BYTES, 0))) / (B.BYTES) * 100, 1, 5) "利用率"
  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;
  -- 查看数据文件
 SET linesize 1000;
select file#,status,name from v$datafile;
SELECT group#,sequence#,status,archived FROM v$log;
SELECT * FROM v$logfile;
SELECT * FROM v$controlfile;