Casual topic incoming again. Table fragmentation, we all know it and hate it as well. Tables get fragmented , this is a normal behaviour. But can we do to defragment them?

Casual method 1:

Creating a dump of the database, dropping the schema/schemas the importing the database. It just works. It defragments all the tables. But it’s an offline operation and takes  a lot of time . We don’t like things that last too long right?

Casual method 2:

moving all tables  like  [code] alter table foo move [/code]

The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same.  This is an offline operation too. And sadly it changes the ROWIDs of the table rows, and as such the indexes, which are based on ROWIDs, will become invalid (UNUSABLE).

 My method:

1
alter table foo shrink space compact

Using the “alter table xxx shrink space compact” command will re-pack the rows, move down the HWM, and releases unused extents.  With standard Oracle tables, you can reclaim space with the “alter table shrink space” command. And the best part, it does not invalidate your indexes .

Here is a little pl/sql block for the task:

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
begin
for s in




(
select table_name




from user_tables where temporary='N'
minus
select distinct(table_name) from user_Tab_columns




where data_type='LONG' or data_type = 'RAW' or
data_type='BLOB' or data_type='CLOB'
or data_type='LONG RAW' or data_type='XMLTYPE'




)




loop




execute immediate 'alter table FOO.' || s.table_name || ' enable row movement';
execute immediate 'alter table FOO.' || s.table_name || ' shrink space compact';
execute immediate 'alter table FOO.' || s.table_name || ' shrink space';
execute immediate 'alter table FOO.' || s.table_name || ' disable row movement';
execute immediate 'analyze table FOO.' || s.table_name || ' compute statistics';




end loop;




end;




/

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