• oracle查询表空间的空间占用情况


    select a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes - b.bytes)/a.bytes)*100,2) percent_used
    from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    where a.tablespace_name=b.tablespace_name order by ((a.bytes - b.bytes) / a.bytes) desc

    select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
    round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
    from
    (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    where a.tablespace_name=b.tablespace_name
    order by ((a.bytes-b.bytes)/a.bytes) desc


    查询所有表空间的总容量、已经使用、剩余、已经使用的百分比!

    select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
    round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
    from
    (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    where a.tablespace_name=b.tablespace_name
    order by ((a.bytes-b.bytes)/a.bytes) desc


    一般来说可以把上面的复杂的查询语句放入一个文件中,需要时再调用,或者创建一个试图,需要时可以查询。
    1  写入文件:#vi /home/mzl/percent_used_tablespace.sql
    内容:
    select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
    round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
    from
    (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    where a.tablespace_name=b.tablespace_name
    order by ((a.bytes-b.bytes)/a.bytes) desc

    2 导入:
    SQL> @/home/mzl/percent_used_tablespace.sql
    SQL> l
      1  select a.tablespace_name,a.bytes "Sum",a.bytes-b.bytes "used",b.bytes "free",
      2  round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
      3  from
      4  (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
      5  (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
      6  where a.tablespace_name=b.tablespace_name
      7* order by ((a.bytes-b.bytes)/a.bytes) desc
    SQL> /



    或者创建视图:
    SQL>create view percent
    SQL>as
    SQL>select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",
    SQL>round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
    SQL>from
    SQL>(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
    SQL>(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
    SQL>where a.tablespace_name=b.tablespace_name
    SQL>order by ((a.bytes-b.bytes)/a.bytes) desc;

    SQL> select * from percent;



    查看表空间的数据文件是否是自动扩展:
    SQL> l
      1* select file_name,tablespace_name,autoextensible from dba_data_files
    SQL> /

    FILE_NAME                                     TABLESPACE_NAME                AUT
    --------------------------------------------- ------------------------------ ---
    /u01/app/oracle/oradata/orcl/risenet.dbf      RISENET
    /u01/app/oracle/oradata/orcl/perfstat.dbf     PERFSTAT                       NO
    /u01/app/oracle/oradata/orcl/example01.dbf    EXAMPLE                        YES
    /u01/disk1/users01.dbf                        USERS                          YES
    /u01/app/oracle/oradata/orcl/sysaux01.dbf     SYSAUX                         YES
    /u01/app/oracle/oradata/orcl/undotbs01.dbf    UNDOTBS1
    /u01/disk2/system01.dbf                       SYSTEM                         YES
    /u01/app/oracle/oradata/orcl/undotbs02.dbf    UNDOTBS2                       NO
    /u01/disk1/pioneer_data.dbf                   PIONEER_DATA                   YES
    /u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   NO
    /u01/disk3/pioneer_undo.dbf                   PIONEER_UNDO                   NO

    FILE_NAME                                     TABLESPACE_NAME                AUT
    --------------------------------------------- ------------------------------ ---
    /u01/app/oracle/oradata/orcl/paul01.dbf       PAUL                           NO
    /u01/disk1/wenchuan.dbf                       WENCHUAN                       NO

    13 rows selected.


    比如表空间PIONEER_INDX已经用了83.33%,数据文件不能自动扩展,可以修改成自动扩展,以免数据写满数据文件。
    SQL> alter database
      2  datafile '/u01/disk2/pioneer_indx.dbf'  autoextend on;

    Database altered.

    SQL> select file_name,tablespace_name,autoextensible from dba_data_files     
      2  where tablespace_name='PIONEER_INDX';

    FILE_NAME                                     TABLESPACE_NAME                AUT
    --------------------------------------------- ------------------------------ ---
    /u01/disk2/pioneer_indx.dbf                   PIONEER_INDX                   YES


    或者给表空间多加一个自动扩展的数据文件,如果有多个硬盘,可以增加多个数据文件(这样多数据库系统的并发性比较好)
    SQL> alter tablespace pioneer_indx
      2  add datafile size 30M;

    Tablespace altered.

    SQL> select file_name,tablespace_name,bytes/1024/1024 "MB"  from dba_data_files
      2  where tablespace_name='PIONEER_INDX';

    FILE_NAME                                     TABLESPACE_NAME
    --------------------------------------------- ------------------------------
            MB
    ----------
    /u01/disk2/pioneer_indx.dbf                   PIONEER_INDX
             6

    /u01/disk5/ORCL/datafile/o1_mf_pioneer__45dpy PIONEER_INDX
    fty_.dbf
            30

  • 相关阅读:
    自动化测试如何解决验证码的问题
    python读取xml文件
    python实现简单爬虫功能
    python使用mysql数据库
    Prometheus 到底 NB 在哪里?- 每天5分钟玩转 Docker 容器技术(84)
    Prometheus 架构
    数据收集利器 cAdvisor
    Weave Scope 多主机监控
    Weave Scope 容器地图
    监控利器 sysdig
  • 原文地址:https://www.cnblogs.com/HondaHsu/p/1545914.html
Copyright © 2020-2023  润新知