0001-Oracle-系统-视图

参考：http://www.cnblogs.com/kerrycode/p/3418694.html
--查看表空间信息
select * from dba_tablespaces t where t.tablespace_name = 'EMS';

--查看数据文件信息
select * from dba_data_files T WHERE T.TABLESPACE_NAME = 'EMS';

--查看用户信息
select * from DBA_USERS T WHERE T.username = 'EMS';

--查看数据库对象
select * from DBA_OBJECTS T WHERE T.OBJECT_NAME LIKE '%DATA_FILE%';

--包含数据库中所有表空间的描述信息
SELECT * FROM DBA_TABLESPACES;

--包含当前用户的表空间的描叙信息
SELECT * FROM USER_TABLESPACES;

--包含从控制文件中获取的表空间名称和编号信息
SELECT * FROM V$TABLESPACE;

--包含数据文件以及所属的表空间的描述信息
SELECT * FROM DBA_DATA_FILES ;

--包含临时数据文件以及所属的表空间的描述信息
SELECT * FROM DBA_TEMP_FILES;

--包含从控制文件中获取的数据文件的基本信息，包括它所属的表空间名称、编号等
SELECT * FROM V$DATAFILE;

--包含所有临时数据文件的基本信息
SELECT * FROM V$TEMPFILE ;

--查看表空间使用情况-1
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
       ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "TOTAL(G)",
       ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "USED(G)",
       ROUND(C.BYTES / (1024 * 1024 * 1024), 2) AS "FREE(G)",
       ROUND((B.BYTES * 100) / A.BYTES, 2) AS "% USED",
       ROUND((C.BYTES * 100) / A.BYTES, 2) AS "% FREE"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

--查看表空间使用情况-2
SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名称",
       ROUND(D.AVAILB_BYTES, 2) AS "表空间大小(G)",
       ROUND(D.MAX_BYTES, 2) AS "最终表空间大小(G)",
       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空间(G)",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99') AS "使用比",
       ROUND(F.USED_BYTES, 6) AS "空闲空间(G)",
       F.MAX_BYTES AS "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /
                     (1024 * 1024 * 1024),
                     6) MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC