以下的SQL将呈现表空间及其包含的数据文件和使用情况。

————————————————————————
Ending。

5 thoughts on “SQL:表空间、数据文件,以及使用率”

  1. The Command is:
    select
    b.tablespace_name “Tablespace Name”,b.datafilelist “Datafile List”,b.totalmb “Total MB”,(b.totalmb – a.freemb) “Used MB”,a.freemb “Free MB”,
    Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”
    from
    (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,
    (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b
    where
    a.tablespace_name=b.tablespace_name
    ;

  2. col “Datafile List” for a90
    set linesize 900
    set pagesize 300

    select
    b.tablespace_name “Tablespace Name”,b.datafilelist “Datafile List”,b.totalmb “Total MB”,(b.totalmb – a.freemb) “Used MB”,a.freemb “Free MB”,
    Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”
    from
    (select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,
    (select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b
    where
    a.tablespace_name=b.tablespace_name
    order by 6;

  3. set linesize 400col “Tablespace Name” for a23selectb.tablespace_name “Tablespace Name”,b.totalmb “Total MB”,a.freemb “Free MB”,Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”from(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) bwherea.tablespace_name=b.tablespace_nameorder by “Usage Percent” desc;

  4. set linesize 400col “Tablespace Name” for a23selectb.tablespace_name “Tablespace Name”,b.totalmb “Total MB”,a.freemb “Free MB”,Round((a.freemb/b.totalmb)*100,2)||’ %’ “Free Percent”from(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) bwherea.tablespace_name=b.tablespace_nameorder by “Free Percent” desc;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

隐藏
变装