Thursday, November 16, 2006

Display datafile usage

Thursday, November 16, 2006 Posted by Andre Broers 1 comment
Display datafile usage.

src: http://www.oracle-consultant.co.uk




column tsname       format a20         heading 'Tablespace Name'
column flname       format a70         heading 'Filename'
column siz          format 999,999,990 heading 'File Size|(MB)'
column maxsiz       format 999,999,990 heading 'Max Size|(MB)'
column pctmax       format 990         heading 'Pct|Max'

set linesize  1000
set trimspool on
set pagesize  32000
set verify    off
set feedback  off

PROMPT
PROMPT ***********************
PROMPT *** DATAFILE STATUS ***
PROMPT ***********************

select file_name                                          flname
,      tablespace_name                                    tsname
,      bytes/1024/1024                                    siz
,      decode(maxbytes,0,0,maxbytes/1024/1024)            maxsiz
,      decode(maxbytes,0,0,bytes/maxbytes*100)            pctmax
from   dba_data_files
/

column tsname      clear
column flname      clear
column siz         clear
column maxsiz      clear
column pctmax      clear

1 comment: