set pagesize 66

clear breaks

clear computes

column "Total Bytes" format 9,999,999,999,999

column "SQL Blocks" format 9,999,999,999

column "Bytes Free" format 9,999,999,999,999

column "Bytes Used" format 9,999,999,999,999

column "% Free" format 9999.999

column "% Used" format 9999.999

break on report

compute sum of "Total Bytes" on report

compute sum of "SQL Blocks" on report

compute sum of "Bytes Free" on report

compute sum of "Bytes Used" on report

compute sum of "% Free" on report

compute sum of "% Used" on report

TTitle left "*** Database: "xdbname", Current Tablespace Usage (As of: "xdate") ***" skip 1

select substr(fs.FILE_ID,1,3) "ID#",

    fs.tablespace_name,

    df.bytes "Total Bytes",

    df.blocks "SQL Blocks",

    sum(fs.bytes) "Bytes Free",

    (100*((sum(fs.bytes))/df.bytes)) "% Free",

    df.bytes-sum(fs.bytes) "Bytes Used",

    100*((df.bytes-sum(fs.bytes))/df.bytes) "% Used"

from sys.dba_data_files df,sys.dba_free_space fs

where df.file_id(+) = fs.file_id

group by fs.file_id,fs.tablespace_name,df.bytes,df.blocks

order by fs.tablespace_name

/

prompt

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt 12.0 Invesigation

prompt if a tablespace has all datafiles with % Used greater

prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>

prompt

ttitle off

prompt

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt + 13.0 Disk Activity                                                     +

prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++

prompt

column "File Name" format a35

column "File Total" format 999,999,999,990

set pagesize 33

ttitle "*** Database: "xdbname", Datafile Disk Activity (As of : "xdate") ***"

select substr(df.file#,1,2) "ID",

    rpad(name,35,'.') "File Name",

    rpad(substr(phyrds,1,10),10,'.') "Phy Reads",

    rpad(substr(phywrts,1,10),10,'.') "Phy Writes",

    rpad(substr(phyblkrd,1,10),10,'.') "Blk Reads",

    rpad(substr(phyblkwrt,1,10),10,'.') "Blk Writes",

    rpad(substr(readtim,1,9),9,'.') "Read Time",

    rpad(substr(writetim,1,10),10,'.') "Write Time",

    sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) "File Total"

from v$filestat fs,v$datafile df

where fs.file# = df.file#

group by df.file#,df.name,phyrds,phywrts,phyblkrd,

    phyblkwrt,readtim, writetim

order by sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim) desc , df.name

/

&

上一页  [1] [2] [3] [4] [5] [6]  下一页