-- 1.當前數(shù)據(jù)文件情況
SELECT B.NAME
      ,A.NAME
      ,A.BYTES/1024/1024  -- 單位為MB
  FROM V$DATAFILE A
      ,V$TABLESPACE B
 WHERE A.TS# = B.TS# ORDER BY B.NAME;
 
 -- 2.表空間情況
SELECT B.NAME
      ,SUM(BYTES)/1024/1024
  FROM V$DATAFILE A
      ,V$TABLESPACE B
 WHERE A.TS# = B.TS#
 GROUP BY B.NAME
 ORDER BY B.NAME ASC;
 
 -- 3.已使用表空間
SELECT TABLESPACE_NAME
     ,SUM(BYTES)/1024/1024
  FROM DBA_SEGMENTS
 GROUP BY TABLESPACE_NAME
 ORDER BY TABLESPACE_NAME ASC;
 
 -- 4.剩余表空間
 SELECT TABLESPACE_NAME
       ,SUM(BYTES)/1024/1024 
  FROM DBA_FREE_SPACE 
  GROUP BY TABLESPACE_NAME 
  ORDER BY TABLESPACE_NAME;

-- 5.查看占用比例
SELECT D.TABLESPACE_NAME
       ,SPACE "SUM_SPACE(M)"
       ,BLOCKS SUM_BLOCKS
       ,SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)"
       ,ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)"
       ,FREE_SPACE "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME
               ,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
               ,SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME
               ,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --IF HAVE TEMPFILE 
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)"
       ,BLOCKS SUM_BLOCKS
       ,USED_SPACE "USED_SPACE(M)"
       ,ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)"
       ,NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME
               ,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
               ,SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME
               ,ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE
               ,ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
 
-- 6.修改表空間自動擴展
ALTER DATABASE DATAFILE 'E:\ORACLEDATA\LUOC_DATA.DBF' AUTOEXTEND ON; 

-- 7.未指定默認表空間而創(chuàng)建用戶嘁圈，則系統(tǒng)應(yīng)該是指定其默認為system表空間
SELECT USERNAME
      ,DEFAULT_TABLESPACE
      ,TEMPORARY_TABLESPACE 
  FROM DBA_USERS 
  WHERE USERNAME = 'QAOE';