So i was spending one of my last days at my current place of work today, as a phonecall came in. The customer wasn’t happy about his Undo tablespace stored in UNDO01.ora . It was taking up 18 Gigs . I run some standard selects in the database catalogs and figured out 0.001 % of it wasn’t used. I found this handy little script on the internet , it worked as it had to, and generated nice ALTER DATABASE DATAFILE XYZ.ORA resize XYZmb outputs.

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
--------------------------------------------------------
    -- free.sql
    --
    -- This SQL Plus script lists freespace by tablespace
    --------------------------------------------------------
    
    column dummy noprint
    column  pct_used format 999.9       heading "%|Used"
    column  name    format a16      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,'UNKOWN')) 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  sys.dba_free_space
           group by tablespace_name ) a,
         ( select sum(bytes)/1024 Kbytes_alloc,
                  tablespace_name
           from sys.dba_data_files
           group by tablespace_name )b
    where a.tablespace_name (+) = b.tablespace_name
    order by &1
    /
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
----------- maxshrink.sql ----------------------------------
    
    set verify off
    column file_name format a50 word_wrapped
    column smallest format 999,990 heading "Smallest|Size|Poss."
    column currsize format 999,990 heading "Current|Size"
    column savings  format 999,990 heading "Poss.|Savings"
    break on report
    compute sum of savings on report
    
    column value new_val blksize
    select value from v$parameter where name = 'db_block_size'
    /
    
    select file_name,
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
           ceil( blocks*&&blksize/1024/1024) currsize,
           ceil( blocks*&&blksize/1024/1024) -
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
    from dba_data_files a,
         ( select file_id, max(block_id+blocks-1) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id(+)
    /
    
    column cmd format a75 word_wrapped
    
    select 'alter database datafile '''||file_name||''' resize ' ||
           ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
    from dba_data_files a,
         ( select file_id, max(block_id+blocks-1) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id(+)
      and ceil( blocks*&&blksize/1024/1024) -
          ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
    /

So it worked like a charm, i managed to shrink te redo log tablespace to 200 Mb. The customer was happy, i was happy too.And yes, it was a safe operation because you can’t shrink the tablespace beyond the size of data in it, and it has autoextend enabled.

OpenSo(u)rcery

Long time, no see. Don't worry, I'm not dead, not even remotely dead. I'm just pretty busy. I'm actually working on great stuff at work and …… Continue reading

Openduty @ Cloud Budapest

Published on April 12, 2015

Leaving Ustream

Published on February 07, 2015