Oracle数据库查看表空间以及修改小空间自动扩展

 -- 1.当前数据文件情况
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.未指定默认表空间而创建用户,则系统应该是指定其默认为system表空间
SELECT USERNAME
      ,DEFAULT_TABLESPACE
      ,TEMPORARY_TABLESPACE 
  FROM DBA_USERS 
  WHERE USERNAME = 'QAOE';  
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 形式推理的部分这部分就剩下模态判断和负判断,闲话不多说 模态就是常说的可能和必然,先看图,可以看到模态判断和直言判...
    kingkissblue阅读 2,700评论 0 0
  • 在无可奈何的循环中,一次便利的终点永远是一份痛苦。
    周淡皮阅读 121评论 0 0