-
Notifications
You must be signed in to change notification settings - Fork 3
/
tablespace_space.sql
71 lines (61 loc) · 2.15 KB
/
tablespace_space.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
--==============================================================================
-- GPI - Gunther Pippèrr
-- Desc: get a overview over the free and used space for a tablespace
-- parameter name of the tablespace
--==============================================================================
-- http://oraculix.wordpress.com/2010/10/03/ora-01652-und-fragmentierte-tablespaces/
--==============================================================================
set verify off
set linesize 130 pagesize 300
define TABLESPACE_NAME = '&1'
prompt
prompt Parameter 1 = Tablespace Name => &&TABLESPACE_NAME.
prompt
column file_id format 9999 heading "File|Id"
column free_space_parts format 999G999G999 heading "Free|Parts"
column free_bytes_total format 999G999D99 heading "Free Total|MB's"
column free_blocks_total format 999G999G999 heading "Free Total|Blocks"
column max_free_bytes_in_one format 999G999D99 heading "Largest|MB's"
column max_free_blks_in_one format 999G999G999 heading "Largest|Blocks"
select file_id,
count(*) free_space_parts
,round(sum(bytes)/1024/1024,2) free_bytes_total
,sum(blocks) free_blocks_total
,round(max(bytes)/1024/1024,2) max_free_bytes_in_one
,max(blocks) max_free_blks_in_one
from sys.dba_free_space
where upper(tablespace_name)=upper('&&tablespace_name')
group by tablespace_name
, file_id
order by file_id
/
prompt
prompt existing storage distriubtion in the tablespace
prompt
select sizes
, count(distinct segment_name) segs
, sum(blocks) blks
from (select segment_name
, case
when blocks < 128 then 'small'
when blocks between 128 and 1023 then 'mittel'
else 'large'
end as sizes
, blocks
from dba_extents
where tablespace_name = upper('&&tablespace_name')
)
group by sizes
order by blks desc
/
prompt
prompt Extented distribution over the tables
prompt
select segment_name
, bytes/1024 as kb
, count(*)
from dba_extents
where tablespace_name=upper('&&tablespace_name')
group by segment_name,bytes
order by 1
/