REM
REM DBAToolZ NOTE:
REM This script was obtained from DBAToolZ.com
REM It's configured to work with SQL Directory (SQLDIR).
REM SQLDIR is a utility that allows easy organization and
REM execution of SQL*Plus scripts using user-friendly menu.
REM Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM s_dbf_all_info.sql
REM
REM DBF TABSP MOST
REM
REM Author:
REM Vitaliy Mogilevskiy
REM VMOGILEV
REM (www.dbatoolz.com)
REM
REM Purpose:
REM
REM VERY HELPFULL TO BALANCE DBF's on FILE SYSTEMS
REM This script displays just about all you need to know
REM about datafiles, storage, tablespaces and autoextend
REM
REM Misc:
REM to turn the autoextend on use this command:
REM SQL> alter database datafile '' autoextend ON NEXT M MAXSIZE M;
REM make sure to specify NEXT and MAXSIZE
REM
REM Usage:
REM s_dbf_all_info.sql
REM
REM Example:
REM s_dbf_all_info.sql
REM
REM
REM History:
REM 08-01-1998 VMOGILEV Created
REM
REM
col vname new_value x noprint
select name vname from v$database;
ttitle skip center "*** TABLESPACE USAGE ***"
set pages 1000
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a21 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) name
, kbytes_alloc kbytes
, kbytes_alloc-nvl(kbytes_free,0) used
, nvl(kbytes_free,0) free
, ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
, nvl(largest,0) largest
from (select sum(bytes)/1024 Kbytes_free
, max(bytes)/1024 largest
, tablespace_name
from dba_free_space
group by tablespace_name) a
, (select sum(bytes)/1024 Kbytes_alloc
, tablespace_name
from dba_data_files
group by tablespace_name) b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
accept ts_name char prompt "Enter Tablespace Name (Enter=ALL): "
ttitle skip center "*** TABLESPACE STORAGE ***"
set pages 999
set lines 132
set verify off
clear col
clear breaks
clear computes
col ts_name format a21 heading "Tablespace Name"
col init_ext format 999999999 heading "KB|Init Ext"
col next_ext format 999999999 heading "KB|Next Ext"
col min_ext format 999999999 heading "KB|Min Ext"
col max_ext format 999999999999 heading "Max Ext"
col pct_inc format 999 heading "Pct Incr"
col stat format a7 heading "Status"
col log format a10 heading "Logging"
SELECT tablespace_name ts_name
, initial_extent/1024 init_ext
, next_extent/1024 next_ext
, min_extents/1024 min_ext
, max_extents max_ext
, pct_increase pct_inc
, status stat
, logging log
FROM dba_tablespaces
WHERE DECODE(UPPER('&&ts_name'),NULL,'x',tablespace_name) like DECODE(UPPER('&&ts_name'),NULL,'x',UPPER('&&ts_name'));
ttitle skip center "*** DATAFILE USAGE ***"
clear computes
clear breaks
set pages 1000
set lines 132
set trims on
col tablespace_name format a15 heading "Tablespace Name"
col file_name format a40 heading "File Name"
col total_size_KB format 999999999 heading "Size KB"
col free_space_KB format 999999999 heading "Free KB"
col total_size format 9,999.00 heading "Size MB"
col free_space format 9,999.00 heading "Free MB"
col pct_used format 999.00 heading "%|Used"
clear breaks
select /*+ ORDERED */
df.tablespace_name
, df.file_name
, df.bytes/1024 total_size_KB
, nvl(fr.bytes/1024,0) free_space_KB
, df.bytes/1024/1024 total_size
, nvl(fr.bytes/1024/1024,0) free_space
, ((df.bytes-nvl(fr.bytes,0))/df.bytes)*100 pct_used
from (select sum(bytes) bytes
, file_id
from dba_free_space
group by file_id) fr
, dba_data_files df
where df.file_id = fr.file_id(+)
and DECODE(UPPER('&&ts_name'),NULL,'x',df.tablespace_name) like DECODE(UPPER('&&ts_name'),NULL,'x',UPPER('&&ts_name'))
order by 1, df.file_id
spool s_dbf_all_info.&x..log
/
ttitle skip center "*** DATAFILE AUTOEXTEND ***"
clear computes
clear breaks
break on tabsp_name skip 1
col file_name format a40 heading "Data File Name"
col tabsp_name format a15 heading "Tablespace Name"
col max_size format 99,999.99 heading "Max|Size MB"
col inc_by format 999.99 heading "Inc|By MB"
col unallocated format 9,999.99 heading "Unalloc|MB"
col cur_size format 99,999.99 heading "Current|Size MB"
compute sum of max_size cur_size unallocated on tabsp_name
select /*+ ORDERED */
tn.name tabsp_name
, ddf.file_name file_name
, ddf.bytes/1024/1024 cur_size
, decode(fex.maxextend,
NULL,ddf.bytes/1024/1024
,fex.maxextend*tn.blocksize/1024/1024) max_size
, nvl(fex.maxextend,0)*tn.blocksize/1024/1024 -
decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024) unallocated
, nvl(fex.inc,0)*tn.blocksize/1024/1024 inc_by
--from sys.v_$dbfile fn
from dba_data_files ddf
, sys.ts$ tn
, sys.filext$ fex
, sys.file$ ft
where ddf.file_id = ft.file#
and ddf.file_id = fex.file#(+)
and tn.ts# = ft.ts#
and DECODE(UPPER('&&ts_name'),NULL,'x',ddf.tablespace_name) like DECODE(UPPER('&&ts_name'),NULL,'x'
,UPPER('&&ts_name'))
order by tn.name
/
ttitle skip center "*** DATAFILE AUTOEXTEND BY MOUNT POINT ***"
clear computes
clear breaks
break on mount_point skip 1
col mount_point format a10 heading "Mnt"
compute sum of max_size cur_size unallocated on mount_point
select /*+ ORDERED */
DISTINCT
SUBSTR(ddf.file_name,1,DECODE(INSTR(ddf.file_name,'/',2),0,INSTR(ddf.file_name,':',1),INSTR(ddf.file_name,'/',2))) mount_point
, tn.name tabsp_name
, ddf.file_name file_name
, ddf.bytes/1024/1024 cur_size
, decode(fex.maxextend,
NULL,ddf.bytes/1024/1024
,fex.maxextend*tn.blocksize/1024/1024) max_size
, nvl(fex.maxextend,0)*tn.blocksize/1024/1024 -
decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024) unallocated
, nvl(fex.inc,0)*tn.blocksize/1024/1024 inc_by
from dba_data_files ddf
, sys.ts$ tn
, sys.filext$ fex
, sys.file$ ft
where ddf.file_id = ft.file#
and ddf.file_id = fex.file#(+)
and tn.ts# = ft.ts#
and SUBSTR(ddf.file_name,1,DECODE(INSTR(ddf.file_name,'/',2),0,INSTR(ddf.file_name,':',1),INSTR(ddf.file_name,'/',2)))
IN (SELECT DISTINCT
SUBSTR(file_name,1,DECODE(INSTR(file_name,'/',2),0,INSTR(file_name,':',1),INSTR(file_name,'/',2)))
FROM dba_data_Files
WHERE DECODE(UPPER('&&ts_name'),NULL,'x',tablespace_name) like DECODE(UPPER('&&ts_name'),NULL,'x',UPPER('&&ts_name')))
order by SUBSTR(ddf.file_name,1,5)
/
spool off
undefine ts_name