| SQL
4DBA's
Storage and free space scripts
|
rem name : TABLSP.sql
rem date : 01-01-2002
rem description: Shows tablespace types and config.
rem compatible : Oracle8i, Oracle9i
-- save settings first:
store set setsave.sql replace
set linesize 90
col tablespace_name for A14 head tablespace
col extent_management for A10 head 'extent|management'
col allocation_type for A10 head 'allocation|type'
select tablespace_name
,extent_management
,allocation_type
,initial_extent
,next_extent
,min_extlen
,contents
from sys.DBA_TABLESPACES
/
-- restore settings:
@setsave.sql
rem name : FREE1.sql
rem date : 01-10-1996
rem description: Shows free space for each tablespace.
rem compatible : v7.0 upwards
rem date : 01-06-2000 Added nvl() for NULLS
break on report
compute sum of Kbyte on report
set linesize 100
col tablespace_name for A16 heading tablespace
col file_name for A46
col Kbyte for 99,999,999
col Kbfree for 9,999,999
col Kb_max for 9,999,999
select F.tablespace_name
,F.file_name
,F.bytes/1024 KBYTE
,sum(nvl(S.bytes,0)/1024) KBFREE
,max(nvl(S.bytes,0)/1024) KB_MAX
from sys.DBA_DATA_FILES F
,sys.DBA_FREE_SPACE S
where F.file_id = S.file_id(+)
group by F.tablespace_name, F.file_name, F.bytes/1024
/
rem name : FREE2.sql
rem date : 01-01-1998
rem desc : Shows free space for each tablespace (size and percent).
rem compatible : v7.1 upwards
col name format A16 head "Tablespace Name"
col pct_used format 999.9 head "Pct|Used"
col Kbytes format 999,999,999 head "KBytes"
col used format 999,999,999 head "Used"
col free format 999,999,999 head "Free"
col max_free format 999,999,999 head "Max size|free chunk"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select nvl(FULL.tablespace_name,nvl(FREE.tablespace_name,'UNKOWN')) Name
,kbytes_used Kbytes
,kbytes_used-nvl(kbytes_free,0) Used
,nvl(kbytes_free,0) Free
,((kbytes_used-nvl(kbytes_free,0)) / kbytes_used)*100 Pct_used
,nvl(max_free,0) Max_free
from
( select sum(bytes)/1024 Kbytes_free
,max(bytes)/1024 max_free
,tablespace_name
from sys.DBA_FREE_SPACE
group by tablespace_name ) FREE,
( select sum(bytes)/1024 Kbytes_used
,tablespace_name
from sys.DBA_DATA_FILES
group by tablespace_name ) FULL
where FREE.tablespace_name (+) = FULL.tablespace_name
order by name
/
rem name: EXTENT.sql
rem date: 01-06-1994
rem desc: Shows segments with extents >9 plus the next extents size.
rem compatible : v7.0 upwards
col Next head 'Next Ext|Kbyte'
col KBYTES head 'Used|Kbytes'
select substr(owner,1,19) Owner
,substr(segment_name,1,30) Segment_name
,substr(segment_type,1,6) type
,rtrim(to_char((bytes/1024),'999,999')) KBytes
,rtrim(to_char(extents,'999')) Ext
,rtrim(to_char((next_extent/1024),'999,999')) NexT
from sys.DBA_SEGMENTS
where extents >= 10
and OWNER not in ('SYS','SYSTEM')
order by owner,segment_type
/
rem name : EXT_TOGO.sql
rem date : 01-10-1998
rem desc : Shows how many extents to go in a tablespace before it is full.
rem compatible : Oracle7, Oracle8i, Oracle9i
rem change: 01-05-2002 Added (+) for full datafiles.
col tablespace_name format A18 head 'Tablespace|name'
col M0 format 99,999,999 head 'Largest|free space'
col M1 format 999,999 head 'Maximum|next extent'
col M2 format 9999999 head 'Extents to go'
prompt Shows how many extents to go in a tablespace before it is full.
prompt It is assumed that Pctincrease=0
prompt Autoallocate Locally Managed Tablespaces are shown as [unknown]
set null [unknown]
select NEXT.tablespace_name
,nvl(FREE.Maxbyte,0)/1024 M0
,MAXT/1024 M1
,FREE.Maxbyte/MAXT M2
from (
select tablespace_name
,max(next_extent) MAXT
from sys.DBA_SEGMENTS
group by tablespace_name ) NEXT
,(
select tablespace_name
,max(bytes) Maxbyte
from sys.DBA_FREE_SPACE
group by tablespace_name ) FREE
where FREE.tablespace_name(+) = NEXT.tablespace_name
order by M2
/
set null ''
rem name : EXT_WARNING.sql
rem descr: Shows segs whose NEXT extent*2 will not fit in the largest free space
rem comp.: v7.1 upwards
col owner format A16
col segment_name format A20
col segment_type format A6 head 'Segmnt|type'
col tablespace_name format A16 head 'Tablespace|name'
col M1 format 99,999,999 head 'Largest|Free'
col N1 format 999,999 head "Next|extent"
break on tablespace_name
prompt Shows segs whose NEXT extent*2 will not fit in the largest free space
select FREE.tablespace_name
,FREE.Maxbyte/1024 M1
,NEXT.owner
,NEXT.segment_type
,NEXT.segment_name
,next_extent/1024 N1
from ( select owner,segment_type,segment_name,tablespace_name,next_extent
from sys.DBA_SEGMENTS ) NEXT
,( select tablespace_name,max(bytes) Maxbyte
from sys.DBA_FREE_SPACE
group by tablespace_name ) FREE
where FREE.tablespace_name = NEXT.tablespace_name
and NEXT.next_extent * 2 >= FREE.Maxbyte
order by tablespace_name,owner,segment_type,segment_name
/
prompt Any rows returned? Create space by adding a datafile.
rem name : STORE_CHECK.sql
rem description: Shows current and next extent sizes, grouped by type.
rem compatible : v7.0 upwards
rem
break on segment_type skip 1
col total# format 9999
col average format 9,999,999 head 'Average|segm.size'
col next format 9,999,999 head 'Next|extent'
col CurExt format 999 head 'Current|Nr Extents'
col MaxExt format 999 head 'Maximum|Nr Extents'
col segment_type format A10 head 'Type'
col pct_increase format 999 head 'Pct|increase'
set head on pagesize 65
select segment_type
,avg(bytes/1024) AVERAGE
,next_extent/1024 NEXT
,pct_increase
,max(extents) CurExt
,decode(max_extents,2147483645,'unltd',max_extents) MaxExt
,count(*) TOTAL#
from sys.DBA_SEGMENTS
where owner not in ('SYS','SYSTEM')
group by segment_type,next_extent,pct_increase,max_extents
/
rem name : STORE_CHECK2.sql
rem date : nov-2000
rem description: Checks table and index storage clauses for all owners.
rem Creates a spool file in C:\temp\DBnameYYMONDD.chk
break on owner on segment_type
col owner for A10 trunc head Owner
col total# for 9999 head 'Total Nr|objects'
col average for 9,999,999 head Average|segm.size
col next for 9,999,999 head Next|extent
col CurExt for 999 head 'Current|Nr Extents'
col MaxExt for A6 head 'Maximum|Nr Extents'
col segment_type for A10 head Segment|Type
col pct_increase for 999 head Pct|increase
set verify off echo off term on feed off wrap off head off
spool tmpspool.tmp
select 'spool C:\temp\'||name||'_'||to_char(sysdate,'yymondd')||'.chk'
from sys.v_$DATABASE;
spool off
@tmpspool.tmp
host del tmpspool.tmp
set head off feed off term ON
select 'Your largest segment is : '||max(bytes/1024)||' Kbyte.'
from sys.DBA_SEGMENTS
/
set head on verify on term on pages 60
select owner
,segment_type
,avg(bytes/1024) AVERAGE
,next_extent/1024 NEXT
,pct_increase
,max(extents) CurExt
,decode(max_extents,2147483645,'unltd',max_extents) MaxExt
,count(*) TOTAL#
from DBA_SEGMENTS
where owner not in ('SYS','SYSTEM')
group by owner,segment_type,next_extent,pct_increase,max_extents
order by owner,segment_type
/
spool off
| SQL
4DBA's
Storage and free space scripts
|
|